共计 5497 个字符，预计需要花费 14 分钟才能阅读完成。
这篇文章主要讲解了“Oracle VPD 的相关功能有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“Oracle VPD 的相关功能有哪些”吧！
测试用的数据表使用 Oracle 的示例 Schema Scott 中的 emp 和 dept：
SCOTT-orcl@DESKTOP-V430TU3 desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SCOTT-orcl@DESKTOP-V430TU3 desc dept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SCOTT-orcl@DESKTOP-V430TU3 set pagesize 100
SCOTT-orcl@DESKTOP-V430TU3 SELECT e.deptno, d.dname, ENAME, JOB, SAL, COMM
2 FROM emp e, dept d
3 WHERE d.deptno = e.deptno;
DEPTNO DNAME ENAME JOB SAL COMM
---------- -------------- ---------- --------- ---------- ----------
10 ACCOUNTING KING PRESIDENT 5000
10 ACCOUNTING CLARK MANAGER 2450
10 ACCOUNTING MILLER CLERK 1300
20 RESEARCH FORD ANALYST 3000
20 RESEARCH SMITH CLERK 800
20 RESEARCH JONES MANAGER 2975
30 SALES JAMES CLERK 950
30 SALES TURNER SALESMAN 1500 0
30 SALES MARTIN SALESMAN 1250 1400
30 SALES WARD SALESMAN 1250 500
30 SALES ALLEN SALESMAN 1600 300
30 SALES BLAKE MANAGER 2850
12 rows selected.过滤允许范围外的行
假设我们希望 SALES 部门只能看到自己部门的数据，不能看到其他部门的数据，按照上一节介绍的内容，可以创建相应的函数，添加相应的访问策略即可。
创建函数
SCOTT-orcl@DESKTOP-V430TU3 CREATE OR REPLACE FUNCTION hide_sal_comm (
2 v_schema IN VARCHAR2,
3 v_objname IN VARCHAR2)
4
5 RETURN VARCHAR2 AS
6 con VARCHAR2 (200);
7
8 BEGIN
9 con := deptno=30
10 RETURN (con);
11 END hide_sal_comm;
12 /
Function created.添加策略
SCOTT-orcl@DESKTOP-V430TU3 BEGIN
2 DBMS_RLS.DROP_POLICY(
3 object_schema = scott ,
4 object_name = emp ,
5 policy_name = hide_sal_policy
6 END;
7 /
BEGIN
ERROR at line 1:
ORA-28102: policy does not exist
ORA-06512: at SYS.DBMS_RLS , line 59
ORA-06512: at line 2
SCOTT-orcl@DESKTOP-V430TU3 BEGIN
2 DBMS_RLS.ADD_POLICY(
3 object_schema = scott ,
4 object_name = emp ,
5 policy_name = hide_sal_policy ,
6 policy_function = hide_sal_comm
7 END;
8 /
PL/SQL procedure successfully completed.查询验证
SCOTT-orcl@DESKTOP-V430TU3 SELECT e.deptno, d.dname, ENAME, JOB, SAL, COMM
2 FROM emp e, dept d
3 WHERE d.deptno = e.deptno;
DEPTNO DNAME ENAME JOB SAL COMM
---------- -------------- ---------- --------- ---------- ----------
30 SALES ALLEN SALESMAN 1600 300
30 SALES WARD SALESMAN 1250 500
30 SALES MARTIN SALESMAN 1250 1400
30 SALES BLAKE MANAGER 2850
30 SALES TURNER SALESMAN 1500 0
30 SALES JAMES CLERK 950
6 rows selected.返回的数据均为 SALES 部门中的数据。
涉及到敏感列时，过滤允许范围外的行
假设这一次我们希望在查询某些敏感列时才过滤，而不查询这些列时就不过滤。VPD 通过在添加策略时指定 sec_relevant_cols 实现。
添加策略
SCOTT-orcl@DESKTOP-V430TU3 BEGIN
2 DBMS_RLS.DROP_POLICY(
3 object_schema = scott ,
4 object_name = emp ,
5 policy_name = hide_sal_policy
6 END;
7 /
PL/SQL procedure successfully completed.
SCOTT-orcl@DESKTOP-V430TU3
SCOTT-orcl@DESKTOP-V430TU3
SCOTT-orcl@DESKTOP-V430TU3 BEGIN
2 DBMS_RLS.ADD_POLICY(
3 object_schema = scott ,
4 object_name = emp ,
5 policy_name = hide_sal_policy ,
6 policy_function = hide_sal_comm ,
7 sec_relevant_cols = sal,comm
8 END;
9 /
PL/SQL procedure successfully completed.
SCOTT-orcl@DESKTOP-V430TU3查询验证
-- 不涉及敏感列
SCOTT-orcl@DESKTOP-V430TU3 SELECT e.deptno,ENAME, d.dname, JOB
2 FROM emp e, dept d
3 WHERE d.deptno = e.deptno;
DEPTNO ENAME DNAME JOB
---------- ---------- -------------- ---------
10 KING ACCOUNTING PRESIDENT
10 CLARK ACCOUNTING MANAGER
10 MILLER ACCOUNTING CLERK
20 FORD RESEARCH ANALYST
20 SMITH RESEARCH CLERK
20 JONES RESEARCH MANAGER
30 JAMES SALES CLERK
30 TURNER SALES SALESMAN
30 MARTIN SALES SALESMAN
30 WARD SALES SALESMAN
30 ALLEN SALES SALESMAN
30 BLAKE SALES MANAGER
12 rows selected.
-- 涉及敏感列
1 SELECT e.deptno, d.dname, ENAME, JOB, SAL, COMM
2 FROM emp e, dept d
3* WHERE d.deptno = e.deptno
SCOTT-orcl@DESKTOP-V430TU3 /
DEPTNO DNAME ENAME JOB SAL COMM
---------- -------------- ---------- --------- ---------- ----------
30 SALES ALLEN SALESMAN 1600 300
30 SALES WARD SALESMAN 1250 500
30 SALES MARTIN SALESMAN 1250 1400
30 SALES BLAKE MANAGER 2850
30 SALES TURNER SALESMAN 1500 0
30 SALES JAMES CLERK 950
6 rows selected.不涉及敏感列时，返回所有行，而涉及敏感列时，则返回可访问范围内的行，过滤范围外的行。
涉及到敏感列数据时，脱敏敏感数据
最后，假设我们希望在查询某些敏感列时不过滤，但不能显示数据，而只能输出 NULL。VPD 通过在添加策略时指定 sec_relevant_cols 和 sec_relevant_cols_opt 实现。
添加策略
SCOTT-orcl@DESKTOP-V430TU3 BEGIN
2 DBMS_RLS.DROP_POLICY(
3 object_schema = scott ,
4 object_name = emp ,
5 policy_name = hide_sal_policy
6 END;
7 /
PL/SQL procedure successfully completed.
SCOTT-orcl@DESKTOP-V430TU3
SCOTT-orcl@DESKTOP-V430TU3 BEGIN
2 DBMS_RLS.ADD_POLICY(
3 object_schema = scott ,
4 object_name = emp ,
5 policy_name = hide_sal_policy ,
6 policy_function = hide_sal_comm ,
7 sec_relevant_cols = sal,comm ,
8 sec_relevant_cols_opt = dbms_rls.ALL_ROWS);
9 END;
10 /
PL/SQL procedure successfully completed.查询验证
SCOTT-orcl@DESKTOP-V430TU3 SELECT e.deptno,ENAME, d.dname, JOB, SAL, COMM
2 FROM emp e, dept d
3 WHERE d.deptno = e.deptno;
DEPTNO ENAME DNAME JOB SAL COMM
---------- ---------- -------------- --------- ---------- ----------
10 KING ACCOUNTING PRESIDENT
10 CLARK ACCOUNTING MANAGER
10 MILLER ACCOUNTING CLERK
20 FORD RESEARCH ANALYST
20 SMITH RESEARCH CLERK
20 JONES RESEARCH MANAGER
30 JAMES SALES CLERK 950
30 TURNER SALES SALESMAN 1500 0
30 MARTIN SALES SALESMAN 1250 1400
30 WARD SALES SALESMAN 1250 500
30 ALLEN SALES SALESMAN 1600 300
30 BLAKE SALES MANAGER 2850
12 rows selected.可以看到，允许范围内（SALES 部门）的行，SAL 和 COMM 都可以正常显示数据，而范围外的数据全部为 NULL。
感谢各位的阅读，以上就是“Oracle VPD 的相关功能有哪些”的内容了，经过本文的学习后，相信大家对 Oracle VPD 的相关功能有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
正文完
