Oracle implements row-level data control-Application of dbms_rls package

Application of dbms_rls package -- Implement row-level security control of database tables
RNS stands for row level security.

Log on to the kgis user to create the recursive experiment data table and create the recursive function to apply it to a table for testing.

C: \ windows \ system32> sqlplus/nolog

SQL * Plus: Release 11.2.0.1.0 production on Wednesday January 30 10:19:59 2013

Copyright (c) 1982,201 0, Oracle. All rights reserved.

SQL> conn kgis/kgis
Connected.
-- Create a table and insert data
SQL> Create Table xx_test (ID number, name varchar2 (255), tag varchar2 (20 ));

The table has been created.

SQL> insert into xx_test values (1, 'A', '011 ');

One row has been created.

SQL> insert into xx_test values (2, 'bb', '022 ');

One row has been created.

SQL> insert into xx_test values (3, 'cc', '033 ');

One row has been created.

SQL> commit;

Submitted.

-- Create three users to query corresponding data for different users
SQL> create user t01 identified by t01
2 default tablespace kgis_data
3 temporary tablespace temp
4 profile default;
 
User Created

SQL> create user t02 identified by t02
2 default tablespace kgis_data
3 temporary tablespace temp
4 profile default;
 
User Created

SQL> create user t03 identified by t03
2 default tablespace kgis_data
3 temporary tablespace temp
4 profile default;
 
User Created

-- Authorized users can query the table xx_test
SQL> grant select on xx_test to t01;
 
Grant succeeded
SQL> grant select on xx_test to t02;
 
Grant succeeded
SQL> grant select on xx_test to t03;
 
Grant succeeded

SQL> grant connect to t01;
 
Grant succeeded
SQL> grant resource to t01;
 
Grant succeeded
SQL> grant connect to t02;
 
Grant succeeded
SQL> grant resource to t02;
 
Grant succeeded
SQL> grant connect to t03;
 
Grant succeeded
SQL> grant resource to t03;
 
Grant succeeded

SQL> Create public synonym xx_test for kgis. xx_test;
 
Synonym created

-- Switch to t01 and you will find that you can view all the data in the table.
SQL> conn t01/t01
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as t01
 
SQL> select * From xx_test;
 
ID name tag
--------------------------------------------------------------------------------------------------------------
1 aa 011
2 BB 022
3 cc 033

-- Switch back to the kgis user to create the user permission table, that is, the data that the user can query
Create Table rls_users (ID number, username varchar2 (255), usertag varchar2 (20 ));
Insert into rls_users values (1, 't01', '011 ');
Insert into rls_users values (2, 't02', '022 ');
Insert into rls_users values (3, 't03', '033 ');
Commit;

-- Create the RLS Function
-- The result returned by the function is the where condition of the corresponding table.
Create or replace function f_select_data_security (p_user varchar2, p_table varchar2) return varchar2 is
Results varchar2 (255 );
Begin
-- Sys_context ('userenv', 'session _ user') Get session_user
-- Or directly use the input parameter p_user
Results: = 'tag in (select usertag from kgis. rls_users where username = sys_context (''userenv'', ''session _ user ''))';
Return results;
End;

-- Verify that the function returns the correct result
Select f_select_data_security ('t02', 'xx _ test') from dual;

-- Add a security policy to table xx_test
Begin
Dbms_rls.add_policy (object_schema => 'kgis ',
Object_name => 'xx _ test ',
Policy_name => 'select _ data_security ',
Policy_function => 'f _ select_data_security ');
End;

-- Check whether the security policy of the RLS has been added.
Select * From dba_policies where object_owner = 'kgis 'and object_name = 'xx _ test ';
-Note:
Two input parameters (user input parameters and object input parameters) in the policy function cannot be left empty, although they can be used in the function. Otherwise
Tip: ORA-28112: unable to execute policy function

-- Switch to the following different users and find that each user can only query corresponding data
SQL> conn t01/t01
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as t01
 
SQL> select * From xx_test;
 
ID name tag
--------------------------------------------------------------------------------------------------------------
1 aa 011
 
SQL> conn t02/t02
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as t02
 
SQL> select * From xx_test;
 
ID name tag
--------------------------------------------------------------------------------------------------------------
2 BB 022
 
SQL> conn t03/t03
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as t03
 
SQL> select * From xx_test;
 
ID name tag
--------------------------------------------------------------------------------------------------------------
3 cc 033

Note: If a user does not want to be controlled, you can modify it in the recursive function to check whether the user returns NULL. If the user returns NULL, all data can be viewed.