溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“Oracle VPD的相關功能有哪些”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“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的相關功能有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
