Oracle精细访问控制

1、创建自定义应用环境

       必须拥有create any context权限。

       create or replace context testenv using scott.emp_ctx；emp_ctx包方案对象用来设置应用环境属性

        定义和设置应用环境属性：

                 DBMS_SESSION.set_context('context_name','attr_name','attr_value')；

                  create or replace package scott.emp_ctx as

                                    procedure set_deptno；

                    end

                    create or replace package body scott.emp_ctx as

                          procedure set_deptno is

                         begin

                               if  sys_context('userenv','session_user')='SCOTT' then

                                       DBMS_SESSION.set_context('testenv','scott_attr1','10');

                                  elseif sys_context('userenv','session_user')='system' then

                                        DBMS_SESSION.set_context('testenv','system_attr1','10'); 

                                        DBMS_SESSION.set_context('testenv','system_attr2','20');

                                     end if;

                                 end;

                           end;

2、创建安全策略函数

       必须带2个参数：第一个方案名     第二个方案对象名

        返回的谓词中只能包含where子句后面的条件,不能有where关键字,也不能包含其他字句(如order by)

        create or replace package scott.emp_security as

                 function select_lmt(schema varchar2,object varchar2) return varchar2;

                  function update_lmt(schema varchar2,object varchar2) return varchar2;

          end;

           create or replace package body scott.emp_security as

                 function select_lmt(schema varchar2,object varchar2) return varchar2

                  is 

                              return_sql varchar2(100);

                    begin

                              return_sql := '1=1';

                              if  user = 'SCOTT' then

                                    return_sql := 'deptno=sys_context('textenv','scott_attr1')';

                               elseif user='SYSTEM' then

                                      return_sql :='deptno=sys_context("testenv","system_attr1")'

                                                              || 'or '

                                                              'deptno=sys_context("testenv","system_attr2")';

                                   end if;

                                    return return_sql;

                                end;

                  function update_lmt()......

     end;

3、将安全策略函数关联到表或视图

        DBMS_RLS包进行安全策略的管理

          execute DBMS_RLS.add_policy (object_schema=>'scott',

                         object_name=>'emp',

                         policy_name=>'select_policy/update_policy',

                         function_schema=>'scott',

                          policy_function=>'emp_security.select_lmt/ipdate_lmt',

                           statement_types=>'select/update、insert、delete') ;

4、创建登陆触发器

         激活安全策略  create or replace trigger logon_trig

                                        after logon on database call scott.emp_ctx.set_deptno;

          alter trigger trig_name disable/enable;

          drop trigger trig_name;

5、安全策略管理

       DBA_POLICIES/ALL_POLICIES/USER_POLICIES

        启用、禁用安全策略

              execute DBMS_RLS.enable_policy(object_schema=>'',object_name=>'',policy_name=>'',enable=>FALSE/TRUE);

         删除安全策略

               execute DBMS_RLS.drop_policy(object_schema=>'',object_name=>'',policy_name=>'');

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