Using Oracle VPD for Row-level security protection (I)

Http://boylook.itpub.net/post/43144/520544

As we all know, Oracle locks are row-level. Let's take a look at Oracle's Secure Access Mechanism:
Secure Data Filtering must be completed at the first layer of the basic data table, so that users cannot bypass security control either through the view or basic table. The most fundamental capability of VPDvirtual private database is to "filter data transparently" to provide row-level security protection.

Later versions of Oracle8i all provide powerful functions such as VPD to meet the ever-increasing system security requirements. By setting a VPD-based fine-grained access policy, you can only access data that you have access to without modifying the application, that is, the application is transparent, of course, more complex permission control is essential for developers to participate.

The following uses a simple example to implement this function. users in the EMP table can only query and update other employee information of their own department after logging on to the database, if it is not in this department, it will not be displayed or cannot be updated.
----------------------------------------------------
1. We need two users: one is the hr user used to set the VPD policy, and the other is the David user with records in the hr. employees table.
SQL> create user David identified by boylook;

User created.

SQL> grant create session to david;

Grant succeeded.

SQL> conn hr
Enter password:
Connected.

2. First, use the hr user to create a policy permission table. For convenience, you can directly create an employee using the employees table. This policy table contains the employee name and department ID. The David user belongs to both Departments 60 and 80.
SQL> create table rls_test as select * from employees;

Table created.

SQL> select department_id from employees where first_name = 'David ';

DEPARTMENT_ID
-------------
60
80
80

3. functions required by the hr user to create a VPD policy.
SQL> @ create_vpd_func see the end of the article)

Function created.
This function provides the following functions:
If you use an hr user to log on, because the table belongs to this user, no restrictions are imposed.
If other users are used to log on to SYS users without this restriction), the Department of the user in the employees table determines which records allow the user to operate, in this example, employees of the 60 and 80 departments, David, will be able to see it.
If the logged-on user is not in the employees table, the user cannot view any data.

Note:
The VPD policy function must contain two parameters, p_schema and p_table, which must be included even if they are not used in the function. Otherwise, the following message is returned when you search the EMP table data:
PLS-00306: The number or type of parameters is incorrect when 'get _ USER_DEPT_ID 'is called.

4. Create a VPD policy with the hr user.
SQL> conn/as sysdba
Connected.
SQL> grant execute on dbms_rls to hr
2;

Grant succeeded.

SQL> conn hr
Enter password:
Connected.
SQL> @ test_add_policy;
10/

PL/SQL procedure successfully completed.

5. So far, our VPD solution has been set up. Next let's test -- hr users can select all 107 records
SQL> select count (*) from employees;

COUNT (*)
----------
107

SQL> conn david
Enter password:
Connected.
-- The David user can only select 39 SQL records belonging to department 60 and 80> select count (*) from hr. employees;

COUNT (*)
----------
39

Appendix:
Cat create_vpd_func. SQL
Create or replace function test_vpd (p_schema in varchar2, p_object in varchar2)
Return varchar2
As
Rochelle retstr varchar2 (2000 );

Type dept_id_type is table of employees. department_id % type;

Dept_id_tab dept_id_type;

Temp_v varchar2 (2000 );
Begin
If sys_context ('userenv', 'current _ user') = user then
Return '';
End if;
Select department_id
Bulk collect into dept_id_tab
From rls_test
Where upper (first_name) = sys_context ('userenv', 'session _ user ');

If dept_id_tab.count = 0 then
Rochelle retstr: = '1 = 2 ';
Else
For I in dept_id_tab.first... dept_id_tab.last
Loop
Temp_v: = temp_v | ',' | dept_id_tab (I );
End loop;
Rochelle retstr: = 'department _ id in ('| ltrim (temp_v,', ') | ')';
End if;
Return l_retstr;
End;
/
Cat test_add_policy. SQL
Declare
Begin
Dbms_rls.add_policy (
Object_schema => 'hr ',
Object_name => 'ployees ',
Policy_name => 'hide _ emp ',
Function_schema => 'hr ',
Policy_function => 'test _ VPD ');
End;

This article is from "MIKE's old blog" blog, please be sure to keep this source http://boylook.blog.51cto.com/7934327/1298619