Oracle security policies

In this article I would like to talk about a solution that allows you to organize data access restriction at the DBMS level, moreover, in the case when the restricted table itself contains data affecting the restriction.

The example that I will give is greatly simplified, but the necessary tools used in the solution are provided exhaustively. Tested on Oracle 11g R2.

Suppose that the SCOTT user needed to organize access restriction to the data of some tables, but the criteria by which access should be granted look so that it is necessary to analyze the data of the restricted table. It would seem that it could be easier - let's go.

')
Establish a connection to the database on behalf of SCOTT and create tables, one of which will contain a list of users with access, and the second the data itself:

CREATE TABLE scott.user_allowed ( ua_id NUMBER, oracle_user VARCHAR2(50) ); Table created. insert into scott.user_allowed (ua_id, oracle_user) values (1, 'SCOTT'); insert into scott.user_allowed (ua_id, oracle_user) values (2, 'OTHERUSER'); 1 row created. 1 row created. commit; CREATE TABLE scott.user_data ( ud_id NUMBER, ud_user_id NUMBER, ud_data VARCHAR2(2000) ); Table created. insert into scott.user_data (ud_id, ud_user_id, ud_data) values (1, 1, 'SCOTT DATA'); insert into scott.user_data (ud_id, ud_user_id, ud_data) values (2, 2, 'OTHERUSER DATA'); 1 row created. 1 row created. commit; select ud_id, ud_user_id, ud_data from user_data; 2 rows selected. 

Everything went well. Now we will execute the connection on behalf of SYS , create a predicate function and security policy:

 CREATE OR REPLACE FUNCTION sys.scott_predicate_allow ( obj_schema IN VARCHAR2, obj_name IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN return 'ud_id in (SELECT ud_id FROM scott.user_data ud, scott.user_allowed ua WHERE ud.ud_user_id = ua.ua_id AND upper(ua.oracle_user) = upper(USER))'; END scott_predicate_allow; / Function created. 

 BEGIN SYS.DBMS_RLS.ADD_POLICY ( Object_schema => 'SCOTT' ,Object_name => 'USER_DATA' ,policy_name => 'SCOTT_ALLOW_USER_DATA' ,function_schema => 'SYS' ,policy_function => 'SCOTT_PREDICATE_ALLOW' ,statement_types => 'SELECT,INSERT,UPDATE,DELETE,INDEX ' ,policy_type => dbms_rls.dynamic ,long_predicate => FALSE ,sec_relevant_cols => 'UD_ID,UD_USER_ID,UD_DATA' ,sec_relevant_cols_opt => NULL ,update_check => FALSE ,enable => TRUE ); END; / PL/SQL procedure successfully completed. 

That's all, now the user SCOTT when accessing the USER_DATA table, will see only the data that belong to him in the ud_user_id column, in accordance with the setting in the USER_ALLOWED table. It remains to verify this, again perform the input on behalf of SCOTT and:

 select ud_id, ud_user_id, ud_data from user_data; ORA-28113: policy predicate has error 

In order to determine what the error is, you will need to view the trace file, which contains a detailed description:

 ORA-28108: circular security policies detected 

In fact, I would call it an analogue of a mutation in a trigger. To solve the problem, you can use ... as practice has shown, there is little that. You want to try it yourself, but considering that the real task will be very different from the example given. Let us return to the connection on behalf of SYS , and execute:

 create or replace type sys.number_table is table of number; / Type created. CREATE OR REPLACE PACKAGE SYS.SCOTT_ALLOWED AS allowed_id number_table:=number_table(); function get_allowed_id return number_table; END SCOTT_ALLOWED; / Package created. CREATE OR REPLACE PACKAGE BODY SYS.SCOTT_ALLOWED AS function get_allowed_id return number_table is begin return allowed_id; end; END SCOTT_ALLOWED; / Package body created. CREATE OR REPLACE FUNCTION sys.scott_predicate_allow ( obj_schema IN VARCHAR2, obj_name IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN scott_allowed.allowed_id:=number_table(); select ud_id bulk collect into scott_allowed.allowed_id from scott.user_data ud, scott.user_allowed ua where ud.ud_user_id = ua.ua_id and upper(ua.oracle_user) = upper(USER); return 'EXISTS (SELECT 1 FROM TABLE(scott_allowed.get_allowed_id) a WHERE a.column_value = ud_id)'; END scott_predicate_allow; / Function created. 

The first thing that was done was declared a new data type, reflecting a table from a single NUMBER column. This type will be used for the package variable to which the list of identifiers will be selected, by which access will be granted. The function in the package is a wrapper that allows reverse access to the data. There will be no problems with the contents of the package variable between sessions, since each session is allocated a separate memory area. Assuming that the purpose of the cursor in the predicate function is to define the primary key identifiers of the table, it is better to modify the constraint being formed into the EXISTS construct.

Now check the result of the work done, making the connection on behalf of SCOTT :

 select ud_id, ud_user_id, ud_data from user_data; 1 row selected.