1. 创建VPD策略用户VPD,测试用户ALEX
SQL> create user vpd
2 identified by vpd
3 default tablespace users
4 temporary tablespace temp
5 /
User created.
SQL> create user alex
2 identified by alex
3 default tablespace users
4 temporary tablespace temp
5 /
User created.
[@more@]2. 给VPD,ALEX授权SQL> grant connect,resource to vpd,alex;
Grant succeeded.
SQL> grant execute on dbms_rls to vpd;
Grant succeeded.
SQL> grant select on scott.emp to alex,vpd;
Grant succeeded.
3. 在VPD用户下,创建策略规则表
SQL> conn vpd/vpd
Connected.
SQL> create table vpd_ply as select ename,deptno from scott.emp;
Table created.
SQL> select * from vpd_ply;
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
ENAME DEPTNO
---------- ----------
JAMES 30
FORD 20
MILLER 10
14 rows selected.
SQL> insert into vpd_ply values('ALEX',30);
1 row created.
SQL> commit;
Commit complete.
3. 在VPD用户下,创建策略规则功能函数
SQL> create or replace function fun_vpd_emp
2 (
3 p_schema in varchar2, --p_schema和p_table必须,否则出错
4 p_table in varchar2 --PLS-00306: 调用 'fun_vpd_emp' 时参数个数或类型错误
5 )
6 return varchar2
7 as
8 l_retstr varchar2(2000);
9 begin
10 if (p_schema = user) then
11 l_retstr := null;
12 else
13 for user_rec in
14 (
15 select deptno
16 from vpd_ply
17 where ename = user
18 ) loop
19 l_retstr := l_retstr||','||user_rec.deptno;
20 end loop;
21 l_retstr := ltrim(l_retstr,',');
22 if (l_retstr is null) then
23 l_retstr := '0=1';
24 else
25 l_retstr := 'DEPTNO IN ('||l_retstr||')';
26 end if;
27 end if;
28 return l_retstr;
29 end;
30 /
Function created.
SQL> grant execute on fun_vpd_emp to public;
Grant succeeded.
SQL> conn alex/alex
Connected.
SQL> select vpd.fun_vpd_emp('SCOTT','EMP') from dual;
VPD.FUN_VPD_EMP('SCOTT','EMP')
----------------------------------------------------
DEPTNO IN (30)
4. 用VPD用户添加VPD策略
SQL> conn vpd/vpd
Connected.
SQL> begin
2 dbms_rls.add_policy (
3 object_schema => 'SCOTT',
4 object_name => 'EMP',
5 policy_name => 'EMP_DEPTNO_PLY_1',
6 function_schema => 'VPD',
7 policy_function => 'FUN_VPD_EMP',
8 statement_types => 'SELECT'
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_rls.add_policy (
3 object_schema => 'SCOTT',
4 object_name => 'EMP',
5 policy_name => 'EMP_DEPTNO_PLY_2',
6 function_schema => 'VPD',
7 policy_function => 'FUN_VPD_EMP',
8 statement_types => 'INSERT, UPDATE, DELETE',
9 update_check => TRUE
10 );
11 end;
12 /
PL/SQL procedure successfully completed.
5. 测试
SQL> conn scott/tiger
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> conn alex/alex
Connected.
SQL> select count(*) from scott.emp;
COUNT(*)
----------
6
SQL> delete from scott.emp where deptno<>30;
0 rows deleted.
SQL> update scott.emp set sal=sal+100;
6 rows updated.
SQL> commit;
Commit complete.
SQL> insert into scott.emp(empno,ename,deptno) values(9999,'VPD',10);
insert into scott.emp(empno,ename,deptno) values(9999,'VPD',10)
*
ERROR at line 1:
ORA-28115: policy with check option violation
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7319461/viewspace-967090/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7319461/viewspace-967090/