本节分三个场景分别介绍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。 **参考资料** [Using Oracle Virtual Private Database to Control Data Access](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/using-oracle-vpd-to-control-data-access.html#GUID-E6343F14-933E-4980-A67A-D5AAEC5743C5)