共計(jì) 5497 個(gè)字符，預(yù)計(jì)需要花費(fèi) 14 分鐘才能閱讀完成。
這篇文章主要講解了“Oracle VPD 的相關(guān)功能有哪些”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學(xué)習(xí)“Oracle VPD 的相關(guān)功能有哪些”吧！
測試用的數(shù)據(jù)表使用 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.過濾允許范圍外的行
假設(shè)我們希望 SALES 部門只能看到自己部門的數(shù)據(jù)，不能看到其他部門的數(shù)據(jù)，按照上一節(jié)介紹的內(nèi)容，可以創(chuàng)建相應(yīng)的函數(shù)，添加相應(yīng)的訪問策略即可。
創(chuàng)建函數(shù)
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.查詢驗(yàn)證
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.返回的數(shù)據(jù)均為 SALES 部門中的數(shù)據(jù)。
涉及到敏感列時(shí)，過濾允許范圍外的行
假設(shè)這一次我們希望在查詢某些敏感列時(shí)才過濾，而不查詢這些列時(shí)就不過濾。VPD 通過在添加策略時(shí)指定 sec_relevant_cols 實(shí)現(xiàn)。
添加策略
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查詢驗(yàn)證
-- 不涉及敏感列
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.不涉及敏感列時(shí)，返回所有行，而涉及敏感列時(shí)，則返回可訪問范圍內(nèi)的行，過濾范圍外的行。
涉及到敏感列數(shù)據(jù)時(shí)，脫敏敏感數(shù)據(jù)
最后，假設(shè)我們希望在查詢某些敏感列時(shí)不過濾，但不能顯示數(shù)據(jù)，而只能輸出 NULL。VPD 通過在添加策略時(shí)指定 sec_relevant_cols 和 sec_relevant_cols_opt 實(shí)現(xiàn)。
添加策略
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.查詢驗(yàn)證
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.可以看到，允許范圍內(nèi)（SALES 部門）的行，SAL 和 COMM 都可以正常顯示數(shù)據(jù)，而范圍外的數(shù)據(jù)全部為 NULL。
感謝各位的閱讀，以上就是“Oracle VPD 的相關(guān)功能有哪些”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對 Oracle VPD 的相關(guān)功能有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！
