The RLS Layer of Security - Oracle

If you want to prevent users from accessing all records (including their own), first use the object privileges to enforce this to ensure the user doesn’t have the privileges on the table.

To strengthen the security, you can also use RLS. RLS provides an additional layer of security and will even prevent someone with the DBA role or an ANY system privilege—SELECT ANY TABLE—from accessing your protected table(s). Note this doesn’t imply the table is protected from a DBA with malicious intentions. The DBA can alter the policy function, drop the table, disable the RLS policy, and so on. The shear fact of being a DBA doesn’t override the RLS capability. In the previous example, a DBA who can read records from your table still can’t delete any records because the delete policy restricts access to the records matching USERNAME with the schema name:

scott@KNOX10g> CONN system/manager Connected.
system@KNOX10g> -- user has SELECT ANY TABLE so he can see all records
system@KNOX10g> SELECT COUNT(*) FROM scott.people;
  COUNT(*)
----------
        14
system@KNOX10g> -- user cannot delete records as RLS prevents this
system@KNOX10g> DELETE FROM scott.people;
0 rows deleted.

The most effective way to prevent records from being accessed within an RLS policy function is to return a string that consists of something that can never happen. An easy example is the string “1=0”. Be aware—returning a null or a zero length string has the opposite effect, and all rows are returned.

CAUTION

Returning null from the policy function allows all records to be accessed.

You might find it useful to create a function that eliminates all records. You can then use it anytime you want to quickly lock down a table:

sec_mgr@KNOX10g> -- Create a function to be used with RLS
sec_mgr@KNOX10g> -- that will always eliminate all records.
sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION no_records (
  2    p_schema  IN  VARCHAR2 DEFAULT NULL,
  3    p_object  IN  VARCHAR2 DEFAULT NULL)
  4    RETURN VARCHAR2
  5  AS
  6  BEGIN
  7    RETURN '1=0';
  8  END;
  9  /
Function created.

There are several ways to create a READ ONLY table or view; here you can see another. Adding the NO_RECORDS function to an RLS policy for inserts, updates, and deletes effectively makes the table READ ONLY.

scott@KNOX10g> CREATE TABLE people_ro AS SELECT * FROM emp;
Table created.
scott@KNOX10g> conn sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> BEGIN
  2    DBMS_RLS.add_policy
  3        (object_schema   =>  'SCOTT',
  4         object_name     =>  'PEOPLE_RO',
  5         policy_name  => 'PEOPLE_RO_IUD',
  6         function_schema =>  'SEC_MGR',
  7         policy_function => 'No_Records',
  8         statement_types => 'INSERT, 
   UPDATE,DELETE',
  9         update_check    => TRUE);
 10  END;
 11  /
PL/SQL procedure successfully completed.

As indicated by the tests here, the RLS policy helps to ensure the table maintains its READ ONLY status:

sec_mgr@KNOX10G> scott@KNOX10g> -- User can read all records
scott@KNOX10g> SELECT COUNT (*) FROM people_ro;
  COUNT(*)
----------
        14
scott@KNOX10g> -- Cannot update any records scott@KNOX10g> UPDATE people_ro
  2     SET ename = NULL;
0 rows updated.
scott@KNOX10g> -- Cannot delete records
scott@KNOX10g> DELETE FROM people_ro;
0 rows deleted.
scott@KNOX10g> -- Cannot insert new records scott@KNOX10g> INSERT INTO people_ro (ename) VALUES ('KNOX');
INSERT INTO people_ro (ename) VALUES ('KNOX')
            *
ERROR at line 1:
ORA-28115: policy with check option violation

While the RLS provides wonderful security, it can be problematic when doing database administration tasks such as performing data backups. As you have seen, even the DBAs and the data owner cannot bypass the RLS policy. If you perform an export as the data owner or another administrator while an RLS policy was enabled, you may very well end up with a dataless backup file.

For this reason (and a few others), there is an EXEMPT ACCESS POLICY privilege. This privilege allows the grantee to be exempted from all RLS functions. An administrator who has to perform data backups can use this privilege to ensure backup ability for all the data in the tables. The following example illustrates how this privilege is granted and the affect it has on enabled RLS policies:

