oracle實現資料行級控制-dbms_rls包的應用

dbms_rls包的應用——實現資料庫表行級安全控制
rls即row LEVEL security

以kgis使用者登入建立rls實驗資料表並建立rls函數應用於某表進行測試

C:\Windows\system32>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 1月 30 10:19:59 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn kgis/kgis
已串連。
--建立表並插入資料
SQL> CREATE TABLE xx_test(ID NUMBER,NAME VARCHAR2(255),tag VARCHAR2(20));

表已建立。

SQL> INSERT INTO xx_test VALUES(1,'aa','011');

已建立 1 行。

SQL> INSERT INTO xx_test VALUES(2,'bb','022');

已建立 1 行。

SQL> INSERT INTO xx_test VALUES(3,'cc','033');

已建立 1 行。

SQL> COMMIT;

提交完成。

--建立三個使用者實現不同使用者查詢相應的資料
SQL> create user t01 IDENTIFIED BY t01
  2    default tablespace kgis_data
  3    temporary tablespace TEMP
  4    profile DEFAULT;
 
User created

SQL> create user t02 IDENTIFIED BY t02
  2    default tablespace kgis_data
  3    temporary tablespace TEMP
  4    profile DEFAULT;
 
User created
 
SQL> create user t03 IDENTIFIED BY t03
  2    default tablespace kgis_data
  3    temporary tablespace TEMP
  4    profile DEFAULT;
 
User created

--授權使用者可以查詢該表xx_test
SQL> GRANT SELECT ON xx_test TO t01;
 
Grant succeeded
SQL> GRANT SELECT ON xx_test TO t02;
 
Grant succeeded
SQL> GRANT SELECT ON xx_test TO t03;
 
Grant succeeded

SQL> GRANT CONNECT TO t01;
 
Grant succeeded
SQL> GRANT RESOURCE TO t01;
 
Grant succeeded
SQL> GRANT CONNECT TO t02;
 
Grant succeeded
SQL> GRANT RESOURCE TO t02;
 
Grant succeeded
SQL> GRANT CONNECT TO t03;
 
Grant succeeded
SQL> GRANT RESOURCE TO t03;
 
Grant succeeded

SQL> CREATE PUBLIC SYNONYM xx_test FOR kgis.xx_test;
 
Synonym created

--此時切換到t01使用者發現可以查看到該表的所有資料
SQL> conn t01/t01
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as t01
 
SQL> select * from xx_test;
 
        ID NAME                                                                             TAG
---------- -------------------------------------------------------------------------------- --------------------
         1 aa                                                                               011
         2 bb                                                                               022
         3 cc                                                                               033

--切回到kgis使用者建立使用者權限表，即使用者對應可以查詢的資料
CREATE TABLE rls_users(ID NUMBER,username VARCHAR2(255),usertag VARCHAR2(20));
INSERT INTO rls_users VALUES(1,'T01','011');
INSERT INTO rls_users VALUES(2,'T02','022');
INSERT INTO rls_users VALUES(3,'T03','033');
COMMIT;

--建立rls函數
--函數返回的結果為對應表的where條件
CREATE OR REPLACE FUNCTION f_select_data_security(p_user VARCHAR2,p_table VARCHAR2) RETURN VARCHAR2 IS
   results VARCHAR2(255);
BEGIN
   --SYS_CONTEXT('USERENV','SESSION_USER') 擷取session_user
   --或者直接用輸入的參數p_user
      results := 'tag IN (SELECT usertag FROM kgis.rls_users WHERE username=SYS_CONTEXT(''USERENV'',''SESSION_USER''))';
   RETURN results;
END;

--驗證函式是否能正確返回
SELECT f_select_data_security('T02','XX_TEST') from dual;

--對錶XX_TEST添加rls安全性原則
BEGIN
   dbms_rls.add_policy(object_schema   => 'KGIS',
                       object_name     => 'XX_TEST',
                       policy_name     => 'SELECT_DATA_SECURITY',
                       policy_function => 'F_SELECT_DATA_SECURITY');
END;

--查看是否已經加上rls安全性原則
SELECT * FROM dba_policies WHERE object_owner='KGIS' AND object_name='XX_TEST';
-註：
策略函數中兩個輸入參數（一個是使用者輸入參數，一個是對象輸入參數）不能不寫，儘管可以在函數中沒有用到。否則
提示：ORA-28112: 無法執行策略函數

--切換到以下不同使用者，發現每個使用者只能查詢各自對應的資料
SQL> conn t01/t01
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as t01
 
SQL> select *from xx_test;
 
        ID NAME                                                                             TAG
---------- -------------------------------------------------------------------------------- --------------------
         1 aa                                                                               011
 
SQL> conn t02/t02
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as t02
 
SQL> select *from xx_test;
 
        ID NAME                                                                             TAG
---------- -------------------------------------------------------------------------------- --------------------
         2 bb                                                                               022
 
SQL> conn t03/t03
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as t03
 
SQL> select *from xx_test;
 
        ID NAME                                                                             TAG
---------- -------------------------------------------------------------------------------- --------------------
         3 cc                                                                               033
         
         註：如果某個使用者不想受控制，則可以在rls函數中進行修改，判斷如果是指定的使用者返回空，返回空則可以查看所有資料。