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>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-730175/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-730175/