system@KNOX10g> CONN system/manager Connected.
system@KNOX10g> -- Show system is affected by RLS policy.
system@KNOX10g> -- No records should be deleted.
system@KNOX10g> DELETE FROM scott.people_ro;
0 rows deleted.
system@KNOX10g> -- grant privilege to bypass RLS policies
system@KNOX10g> GRANT EXEMPT ACCESS POLICY TO SYSTEM;
Grant succeeded.
system@KNOX10g> -- Show system is no longer affected by RLS policy.
system@KNOX10g> -- All records should be deleted.
system@KNOX10g> DELETE FROM scott.people_ro;
14 rows deleted.
system@KNOX10g> ROLLBACK ;

EXEMPT ACCESS POLICY is a very powerful privilege. The privilege isn’t specific to a schema or policy; it applies to all schemas and all policies. Care should be given to ensure that the privilege is well guarded. By default, users with SYSDBA privileges are exempt from RLS policies. You can determine who has been granted the EXEMPT ACCESS POLICY privilege by checking system privileges, as shown here:

sec_mgr@KNOX10g> -- Show users that are exempt from RLS policies
sec_mgr@KNOX10g> SELECT grantee
  2    FROM dba_sys_privs
  3   WHERE PRIVILEGE = 'EXEMPT ACCESS 
   POLICY';
GRANTEE
----------------------------------
SYSTEM

NOTE

The EXEMPT ACCESS POLICY system privilege allows privileged users to bypass RLS  policies. This may be necessary for database backup and recovery, but it can also be a security risk. Guard this privilege tightly.

You can’t prevent privileged users from abusing their privileges, you can only catch them doing it. Auditing is an effective way to ensure the RLS exemption privilege is not being abused. The following shows how to enable auditing for this privilege. Once enabled, you should test to ensure that everything is being audited as you think. For this to occur, the system privilege has to be exercised. This too is shown:

sec_mgr@KNOX10g> -- Audit the exempt policy system privilege
sec_mgr@KNOX10g> AUDIT EXEMPT ACCESS POLICY BY ACCESS;
Audit succeeded.
sec_mgr@KNOX10g> -- Test audit by exercising the system privilege
sec_mgr@KNOX10g> CONN system/manager Connected.
system@KNOX10g> DELETE FROM scott.people_ro;
14 rows deleted.
system@KNOX10g> -- Rollback will not erase the audit record
system@KNOX10g> ROLLBACK ;
Rollback complete.
system@KNOX10g> CONN sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> -- show audited operation
sec_mgr@KNOX10g> BEGIN
  2    FOR rec IN (SELECT *
  3                  FROM dba_audit_trail)
  4    LOOP
  5      DBMS_OUTPUT.put_line ('------------
    -------------');
  6      DBMS_OUTPUT.put_line ('Who: ' ||
    rec.username);
  7      DBMS_OUTPUT.put_line (  'What: '
  8                  || rec.action_name
  9                  || ' on '
 10                  || rec.owner
 11                  || '.'
 12                  || rec.obj_name);
 13      DBMS_OUTPUT.put_line ( 'When: '
 14                  || TO_CHAR
 15                     (rec.TIMESTAMP,
 16                    'MM/DD HH24:MI'));
 17      DBMS_OUTPUT.put_line ('How: "' ||
   rec.sql_text || '"');
 18      DBMS_OUTPUT.put_line ('Using: ' ||
   rec.priv_used);
 19    END LOOP;
 20  END;
 21  /
--------------------
Who:   SYSTEM
What:  DELETE on SCOTT.PEOPLE_RO
When:  04/04 14:22
How:   "DELETE FROM scott.people_ro"
Using: DELETE ANY TABLE
--------------------
Who:   SYSTEM
What:  DELETE on SCOTT.PEOPLE_RO
When:  04/04 14:22
How:   "DELETE FROM scott.people_ro"
Using: EXEMPT ACCESS POLICY
PL/SQL procedure successfully completed.

The audit trail shows two records because the SYSTEM user exercised two system privileges when the delete statement was issued. The first privilege, DELETE ANY TABLE, allowed access to the table. The second privilege, EXEMPT ACCESS POLICY, allowed access within the table.