使用DBMS_RLS實現細粒度訪問控制
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 精細粒度訪問控制：DBMS_RLS包實現資料庫錶行級安全控制資料庫
- 使用Virtual Private Database實現細粒度訪問控制Database
- 利用VPD細粒度訪問策略實現行級安全性
- 使用DBMS_RLS包實現列級VPD控制
- Spring+Struts 2 實現細粒度許可權控制問題Spring
- 利用VPD細粒度訪問策略實現行級安全性 Step By Step
- 細粒度訪問控制(Fine-Grained access control)的一個簡單例子AI單例
- Java 中常見的細粒度鎖實現Java
- Struts2實現訪問控制
- 【RAC】RAC 實現IP訪問控制
- Java細粒度鎖實現的3種方式Java
- 用訪問控制列表實現網路單向訪問(轉)
- ORACLE FGAC(細粒度許可權控制)（轉）Oracle
- oracle 10g 增強審計(二）----細粒度訪問審計Oracle 10g
- oracle實驗記錄 （精細策略dbms_rls）Oracle
- .net core下訪問控制層的實現
- API閘道器控制靈活，支援細粒度許可權控制API
- 使用動態Proxy和Java ACL進行使用者訪問控制機制實現Java
- 如何實現多層目錄下訪問控制器
- SpringBoot框架整合SpringSecurity實現安全訪問控制Spring Boot框架Gse
- 程式設計實現遍歷ACL訪問控制列表檢查程式訪問許可權程式設計訪問許可權
- 訪問使用者中心實現認證
- SpringMVC+RestFul詳細示例實戰教程（實現跨域訪問）SpringMVCREST跨域
- oracle細粒度審計Oracle
- Mongodb訪問控制MongoDB
- Flask——訪問控制Flask
- RabbitMQ訪問控制MQ
- Nginx訪問控制Nginx
- Swift 訪問控制Swift
- 使用nginx控制ElasticSearch訪問許可權NginxElasticsearch訪問許可權
- Java實現在訪問者模式中使用反射Java模式反射
- oracle審計-細粒度(轉)Oracle
- oracle 細粒度審計（fga）Oracle
- Java 細粒度鎖續篇Java
- ASM條帶細粒度管理ASM
- 檔案和目錄的訪問控制(2)新增訪問控制
- 控制普通使用者訪問資料字典
- ABAC訪問控制模型模型