Oracle 10g column Mask vs. relational view

In the previous article, I introduced how to use Oracle 10g's Virtual Private database feature (VPD) to hide a column of data TechRepublic members Brooklynpennypincher suggested that I write a comparison article about VPD and relational views.

The task of the previous tip is to hide the salary and commission_pct columns in the report if the department_id value equals 60. Because the predicate is a static value, a relational view must also be able to accomplish the same task.

List A shows a view that uses the Selector case statement (which is available in the 8i version of Oracle SQL) to compare the relationship between the depatment_id value and 60, if equality returns NULL, and returns salary if it is another value. A similar case statement can also hide data from commission_pct columns. Protects data by granting access only to views instead of basic (Employees).

But if I change the rules a little bit, I want to hide all the wages in the department except the user. What's going to happen?

Your first thought might be to simply create more views-one per department-and grant each user access to the corresponding view. There are many reasons why this is not a good solution, specifically as follows:

A large number of views may increase the maintenance burden (for example, if one changes, all other possibilities may also need to be changed). The new department may require a new view, and the user must grant access permission, and the access permission must change when the user changes the department.

The query implemented in the view may be a static value, not a bound variable, so a large number of equally important copies of the query are stored in the shared pool.

Application development must invoke different view names for different users, which is also a source of complexity.

List B makes some changes to the VPD policy function in the previous article. Takes the direct comparison department number 60, which uses the Sys_context function to return the user department number (assuming that this value is set when the user logs on), and then the function returns a different assertion (where statement) for the different users. Wages are displayed only in rows that satisfy the assertion condition, and if no departmental number is set, returns the assertion "1==2", which is always false, so that all the wages in the report are hidden.

It is more advantageous to call the function Sys_context as a bound variable in the query, and only one copy of the query is stored in the shared pool to handle all departments.

You can also use the Sys_context function to build a relational view in the same way, but a different view in the application will probably see all of the data. The VPD method will filter all access to the Employees table, regardless of which query.

This is the key difference between the two methods: The view is designed to filter the data in an application, and VPD is designed to allow a large number of user groups to transparently share the same data table, and everyone can only see the data he owns without any application.

CREATE OR REPLACE VIEW Masked_salary_view

As

SELECT first_name, last_name,

   Case department_id

     When THEN NULL

     ELSE Salary

   End as salary,

   department_id

From Employees

FUNCTION rls_dept (Obj_owner in VARCHAR2, obj_name in VARCHAR2)



As
   Deptno number                       ;
   predicate                    VARCHAR2 (200);

BEGIN
   Deptno: = Sys_context (' Hr_context ', ' DEPT ');
   IF Deptno is NULL THEN
      predicate: = ' 1=2 ';
   ELSE
      predicate: = ' department_id = ' | | Deptno
   End IF;


   return (predicate);

End Rls_dept;

/