使用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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Java 中常見的細粒度鎖實現Java
- .net core下訪問控制層的實現
- SpringBoot框架整合SpringSecurity實現安全訪問控制Spring Boot框架Gse
- API閘道器控制靈活，支援細粒度許可權控制API
- 如何實現多層目錄下訪問控制器
- SpringMVC+RestFul詳細示例實戰教程（實現跨域訪問）SpringMVCREST跨域
- 訪問使用者中心實現認證
- Flask——訪問控制Flask
- Mongodb訪問控制MongoDB
- 使用nginx控制ElasticSearch訪問許可權NginxElasticsearch訪問許可權
- 細粒度影象分類
- openGauss 訪問控制模型模型
- ABAC訪問控制模型模型
- 類的訪問控制
- kubernetes實踐之十六：RBAC 角色訪問控制
- oracle審計-細粒度(轉)Oracle
- oracle 細粒度審計（fga）Oracle
- Java 細粒度鎖續篇Java
- SAP S/4HANA CDS View的訪問控制實現：DCL介紹View
- docker使用Open Policy Agent(OPA)進行訪問控制Docker
- SAP CDS entity 中使用 @readonly 進行訪問控制
- Ubuntu 增加埠訪問控制Ubuntu
- Swift 中的訪問控制Swift
- IOS - ACL （訪問控制列表）iOS
- 006.Nginx訪問控制Nginx
- HTTP之訪問控制「CORS」HTTPCORS
- Vue前端訪問控制方案Vue前端
- 工作流Activiti框架中的LDAP元件使用詳解！實現對工作流目錄資訊的分散式訪問及訪問控制框架LDA元件分散式
- weblogic控制檯訪問慢問題Web
- 使用PowerBI_Embed實現Web訪問報表 part 1Web
- 使用PowerBI_Embed實現Web訪問報表 part 2Web
- 使用PowerBI_Embed實現Web訪問報表 part 3Web
- 設計模式學習-使用go實現訪問者模式設計模式Go
- 一文讀懂圖資料庫 Nebula Graph 訪問控制實現原理資料庫
- Golang 自制簡易細粒度鎖Golang
- Oracle OCP(52)：細粒度審計Oracle
- Java如何實現延時訪問Java
- 如何打通CMDB，實現就近訪問
- Holer實現Oracle外網訪問Oracle