数据库细粒度访问

1，介绍
DBMS_RLS用于实现细粒度访问控制，Virtual Private Database (VPD)基于该功能实现，只在企业版中支持。

2，实现
基于动态谓词实现，即在解析SQL时动态增加安全规则。

(1)可以控制的操作：index,select,insert,update,delete
--index?
--select in join?
--select->trigger

(2)SYS用户不受任何安全规则限制，有EXEMPT ACCESS POLICY权限的用户也不受此限制。
(3)生成动态谓词的策略函数接口如下：
FUNCTION policy_function (object_schema IN VARCHAR2, object_name VARCHAR2)
        RETURN VARCHAR2
    --- object_schema is the schema owning the table of view.
    --- object_name is the name of table, view, or synonym to which the policy applies.

可以在策略函数中使用SYS_CONTEXT、SYSDATE等。

(4)策略函数不能修改数据库状态(purity level of WNDS (write no database state)).
(5)如果策略函数返回字符串为0，则表示没有限制。

(6)策略函数的有效性在运行时检查。

(7)支持对象类型表,视图,同义词

(8)同一个对象上支持多个安全规则,所有动态谓词以AND方式连接

(9)动态谓词中子查询引用对象的权限检查和对象查找,是针对策略函数的owner

(10)支持列屏蔽（仅SQL）

3，测试行级访问控制
--3.1 透明过滤部分行
SQL> conn mh/mh
SQL> select deptno,count(*) from scott.emp group by deptno

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5
        10          3

SQL>
SQL> CREATE OR REPLACE FUNCTION rls_query_on_hremp(object_schema IN VARCHAR2,
  2                                                object_name   VARCHAR2)
  3    RETURN VARCHAR2 IS
  4  BEGIN
  5    RETURN 'deptno<>30';
  6  END;
  7  /

Function created.

SQL>
SQL> BEGIN
  2    dbms_rls.add_policy('scott',
  3                        'emp',
  4                        'emp_policy',
  5                        'mh',
  6                        'rls_query_on_hremp',
  7                        'select');
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from v$vpd_policy;

no rows selected

SQL>
SQL> select deptno,count(*) from scott.emp
  2  group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        20          5
        10          3
==>无dept=30的数据，OK

--3.2 切换用户是否有效？
SQL> conn scott/tiger
Connected.
SQL>
SQL> select deptno,count(*) from scott.emp
  2  group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        20          5
        10          3

--3.3 关联查询是否有效？
SQL> conn scott/tiger
Connected.
SQL> SELECT e.deptno, COUNT(*)
  2    FROM scott.emp e, scott.dept d
  3   WHERE e.deptno = d.deptno
  4   GROUP BY e.deptno;

    DEPTNO   COUNT(*)
---------- ----------
        20          5
        10          3

--3.4 子查询是否有效？
SQL> SELECT t.deptno
  2    FROM scott.dept t
  3   WHERE deptno = ANY (SELECT DISTINCT deptno FROM scott.emp);

    DEPTNO
----------
        10
        20

--3.5 SYS用户是否有效？
SQL> conn / as sysdba
SQL> select deptno,count(*) from scott.emp
  2  group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5
        10          3

SQL> SELECT e.deptno, COUNT(*)
  2    FROM scott.emp e, scott.dept d
  3   WHERE e.deptno = d.deptno
  4   GROUP BY e.deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5
        10          3

SQL> SELECT t.deptno
  2    FROM scott.dept t
  3   WHERE deptno = ANY (SELECT DISTINCT deptno FROM scott.emp);

    DEPTNO
----------
        10
        20
        30

--3.6 v$vpd_policy包含查询记录
SQL> col sql_id for a20
SQL> col policy_group for a15
SQL> col policy for a15
SQL> col p_f_owner for a4
SQL> col predicate for a12
SQL> SELECT sql_id,
  2         policy_group,
  3         policy,
  4         policy_function_owner p_f_owner,
  5         predicate
  6    FROM v$vpd_policy;

SQL_ID               POLICY_GROUP    POLICY          P_F_ PREDICATE
-------------------- --------------- --------------- ---- ------------
89u421ugd46n2        SYS_DEFAULT     EMP_POLICY      MH   deptno<>30
gjm7h263txfu8        SYS_DEFAULT     EMP_POLICY      MH
gjm7h263txfu8        SYS_DEFAULT     EMP_POLICY      MH   deptno<>30
a31426c4w6zaa        SYS_DEFAULT     EMP_POLICY      MH   deptno<>30
a31426c4w6zaa        SYS_DEFAULT     EMP_POLICY      MH
dahuur4cwvycw        SYS_DEFAULT     EMP_POLICY      MH   deptno<>30
dahuur4cwvycw        SYS_DEFAULT     EMP_POLICY      MH

7 rows selected.

3.7删除RLS
BEGIN
  dbms_rls.drop_policy('scott',
                      'emp',
                      'emp_policy');
END;
/

4，测试行级访问控制
--4.1缺省情况下，进行行访问控制

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT deptno, round(AVG(sal)), MAX(comm) FROM scott.emp GROUP BY deptno;

    DEPTNO ROUND(AVG(SAL))  MAX(COMM)
---------- --------------- ----------
        30            1567       1400
        20            2175
        10            2917

SQL> SQL> CREATE OR REPLACE FUNCTION pf1(oowner IN VARCHAR2, ojname IN VARCHAR2)
  2    RETURN VARCHAR2 AS
  3    con VARCHAR2(200);
  4  BEGIN
  5    con := 'deptno=30';
  6    RETURN(con);
  7  END pf1;
  8  /

Function created.

