Oracle security policies

In this article, I would like to talk about a solution that allows you to organize access restriction to data at the DBMS level, moreover, in the case when the constrained table itself contains data that affects 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 who have 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, make the connection on behalf of SYS , create a predicate function and a 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 SCOTT user, when accessing the USER_DATA table , will see only the data that belongs to him in the ud_user_id column , according to the setting in the USER_ALLOWED table . It remains to verify this, again log in as 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, not much. If you want, try it yourself, but given that the real task will be very different from the above example. Let's go back to the connection on behalf of SYS , and do:

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 to declare a new data type that reflects a single-column table of type NUMBER . This type will be used for a package variable in which a list of identifiers will be selected by which access will be granted. The function in the package is a wrapper that allows you to call back data. There will be no problems with the contents of the package variable between sessions, since a separate memory area is allocated for each session. Assuming that the purpose of the cursor in the predicate function is to determine the identifiers of the primary key of the table, it is more advisable to modify the generated constraint into the EXISTS construct .

Now check the result of the work done by connecting on behalf of SCOTT :

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