VPD策略實現行級安全性

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