в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:57Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/System Packages/dbms rls - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия