Let your application no longer make changes to the database "cold" (i)

Program | data | Database


Original Author: Jim Czuprynski





Outline





changes to Oracle database objects make the data objects used by the application in an invalid state for a certain period of time, resulting in fatal errors. This article discusses techniques that can be used by DBAs to reduce database changes and conflicts between applications in practical applications.





as a famous philosopher in ancient Times said, "The only thing that is unchanged is change." The responsibility of the DBA is to control the changes in the database to make it orderly. Depending on the stability of your IT team and the stability of your development and support applications, you can easily handle calls every day, even every hour, and need to change the database structure.





since I am responsible for providing 24 (hour) X7 (days) of support for the customer's database system, one of the things I am very concerned about is that our applications can provide consistent and consistent performance without the need for specific database maintenance. Also, I found that as long as the plan was well planned, I could build a security layer to insulate applications from the database objects they were accessing.





Security Layer





uses a basic view to isolate applications





in its form, a basic view is nothing more than a view referencing all the columns in the table. When a new table is created, I usually immediately create a basic view and create a common synonym (public synonym) for the view, and then authorize the necessary object access to the appropriate role.





Now that Oracle allows direct manipulation of the base table with DML statements, I can reposition the DML to the base view and replace the base table. Here's an example. In the HR demo mode, there is a Employees table, and I grant the role oltprole full access to 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 great benefit of
using Basic view is that I can focus on the application of read and write data, while also isolating the application from the destructive drop table and the TRUNCATE statement. At the same time, I also prevented those "low-level DBAs" and "overzealous developers" from accidentally deleting important database objects.





advice: Note If you add a new column to the base table, the column is not automatically added to the basic view unless you recompile the basic view. This is a double-edged sword, when you reach the isolation application with the database object changes, but also cannot immediately get the newly added columns. Also, keep in mind that the constraint on the field is not included in the basic view (for example, if the column has a NOT NULL constraint and no default value is provided, or if there is a check constraint), the emitted INSERT statement fails.





uses basic view isolation to access the specified data





since you can specify aliases for columns in a view, we can use this feature to restrict the data returned by the user. We still use the Employees table, and I want to restrict the Oltpuser role to access only the required columns, in other words, just fill in the non-empty required columns when inserting an employee information.





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 oltpuser the user to the basic view Bv_employee to execute the DML statement, adding only the necessary information when recording.





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





 





not to be continued