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.

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.

4. 在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)

5. 用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.

6. 测试

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
Trackback

no comment untill now

Add your comment now

切换到手机版