本节分三个场景分别介绍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

ITPub博客

Oracle vs PostgreSQL DBA（22）- Oracle VPD#2

最新文章