细粒度访问控制(Fine-Grained access control)的一个简单例子

限制用户只能从ad.t0320_2表中查询到和自己用户名匹配的记录，例如：A用户登陆后，执行select * from ad.t0320_2，在FGAC的作用下，相当于为这条语句增加了一个隐含的条件where username='A'，等于执行了select * from ad.t0320_2 where username='A'仅返回符合username='A'这一条件的记录，实现方法如下

###connect SYS，赋予必要的权限给ad用户
grant create any context to ad;
grant ADMINISTER DATABASE TRIGGER to ad;

###connect ad
---创建测试表
create table t0320_1 as select * from all_users;
create table t0320_2 as select * from all_users;

set pagesize 120
select * from t0320_1;

USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
LIVREORG                              167 20140922 15:28:42
PRECISE_CLIENT1                       129 20121018 15:05:25
PRECISE_CLIENT2                       130 20121018 15:05:32
AD                                     53 20120921 08:52:05
AID                                   100 20121008 15:43:04
BD                                     54 20120921 08:52:06
CD                                     55 20120921 08:52:06
TSBZ                                  156 20131021 15:17:02
PD                                     57 20120921 08:52:06
SD                                     58 20120921 08:52:06
UD                                     59 20120921 08:52:06
TYCXCL                                158 20140424 13:41:43
POWERQUERY                            107 20121012 16:33:04
MIG                                    73 20120921 16:20:35
OB60                                   79 20120921 20:38:26
ID                                     92 20121006 17:31:35
MD                                     93 20121006 17:32:00
ZD                                     94 20121006 17:32:00
WANGGUAN                              104 20121009 10:04:09
JD                                    111 20121016 22:12:33
REAL_DSG_AICBS                        131 20121103 19:09:16
MNG                                   136 20121212 11:08:44
POWERCZ                               150 20130131 16:09:17
POWERUSER                             140 20121218 15:56:50
DSSUSER                               139 20121217 10:58:45
POB                                   141 20121226 19:50:44
BOSSJH                                151 20130222 20:36:02
DIP                                    14 20120920 14:59:43
ORACLE_OCM                             21 20120920 15:00:40
DSG                                    80 20120929 13:08:58
CAPES                                 154 20130415 17:56:32
DMONITOR                              153 20130311 10:40:45
DBSNMP                                 30 20120920 15:07:49
APPQOSSYS                              31 20120920 15:07:51
WMSYS                                  32 20120920 15:08:52
EXFSYS                                 42 20120920 15:17:08
CTXSYS                                 43 20120920 15:17:24
OLAPSYS                                46 20120920 15:18:50
SYSMAN                                 50 20120920 15:20:21
XDB                                   127 20121018 10:32:54
AVCOLLUSER                            148 20130122 23:19:44
SYS                                     0 20120920 14:55:29
SYSTEM                                  5 20120920 14:55:29
OUTLN                                   9 20120920 14:55:31
MGMT_VIEW                              52 20120920 15:24:06

---创建一个有权设置application context属性的package，这个package的功能是为t0320_ctx这个context创建一个名为uid的属性，并将该属性赋值成从ad.t0320_1表里得到的user_id，这个user_id是根据登陆的用户名匹配得到的
create or replace package t0320_pkg_set_ctx is
procedure t0320_prc_set_ctx;
end;
/

create or replace package body t0320_pkg_set_ctx is
procedure t0320_prc_set_ctx is
v_userid number;
begin
select user_id into v_userid from ad.t0320_1 where username=sys_context('userenv','session_user');
dbms_session.set_context(namespace=>'t0320_ctx',attribute=>'uid',value=>v_userid);
end;
end;
/

---赋予ad.t0320_pkg_set_ctx的执行权限给public
grant execute on ad.t0320_pkg_set_ctx to public;

---创建secure application context，指定使用ad.t0320_pkg_set_ctx才能修改context属性
create context t0320_ctx using ad.t0320_pkg_set_ctx;

---创建一个logon trigger，根据登陆的用户调用t0320_prc_set_ctx设置context
CREATE OR REPLACE TRIGGER cap_login_info
  AFTER LOGON ON DATABASE
  BEGIN
    ad.t0320_pkg_set_ctx.t0320_prc_set_ctx;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR
        (-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);
 END;
/

------创建一个package，该package包含一个function，该function用于返回动态条件，创建安全策略的时候会指向这个package，安全策略函数里必须包含两个Varchar2类型的入参，设计这两个入参的用途是能够根据不同的对象生成不同的条件，不管我们是否用到这两个入参，定义时必须要把它们包括进来，否则在对表进行操作时会遇到ORA-28112: failed to execute policy function错误
create or replace package t0320_pkg_policy is
function t0320_func_policy(d1 varchar2,d2 varchar2) return varchar2;
end;
/

create or replace package body t0320_pkg_policy is
function t0320_func_policy(d1 varchar2,d2 varchar2) return varchar2 is
v_predicate varchar2(1000);
begin
v_predicate:='user_id=sys_context(''t0320_ctx'',''uid'')';
return v_predicate;
end;
end;
/

---对t0320_1表添加安全策略
exec dbms_rls.add_policy(object_schema=>'ad',object_name=>'t0320_2',policy_name=>'t0320_pol1',function_schema=>'ad',policy_function=>'t0320_pkg_policy.t0320_func_policy');

---将表ad.t0320_2的访问权限赋给ud、zd用户
grant select on ad.t0320_2 to zd,ud;

---使用不同的用户登陆数据库后Select * from t0320_1，观察select到的记录是否经过了过滤
***使用ud用户登陆，只能看到自己的记录
connect ud/Uiop246!
select sys_context('T0320_CTX','uid') from dual;
SYS_CONTEXT('T0320_CTX','UID')
--------------------------------------------------------------------------------
59

select * from ad.t0320_2;
USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
UD                                     59 20120921 08:52:06

***使用zd用户登陆，只能看到自己的记录
connect zd/Uiop246!
select sys_context('T0320_CTX','uid') from dual;
SYS_CONTEXT('T0320_CTX','UID')
--------------------------------------------------------------------------------
94

select * from ad.t0320_2;
USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
ZD                                     94 20121006 17:32:00

 

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

转载于:http://blog.itpub.net/53956/viewspace-1467718/