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函数中进行修改，判断如果是指定的用户返回空，返回空则可以查看所有数据。