Oracle dbms_rls Oracle Tips by Burleson Consulting

Using Oracle dbms_rls

A VPD security model uses the Oracle dbms_rls package (RLS stands for row-level security) to implement the security policies and application contexts. This requires a policy that is defined to control. access to tables and rows. Below is an example for the Oracle dbms_rls policy:

Here are examples of invocations of the dbms_rls.add policy:

DBMS_RLS.ADD_POLICY (
   'pubs',
   'book',
   'access_policy',
   'pubs',
   'book_access_policy',
   'select'

 );

BEGIN
  DBMS_RLS.ADD_POLICY (object_schema         => 'scott',
                       object_name           => 'emp',
                       policy_name           => 'sp_job',
                       function_schema       => 'scott',
                       policy_function       => 'pf_job',
                       policy_type           => DBMS_RLS.STATIC,
                       sec_relevant_cols     => 'sal,comm',
                       sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/

CALL DBMS_RLS.ADD_POLICY ('BART', 'condition_sets', 'condition_sets_policy', 'BART', 'exp_security.client_id_security', 'SELECT');

Oracle Virtual Private Database

object_schema => 'CLAIM_SCHEMA',
object_name => 'CLAIMS',
policy_name => 'CLAIM_SELECT_POLICY',
function_schema => 'SECUSER',
policy_function => 'SELECT_AUTH_CLAIMS',
statement_types => 'SELECT'
);

dbms_rls.add_policy (
object_schema => 'CLAIM_SCHEMA',
object_name => 'CLAIMS',
policy_name => 'CLAIM_INSERT_POLICY',
function_schema => 'SECUSER',
policy_function => 'INSERT_AUTH_CLAIMS',
statement_types => 'INSERT',
update_check => TRUE
);

dbms_rls.add_policy (
object_schema => 'CLAIM_SCHEMA',
object_name => 'CLAIMS',
policy_name => 'CLAIM_UPDATE_POLICY',
function_schema => 'SECUSER',
policy_function => 'UPDATE_AUTH_CLAIMS',
statement_types => 'UPDATE',
update_check => TRUE
);

dbms_rls.add_policy (
object_schema => 'CLAIM_SCHEMA',
object_name => 'CLAIMS',
policy_name => 'CLAIM_VALUE_POLICY',
function_schema => 'SECUSER',
policy_function => 'CHECK_CLAIM_VALUE_POLICY',
statement_types => 'UPDATE',
update_check => TRUE