ITPub博客

首页 > 数据库 > Oracle > dbms_rls包的使用

dbms_rls包的使用

原创 Oracle 作者:to_be_dba 时间:2014-03-09 12:25:49 0 删除 编辑
前段时间做一个系统优化时,发现系统中查询语句中添加了很多对会话的限制,系统的名称是xxxxVPD。
当时以为是开发商的什么安全策略,直到看到了oracle的dbms_rls包,才恍然大悟。原来这也是oracle的安全策略啊。

知识点1:dbms_rls包的使用
rls即row level security,是对语句的额外限制。
该特性从10g开始出现。

脚本:
create or replace function my_name 
(
obj_schema varchar2, 
obj_name varchar2

return varchar2 
is 
  d_my_name varchar2(2000);  
BEGIN 
  d_my_name := 'owner_name = sys_context (''USERENV'', ''SESSION_USER'')'; 
  -- this is the restriction you wish to enforce 
  RETURN d_my_name; 
END my_name;  
/

exec dbms_rls.add_policy('SCOTT','ASD','POLICY1','SCOTT','MY_NAME'); 
exec dbms_rls.enable_policy('SCOTT','ASD','POLICY1',true);
exec dbms_rls.refresh_policy('SCOTT','ASD','POLICY1');

exec dbms_rls.drop_policy('SCOTT','ASD','POLICY1');
exec dbms_rls.enable_policy('SCOTT','ASD','POLICY1',false);


实验:
(1)准备
SQL> conn scott/scott@testdb
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as scott


SQL> create table scott.asd as select owner,table_name from dba_tables;


Table created


SQL> select count(1) from scott.asd;


  COUNT(1)
----------
      2780


SQL> select count(1) from scott.asd where owner='SCOTT';


  COUNT(1)
----------
       42

SQL> select count(1) from scott.asd where owner='TERRY';

  COUNT(1)
----------
         0

(2)创建函数
SQL> create or replace function my_name
  2  (
  3  obj_schema varchar2,
  4  obj_name varchar2
  5  )
  6  return varchar2
  7  is
  8    d_my_name varchar2(2000);
  9  BEGIN
 10    d_my_name := 'owner = sys_context (''USERENV'', ''SESSION_USER'')';
 11    -- this is the restriction you wish to enforce
 12    RETURN d_my_name;
 13  END my_name;
 14  /

Function created

(3)创建策略并开启
SQL> exec dbms_rls.add_policy('SCOTT','ASD','POLICY1','SCOTT','MY_NAME');

PL/SQL procedure successfully completed

SQL> exec dbms_rls.enable_policy('SCOTT','ASD','POLICY1',true);

PL/SQL procedure successfully completed

(4)查询和验证
SQL> select count(1) from asd;

  COUNT(1)
----------
        42
       
SQL> explain plan for select count(1) from scott.asd;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1818302109
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    17 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| ASD  |    42 |   714 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OWNER"=SYS_CONTEXT('USERENV','SESSION_USER'))          --可以看到,此处加上了policy的约束
Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected

(5)对特定操作添加policy
SQL> exec dbms_rls.drop_policy('SCOTT','ASD','POLICY1');

PL/SQL procedure successfully completed

SQL> exec dbms_rls.add_policy('SCOTT','ASD','POLICY1','SCOTT','MY_NAME','INSERT,INDEX,UPDATE,DELETE');

PL/SQL procedure successfully completed

SQL> select count(1) from asd;

  COUNT(1)
----------
      2780

注:select语句的policy已经解除了。

下面是update语句使用policy的实例:
SQL> update asd set table_name=table_name||'123';

42 rows updated

SQL> rollback;

Rollback complete

下面是delete语句使用policy的实例:
SQL> delete from asd ;

42 rows deleted

SQL> rollback;

Rollback complete

当我执行insert语句时,出现了问题:
SQL> insert into asd select * from asd;

2780 rows inserted

SQL> rollback;

Rollback complete

SQL> create table asd2 as select * from asd;

Table created

SQL> select count(1) from asd2;

  COUNT(1)
----------
      2780

不太明白为什么insert语句没有使用policy

INDEX语句也不知道哪里可以使用policy:
SQL> create index idx_asd_tab on asd(table_name);

Index created


SQL> select dbms_metadata.get_ddl('INDEX','IDX_ASD_TAB','SCOTT') from dual;


DBMS_METADATA.GET_DDL('INDEX',
--------------------------------------------------------------------------------


  CREATE INDEX "SCOTT"."IDX_ASD_TAB" ON "SCOTT"."ASD" ("TABLE_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
  TABLESPACE "USERS"

(6)经过实验,sys用户下不会受到policy的影响。
在sys用户下创建policy,其他schema会受到此影响,但sys是超级用户,可以看到所有信息。

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as SYS
         
SQL> define
DEFINE _SQLPLUS_RELEASE = "000000000" (CHAR)
DEFINE _EDITOR          = "PLSQLDev" (CHAR)
DEFINE _DATE            = "2014/3/8" (CHAR)
DEFINE _PRIVILEGE       = "" (CHAR)
DEFINE _O_VERSION       = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 " (CHAR)
DEFINE _O_RELEASE       = "000000000" (CHAR)
DEFINE _USER            = "SYS" (CHAR)          --当前用户环境变量中 _USER是SYS
DEFINE _CONNECT_IDENTIFIER = "TESTDB" (CHAR)

SQL> select sys_context('USERENV','SESSION_USER') from dual;

SYS_CONTEXT('USERENV','SESSION
--------------------------------------------------------------------------------
SYS

SQL> create or replace function my_name
  2  (
  3  obj_schema varchar2,
  4  obj_name varchar2
  5  )
  6  return varchar2
  7  is
  8    d_my_name varchar2(2000);
  9  BEGIN
 10    d_my_name := 'owner = sys_context (''USERENV'', ''SESSION_USER'')';
 11    -- this is the restriction you wish to enforce
 12    RETURN d_my_name;
 13  END my_name;
 14  /

Function created

SQL> execute dbms_rls.add_policy('SCOTT','ASD','POLICY1','SYS','MY_NAME');

PL/SQL procedure successfully completed

SQL> select count(1) from scott.asd;

  COUNT(1)
----------
      2780

SQL> exec dbms_rls.enable_policy('SCOTT','ASD','POLICY1',true);

PL/SQL procedure successfully completed

SQL> select count(1) from scott.asd;

  COUNT(1)
----------
      2780

SQL> exec dbms_rls.refresh_policy('SCOTT','ASD','POLICY1');

PL/SQL procedure successfully completed

SQL> select count(1) from scott.asd;

  COUNT(1)
----------
      2780

SQL> select count(1) from scott.asd where owner= sys_context('USERENV','SESSION_USER');

  COUNT(1)
----------
       968

以上pl/sql package的用法:
DBMS_RLS.ADD_POLICY (
   object_schema            IN VARCHAR2 NULL,
   object_name              IN VARCHAR2,
   policy_name              IN VARCHAR2,
   function_schema          IN VARCHAR2 NULL,
   policy_function          IN VARCHAR2,
   statement_types          IN VARCHAR2 NULL,
   update_check             IN BOOLEAN  FALSE,
   enable                   IN BOOLEAN  TRUE,
   static_policy            IN BOOLEAN  FALSE,
   policy_type              IN BINARY_INTEGER NULL,
   long_predicate           IN BOOLEAN  FALSE,
   sec_relevant_cols        IN VARCHAR2,
   sec_relevant_cols_opt    IN BINARY_INTEGER NULL);
   
DBMS_RLS.DROP_POLICY (
   object_schema IN VARCHAR2 NULL,
   object_name   IN VARCHAR2,
   policy_name   IN VARCHAR2);
   
DBMS_RLS.REFRESH_POLICY (
   object_schema IN VARCHAR2 NULL,
   object_name   IN VARCHAR2 NULL,
   policy_name   IN VARCHAR2 NULL);
   
DBMS_RLS.ENABLE_POLICY (
   object_schema IN VARCHAR2 NULL,
   object_name   IN VARCHAR2,
   policy_name   IN VARCHAR2,
   enable        IN BOOLEAN);        


上面讲到的是对所有语句的policy,还可以增加策略组(policy group):
DBMS_RLS.ADD_GROUPED_POLICY(
   object_schema   VARCHAR2,
   object_name     VARCHAR2,
   policy_group    VARCHAR2,
   policy_name     VARCHAR2,
   function_schema VARCHAR2,
   policy_function VARCHAR2,
   statement_types VARCHAR2,
   update_check    BOOLEAN,
   enabled         BOOLEAN,
   static_policy   IN BOOLEAN  FALSE,
   policy_type     IN BINARY_INTEGER NULL,
   long_predicate  IN BOOLEAN  FALSE,
   sec_relevant_cols IN VARCHAR2);

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-1103962/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    397121