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