細粒度訪問控制(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