Oracle PL/SQL Tutorial/System Packages/dbms rls

Add our policy to the EMP view

   <source lang="sql">

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></source>

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

   <source lang="sql">

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.</source>

dbms_rls.drop_policy

   <source lang="sql">

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</source>

dbms_rls.drop_policy and dbms_rls.add_policy

   <source lang="sql">

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></source>

dbms_rls.shared_static

   <source lang="sql">

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</source>