dbms_rls包的应用——实现数据库表行级安全控制
rls即row LEVEL security以kgis用户登录创建rls实验数据表并创建rls函数应用于某表进行测试C:\Windows\system32>sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on 星期三 1月 30 10:19:59 2013Copyright (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 createdSQL> 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_testSQL> GRANT SELECT ON xx_test TO t01; Grant succeededSQL> GRANT SELECT ON xx_test TO t02; Grant succeededSQL> GRANT SELECT ON xx_test TO t03; Grant succeededSQL> GRANT CONNECT TO t01; Grant succeededSQL> GRANT RESOURCE TO t01; Grant succeededSQL> GRANT CONNECT TO t02; Grant succeededSQL> GRANT RESOURCE TO t02; Grant succeededSQL> GRANT CONNECT TO t03; Grant succeededSQL> GRANT RESOURCE TO t03; Grant succeededSQL> CREATE PUBLIC SYNONYM xx_test FOR kgis.xx_test; Synonym created--此时切换到t01用户发现可以查看到该表的所有数据SQL> conn t01/t01Connected 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/t01Connected 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/t02Connected 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/t03Connected 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函数中进行修改，判断如果是指定的用户返回空，返回空则可以查看所有数据。