Oracle PL/SQL Tutorial/System Packages/dbms rls

Add our policy to the EMP view

SQL>
SQL>
SQL> begin
  2      dbms_rls.add_policy
  3      ( object_name     => "EMP",
  4        policy_name     => "HR_APP_SELECT_POLICY",
  5        policy_function => "HR_PREDICATE_PKG.SELECT_FUNCTION",
  6        statement_types => "select" );
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-00439: feature not enabled: Fine-grained access control
ORA-06512: at "SYS.DBMS_RLS", line 20
ORA-06512: at line 2

SQL>

Call dbms_rls.add_policy to add policy to a table with statement type, update check and function schema

SQL>
SQL>
SQL> create table data_table
  2  (   some_data   varchar2(30),
  3      OWNER       varchar2(30) default USER
  4  )
  5  /
Table created.
SQL> begin
  2    dbms_rls.add_policy
  3     ( object_schema   => "userName",
  4       object_name     => "data_table",
  5       policy_name     => "MY_POLICY",
  6       function_schema => "TKYTE",
  7       policy_function => "security_policy_function",
  8       statement_types => "select, insert, update, delete" ,
  9       update_check    => TRUE,
 10       enable          => TRUE
 11     );
 12  end;
 13  /
begin
*
ERROR at line 1:
ORA-00439: feature not enabled: Fine-grained access control
ORA-06512: at "SYS.DBMS_RLS", line 20
ORA-06512: at line 2

SQL>
SQL> drop table data_table;
Table dropped.

dbms_rls.drop_policy

SQL>
SQL> begin
  2     -- drop the policy first.
  3     dbms_rls.drop_policy (
  4        object_schema     => "HR",
  5        object_name      => "EMP",
  6        policy_name      => "EMP_POLICY"
  7     );
  8
  9     dbms_rls.add_policy (
 10        object_schema     => "HR",
 11        object_name       => "EMP",
 12        policy_name       => "EMP_POLICY",
 13        function_schema   => "RLSOWNER",
 14        policy_function   => "AUTHORIZED_EMPS",
 15        statement_types   => "INSERT, UPDATE, DELETE, SELECT",
 16        update_check      => true,
 17        sec_relevant_cols => "SAL, COMM"
 18     );
 19  end;
 20  /
begin
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_RLS", line 59
ORA-06512: at line 3

dbms_rls.drop_policy and dbms_rls.add_policy

SQL>
SQL> begin
  2     dbms_rls.drop_policy( "UserName", "T", "T_POLICY" );
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-01918: user "USERNAME" does not exist
ORA-06512: at "SYS.DBMS_RLS", line 59
ORA-06512: at line 2

SQL>
SQL> begin
  2     dbms_rls.add_policy
  3     ( object_name => "T",
  4       policy_name => "T_POLICY",
  5       policy_function => "rls_examp",
  6       statement_types => "select, insert",
  7       update_check    => TRUE );
  8  end;
  9  /
begin
*
ERROR at line 1:
ORA-00439: feature not enabled: Fine-grained access control
ORA-06512: at "SYS.DBMS_RLS", line 20
ORA-06512: at line 2

SQL>

dbms_rls.shared_static

SQL> begin
  2     dbms_rls.drop_policy (
  3        object_schema         => "HR",
  4        object_name           => "DEPT",
  5        policy_name           => "EMP_DEPT_POLICY"
  6     );
  7     dbms_rls.add_policy (
  8        object_schema         => "HR",
  9        object_name           => "DEPT",
 10        policy_name           => "EMP_DEPT_POLICY",
 11        function_schema       => "RLSOWNER",
 12        policy_function       => "AUTHORIZED_EMPS",
 13        statement_types       => "SELECT, INSERT, UPDATE, DELETE",
 14        update_check          => true,
 15        policy_type           => dbms_rls.shared_static
 16     );
 17     dbms_rls.add_policy (
 18        object_schema         => "HR",
 19        object_name           => "EMP",
 20        policy_name           => "EMP_DEPT_POLICY",
 21        function_schema       => "RLSOWNER",
 22        policy_function       => "AUTHORIZED_EMPS",
 23        statement_types       => "SELECT, INSERT, UPDATE, DELETE",
 24        update_check          => true,
 25        policy_type           => dbms_rls.shared_static
 26     );
 27  end;
 28  /
begin
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_RLS", line 59
ORA-06512: at line 2