Oracle database Security Virtual Private database (VPD)

Oracle's security is divided into four parts, namely user management, access control, data protection and monitoring, specific reference to the [Oracle] Database Security Overview http://blog.csdn.net/u010415792/article/details/9008089

One of the most common technologies in Access control today is VPD,VPD virtual Private database, which appears at Oracle 8i and is an early data security tool for Oracle.

It means that by specifying a policy, the user's SQL is automatically added to the filter predicate to filter the result set, and the schematic diagram is as follows:

The approximate process is as follows: The user issues an SQL statement to access the data in the table, which triggers the security policy defined on the table, which adds the where predicate condition to the corresponding column and eventually returns the filtered result set to the user. This allows the user to see only the data that they have permission to see, as shown in the following illustration:

User 1 and User 2 emit two identical SQL statements, but depending on the security policy, they may get different results, just like accessing their own private databases, which is why this technology is called a virtual private database. Let's look at a few examples:

1 First, create a function that defines which predicate conditions to add:

Sql> 

Create or Replace function Hide_sal_comm (V_schema varchar2, V_object varchar2)  
  2 return  varchar2 as Con varchar2 (m);  
  3  begin
  4  con:= ' deptno=30 ';  
  5 return  (con);  
  6 end  Hide_sal_comm;  
  7  /  
      
function has been created.

2 use Dmbs_rls.add_policy () to add a security policy, specify the function created in the previous step in Policy_function, and specify the fields to filter in Sec_relevant_cols:

Sql> begin
  2     dbms_rls.add_policy (  
  3       object_schema          => ' Scott ',  
  4       object_ Name            => ' emp ',  
  5       policy_name            => ' Hide_sal_policy ',  
  6       policy_function        = > ' Hide_sal_comm ',  
  7       sec_relevant_cols      => ' Sal,comm ');  
  8 End  ;  
  9  /  
      
Pl/sql process has been successfully completed.

3 after adding a security policy, you can now access the table and discover that only deptno=30 data is available:

Sql> select * from Scott.emp;  
      
     EMPNO ename      JOB              MGR hiredate              SAL       COMM   

  DEPTNO  
------------------------------------------- ------------------------------ 

----------  
      7499 ALLEN      salesman        7698 20月-February -81           1600        300  
      7521 WARD       salesman        7698 2 February-February -81           1250  
      7654 MARTIN     Salesman        7698 2 August-September -81           1250       1400  
      7698 BLAKE      MANAGER         7839 January-May -81           2850  
      7844 TURNER     salesman        7698 August-September -81           1500          0  
      7900 JAMES      Clerk           7698 March-December -81            950  
      
6 rows have been selected.