精细访问控制(可以使不同用户执行相同操作﹐处理不同的数据库数据,通过使用系统包DBMS_RLS实现的)

1.      建立应用上下文

Create or replace context empenv using scott.ctx

2.      建立包过程设置应用上下文属性

Create or replace package scott.ctx as

 Procedure set_deptno;

End;

/

Create or replace package body scott.ctx as

  Procedure set_deptno is

    Id number;

  Begin

    If sys_context(‘userenv’,’session_user’)=’JONES’ then

       Dbms_session.set_context(‘empenv’,’deptno’,20);

    Elsif sys_context(‘userenv’,’session_user’)=’BLAKE’ then

       Dbms_session.set_context(‘empenv’,’deptno’,30);

    else

       Dbms_session.set_context(‘empenv’,’deptno’,10);

           End if;

           End;

          End;

3.      设置登录触发器

Create or replace trigger login_trig

  After logon on database call scott.ctx.set_deptno

/

4.      建立策略函数

Create or replace package scott.emp_security as

  Function emp_sec(p1 varchar2,p2 varchar2) return varchar2;

End;

/

Create or replace package body scott.emp_security as

 Function emp_sec(p1 varchar2,p2 varchar2) return varchar2

  Is

   D_predicate varchar2(2000);

  Begin

    If user not in(‘sys’,’system’,’scott’) then

        D_predicate :=’deptno=sys_context(“empenv”,”deptno”)’;

       Return d_predicate;

    End if;

      Return ‘1=1’;

  End;

End;

5.      增加策略

exec dbms_rls.add_policy (

              object_schema =>           'scott',

              object_name =>             'emp',

              policy_name =>             'emp_policy',

              function_schema =>    'scott',

              policy_function =>    'emp_security.emp_sec',

              statement_types =>    'SELECT',

              update_check =>            TRUE,

              enable =>                   TRUE

     );