Original author: Jim Czuprynski
Outline
For the change of Oracle database objects, the data objects used in the application are in an invalid state in a certain period of time, resulting in fatal errors. This article discusses some techniques, which can reduce the conflict between database modifications and applications for DBA in practical applications.
As an ancient, a famous philosopher said that "the only constant change is". DBA's duty is to control the changes in the database, making it order. Depending on your IT team's stability and the stability of your development and supporting applications, you can calmly pay the daily call, and you need to change the database structure.
Since I am responsible for the support of the customer's database system, one of the work is that I am very concerned about providing our application to provide sustained stable performance without requiring specific database maintenance. Moreover, I found that as long as the prior planning is written, I can build a security layer to make the application and the database objects they have to access are insulated.
Security layer
Use basic views to isolate applications
As far as it is in its form, a basic view is just a View references all columns in the table. When a new table is created, I usually create a basic view immediately and create a public synonym for this view, and then authorize the necessary object access to the appropriate role.
Since Oracle allows DML statements to operate the basic table directly, I can relocate DML to the basic view to replace the basic table. Below is an example in HR Demo mode, there is an Employees table, I grant the role OLTPROLE's right to fully access the view.
Create or Replace View Hr.BV_EMPLOYEES AS
Select * from hr.employees
/
Create public synonym Employees for hr.bv_employees;
Grant SELECT, INSERT, UPDATE, DELETE ON HR.BV_EMPLOYEES TO OLTPROLE
The biggest advantage of using the basic view is that I can focus on the application's read and write data, and it is also isolated with destructive DROP TABLE and TRUNCATE statements. At the same time, I also prevent "low-level DBA" and "overwind developers" in a hurry or unexpectedly delete important database objects.
Advice: Note If you add a new column to the base form, this column will not be added automatically, unless you re-compile the basic view. This is the double-edged sword, when you reach the fluctuation of the application and the database object, but you can't get new columns immediately. Moreover, to remember that the constraint on the field is not included in the basic view (for example, a constraint of NOT NULL and does not provide a default value, or a check constraint), the issued INSERT statement will fail.
Use basic view to isolate applications to access specified data
Since you can specify an alias for the column of the view, we can use this feature to limit the data returned by the user. We still use the Employees table, I want to limit the OLTPUser role to access the necessary columns, in other words, when inserting an employee information, you only need to fill in those non-empty essential columns.
Drop view hr.bv_employees;
Create or replace view hr.bv_employees
EMPID,
FNAME,
Lname,
Email,
Hire_date,
JOB_ID)
AS
SELECT
EMPLOYEE_ID,
First_name,
Last_name,
Email,
Hire_date,
Job_ID
From hr.employees
/
Drop Public Synonym Employees;
Create Public Synonym Employees for BV_Employees; Grant SELECT, INSERT, UPDATE, DELETE ON HR.BV_EMPLOYEES TO OLTPROLE
Now I execute the DML statement with the OLTPUSER user, and only the necessary information is required when the record is increased.
INSERT INTO EMPLOYEES
Values (501, 'Damien', 'McGilliCudy', 'Damienm@oracle.com', to_date ('12/31 / 1999 '),' fi_account ');
COMMIT;
SQL> SELECT *
2 from Employees
3 WHERE EMPID> = 500
4 order by Empid;
Empid Fname Lname
---------- ------------------------------------------------------------------------------------------------------------------------------------------ -----
Email Hire_Date job_id
-------------------------------------------------------------------------------------------------------------------------------------- ----
501 Damien McGilliCudy
Damienm@oracle.com 12/31/1999 00:00:00 Fi_account
Last continued
