实现数据行的权限控制(Policy的应用)

1.  Oracle Policy说明

　　Policy应用于数据行访问权限控制时，其作用简而言之，就是在查询数据表时，自动在查询结果上加上一个Where子句。如果该查询已有where子句，则在该Where子句后面加上"And ..."。

由Oracle Policy自动加入的Where子句的内容，通常由一个函数来实现。而进行数据行访问权限控制算法实现的结果，也是通过该函数返回。

2.  测试表

测试环境10.2.0.5

create table t_policy

(

  id number(12),

  name varchar2(30)

);

insert into t_policy values(1,'a');

insert into t_policy values(2,'b');

insert into t_policy values(3,'c');

commit;

3.  Oracle Policy语法

添加Policy

DBMS_RLS.ADD_POLICY (

  object_schema            INVARCHAR2 NULL,

  object_name              INVARCHAR2,

  policy_name              INVARCHAR2,

  function_schema          INVARCHAR2 NULL,

  policy_function          INVARCHAR2,

   statement_types          IN VARCHAR2 NULL,

  update_check             INBOOLEAN  FALSE,

  enable                   INBOOLEAN  TRUE,

  static_policy            INBOOLEAN  FALSE,

  policy_type              INBINARY_INTEGER NULL,

  long_predicate           IN BOOLEAN  FALSE,

  sec_relevant_cols        INVARCHAR2,

  sec_relevant_cols_opt    INBINARY_INTEGER NULL);

 

创建测试的Policy函数：

CREATE OR REPLACE FunctionFn_GetPolicy(P_Schema In Varchar2,

                                       P_Object In Varchar2)

Return Varchar2 Is

  p_uservarchar2(30);

Begin

  p_user:= sys_context ('userenv','session_user');

 

  case

    whenp_user = '1001' then Return 'ID=1';

    whenp_user = '1002' then Return 'ID=2';

    elsereturn null;

  endcase;

End;

该policy函数的作用，主要是用来返回查询条件到DML语句

 

添加policy

Begin

 DBMS_RLS.Add_Policy(Object_Schema  => 'Scott',

          Object_Name     => 'T_Policy',

          Policy_Name     =>'T_TestPolicy',

          Function_Schema => 'Scott',

          Policy_Function => 'Fn_GetPolicy',

          Statement_Types => 'Select,Insert,Update,Delete',

          Static_Policy   => TRUE,

          Policy_Type => NULL);

End;

/

 

删除Policy

begin

 DBMS_RLS.drop_policy(object_name => 'T_POLICY',

                       policy_name =>'T_TestPolicy');

end;

/

 

设定Policy状态

dbms_rls.enable_policy

dbms_rls.disable_grouped_policy

4.  相关执行计划

SQL> conn 1001/1001

Connected.

SQL> select * fromscott.t_policy;

 

        ID NAME

----------------------------------------

         1 a

 

SQL> set autotracetraceonly
SQL> select * fromscott.t_policy;

Execution Plan
----------------------------------------------------------
Plan hash value: 1252456560

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    1 |    30 |     3  (0) | 00:00:01 |
|*  1 | TABLE ACCESS FULL | T_POLICY |    1 |    30 |     3  (0) | 00:00:01 |
------------------------------------------------------------------------------

Predicate Information(identified by operation id):
---------------------------------------------------
   1 - filter("ID"=1)

Note
-----
   - dynamic sampling used for this statement

5.  相关视图

USER_POLICIES

DBA_POLICIES