SQL>
SQL> BEGIN
  2    dbms_rls.add_policy(object_schema     => 'scott',
  3                        object_name       => 'emp',
  4                        policy_name       => 'sp',
  5                        function_schema   => 'mh',
  6                        policy_function   => 'pf1',
  7                        sec_relevant_cols => 'sal,comm');
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> SELECT deptno, round(AVG(sal)), MAX(comm) FROM scott.emp GROUP BY deptno;

    DEPTNO ROUND(AVG(SAL))  MAX(COMM)
---------- --------------- ----------
        30            1567       1400

SQL> col sql_id for a20
SQL> col policy_group for a15
SQL> col policy for a15
SQL> col p_f_owner for a4
SQL> col predicate for a12
SQL> SELECT sql_id,
  2         policy_group,
  3         policy,
  4         policy_function_owner p_f_owner,
  5         predicate
  6    FROM v$vpd_policy;

SQL_ID               POLICY_GROUP    POLICY          P_F_ PREDICATE
-------------------- --------------- --------------- ---- ------------
dhsxa40t5rxs5        SYS_DEFAULT     SP              MH   deptno=30

SQL> BEGIN
  2    dbms_rls.drop_policy(object_schema     => 'scott',
  3                        object_name       => 'emp',
  4                        policy_name       => 'sp');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> SELECT deptno, round(AVG(sal)), MAX(comm) FROM scott.emp GROUP BY deptno;

    DEPTNO ROUND(AVG(SAL))  MAX(COMM)
---------- --------------- ----------
        30            1567       1400
        20            2175
        10            2917

--4.2进行列屏蔽（sec_relevant_cols_opt）
SQL> BEGIN
  2    dbms_rls.add_policy(object_schema     => 'scott',
  3                        object_name       => 'emp',
  4                        policy_name       => 'sp',
  5                        function_schema   => 'mh',
  6                        policy_function   => 'pf1',
  7                        sec_relevant_cols => 'sal,comm',
  8                        sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT deptno, round(AVG(sal)), MAX(comm) FROM scott.emp GROUP BY deptno;

    DEPTNO ROUND(AVG(SAL))  MAX(COMM)
---------- --------------- ----------
        30            1567       1400
        20
        10

SQL> col sql_id for a20
SQL> col policy_group for a15
SQL> col policy for a15
SQL> col p_f_owner for a4
SQL> col predicate for a12
SQL> SELECT sql_id,
  2         policy_group,
  3         policy,
  4         policy_function_owner p_f_owner,
  5         predicate
  6    FROM v$vpd_policy;

SQL_ID               POLICY_GROUP    POLICY          P_F_ PREDICATE
-------------------- --------------- --------------- ---- ------------
dhsxa40t5rxs5        SYS_DEFAULT     SP              MH   deptno=30

SQL> select empno,deptno,sal from scott.emp;

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7369         20
      7499         30       1600
      7521         30       1250
      7566         20
      7654         30       1250
      7698         30       2850
      7782         10
      7788         20
      7839         10
      7844         30       1500
      7876         20
      7900         30        950
      7902         20
      7934         10

14 rows selected.

--4.3在谓词中包含屏蔽列的查询将过滤不满足条件的行（被屏蔽列=null）
SQL> select empno,deptno,sal from scott.emp
  2  where sal>0;

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7499         30       1600
      7521         30       1250
      7654         30       1250
      7698         30       2850
      7844         30       1500
      7900         30        950
6 rows selected.

--4.4 修改策略函数：
SQL>
SQL> BEGIN
  2    dbms_rls.drop_policy(object_schema     => 'scott',
  3                        object_name       => 'emp',
  4                        policy_name       => 'sp');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE FUNCTION pf1(oowner IN VARCHAR2, ojname IN VARCHAR2)
  2    RETURN VARCHAR2 AS
  3    con VARCHAR2(200);
  4  BEGIN
  5    con := 'deptno>=30';
  6    RETURN(con);
  7  END pf1;
  8  /

Function created.

SQL>
SQL> BEGIN
  2    dbms_rls.add_policy(object_schema     => 'scott',
  3                        object_name       => 'emp',
  4                        policy_name       => 'sp',
  5                        function_schema   => 'mh',
  6                        policy_function   => 'pf1',
  7                        sec_relevant_cols => 'sal,comm',
  8                        sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> SELECT deptno, round(AVG(sal)), MAX(comm) FROM scott.emp GROUP BY deptno;

    DEPTNO ROUND(AVG(SAL))  MAX(COMM)
---------- --------------- ----------
        30            1567       1400
        20
        10

SQL> select empno,deptno,sal from scott.emp
  2  where sal>0;

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7499         30       1600
      7521         30       1250
      7654         30       1250
      7698         30       2850
      7844         30       1500
      7900         30        950

6 rows selected.

SQL>
SQL> select empno,deptno,sal from scott.emp;

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7369         20
      7499         30       1600
      7521         30       1250
      7566         20
      7654         30       1250
      7698         30       2850
      7782         10
      7788         20
      7839         10
      7844         30       1500
      7876         20
      7900         30        950
      7902         20
      7934         10

14 rows selected.

--4.5 查询，删除
SQL> col policy_name for a10
SQL> col object_owner for a10
SQL> col object_name for a10
SQL> col function for a10
SQL> col sel for a10
SQL> SELECT policy_name, object_owner, object_name, FUNCTION, sel
  2    FROM dba_policies
  3  where policy_name='SP';

POLICY_NAM OBJECT_OWN OBJECT_NAM FUNCTION   SEL
---------- ---------- ---------- ---------- ----------
SP         SCOTT      EMP        PF1        YES

SQL>
SQL> BEGIN
  2    dbms_rls.drop_policy(object_schema     => 'scott',
  3                        object_name       => 'emp',
  4                        policy_name       => 'sp');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>