Oracle VPD实例

1、创建相关表并初始化相关数据
SQL> create table enmo_emp (empno number(8),empname varchar2(50),deptname varchar2(20),empsal number,constraint pk_emp primary key (empno));

Table created.

SQL> desc enmo_emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(8)
EMPNAME VARCHAR2(50)
DEPTNAME VARCHAR2(20)
EMPSAL NUMBER

SQL> insert into enmo_emp values(00000001,'Eygle','BOSS',80000);

1 row created.

SQL> insert into enmo_emp values(00000002,'Kamus','BOSS',80000);

1 row created.

SQL> insert into enmo_emp values(00000003,'yangtingkun','BOSS',80000);

1 row created.

SQL> insert into enmo_emp values(00000004,'HongyeDBA','FRESHMAN',8000);

1 row created.

SQL> insert into enmo_emp values(00000005,'songchunfeng','FRESHMAN',8000);

1 row created.

SQL> insert into enmo_emp values(00000006,'Steven','FRESHMAN',8000);

1 row created.

SQL> COMMIT;

SQL> select empname,empsal from enmo_emp where deptname='BOSS';

EMPNAME EMPSAL
-------------------------------------------------- ----------
Eygle 80000
Kamus 80000
yangtingkun 80000

SQL> select empname,empsal from enmo_emp where deptname='FRESHMAN';

EMPNAME EMPSAL
-------------------------------------------------- ----------
HongyeDBA 8000
songchunfeng 8000
Steven 8000

2、创建类似视图
SQL> create view enmo_freshman as select empname,empsal from enmo_emp where deptname='FRESHMAN';

View created.
SQL> create view enmo_boss as SELECT EMPNAME,empsal FROM ENMO_EMP WHERE DEPTNAME='BOSS';

View created.
SQL> select * from enmo_boss;

EMPNAME EMPSAL
-------------------------------------------------- ----------
Eygle 80000
Kamus 80000
yangtingkun 80000

SQL> select * from enmo_freshman;

EMPNAME EMPSAL
-------------------------------------------------- ----------
HongyeDBA 8000
songchunfeng 8000
Steven 8000

3、创建函数
SQL> CREATE OR REPLACE FUNCTION F_ENMO_EMP(V_SCHEMA IN VARCHAR2,V_OBJECT IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN 'deptname=''FRESHMAN'' ';
END;
/

Function created.

4、添加策略
SQL> BEGIN
2 dbms_rls.add_policy ('AWEN','ENMO_EMP','POLICY_ENMO_EMP','AWEN','F_ENMO_EMP');
3 end;
4 /

PL/SQL procedure successfully completed.

5、验证数据
SQL> SELECT EMPNAME,EMPSAL FROM ENMO_EMP;


EMPNAME EMPSAL
-------------------------------------------------- ----------
HongyeDBA 8000
songchunfeng 8000
Steven 8000


SQL> CONN / AS SYSDBA
Connected.
SQL> SELECT EMPNAME,EMPSAL FROM AWEN.ENMO_EMP;


EMPNAME EMPSAL
-------------------------------------------------- ----------
Eygle 80000
Kamus 80000
yangtingkun 80000
HongyeDBA 8000
songchunfeng 8000
Steven 8000

6 rows selected.

6、查看策略
SQL> conn awen/oracle

Connected.
SQL> select object_name from user_policies;

OBJECT_NAME
------------------------------
ENMO_EMP

SQL> desc user_policies;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME NOT NULL VARCHAR2(30)
POLICY_GROUP NOT NULL VARCHAR2(30)
POLICY_NAME NOT NULL VARCHAR2(30)
PF_OWNER NOT NULL VARCHAR2(30)
PACKAGE VARCHAR2(30)
FUNCTION NOT NULL VARCHAR2(30)
SEL VARCHAR2(3)
INS VARCHAR2(3)
UPD VARCHAR2(3)
DEL VARCHAR2(3)
IDX VARCHAR2(3)
CHK_OPTION VARCHAR2(3)
ENABLE VARCHAR2(3)
STATIC_POLICY VARCHAR2(3)
POLICY_TYPE VARCHAR2(24)
LONG_PREDICATE VARCHAR2(3)

SQL> select OBJECT_NAME,POLICY_GROUP,POLICY_NAME from user_policies;

OBJECT_NAME POLICY_GROUP
------------------------------ ------------------------------
POLICY_NAME
------------------------------
ENMO_EMP SYS_DEFAULT
POLICY_ENMO_EMP

7、删除策略
SQL> exec dbms_rls.drop_grouped_policy('AWEN','ENMO_EMP','SYS_DEFAULT','POLICY_ENMO_EMP');

PL/SQL procedure successfully completed.

SQL> SELECT EMPNAME,EMPSAL FROM ENMO_EMP;

EMPNAME EMPSAL
-------------------------------------------------- ----------
Eygle 80000
Kamus 80000
yangtingkun 80000
HongyeDBA 8000
songchunfeng 8000
Steven 8000

6 rows selected.