利用FGAC/VPD重寫使用者SQL

FGAC/VPD可以從安全方面限制使用者在行級別的訪問許可權，對於安全性考慮的應用有很大幫助。

另外，在一些特別特殊的情況下，可以透過它改寫使用者SQL的謂詞（where condition）。

這裡step by step做一個例子，以供以後參考。

SQL> show user
USER is "HAOZHU_USER"
SQL> create table testfgac as select * from dba_tables;

Table created.

1.建立一個context用來標示你希望標示的屬性。

這裡我用my_role來代表當前使用者是否是我定義的“DB_OWNER”

SQL> create context hao_context using hao_package;

Context created.

SQL> create or replace procedure hao_package
( my_role in varchar2)
as
begin
    dbms_session.set_context (
            'HAO_CONTEXT',
            'MY_ROLE',
            my_role
    );
end;

Procedure created.

2.建立一個login trigger，如果當前使用者是'HAO'，那麼設立它的role是DB_OWNER.
SQL> create or replace trigger hao_ctx_trig
after logon on database
declare
   my_role varchar2(50);
begin
   select decode(sys_context('USERENV','SESSION_USER'),'HAO',
   'DB_OWNER','NOT_DB_OWNER')
   into my_role from dual;
   hao_package(my_role);
exception
   when NO_DATA_FOUND then
null;
   when OTHERS then
        raise;
end;

Trigger created.

3.檢視login trigger生效沒有
SQL>  show user
USER is "SYS"
SQL>  select sys_context('HAO_CONTEXT','MY_ROLE') myrole from dual;

MYROLE
--------------------
NOT_DB_OWNER

SQL> conn hao/hao
Connected.
SQL>  select sys_context('HAO_CONTEXT','MY_ROLE') myrole from dual;

MYROLE
--------------------
DB_OWNER

4.最重要的一步，建立一個function用來作為返回你想修改的謂詞。
SQL>  create or replace function hao_secure
(my_owner in varchar2,
 my_obj in varchar2)
return varchar2
as
   ret varchar2(2000);
begin

select decode(sys_context('HAO_CONTEXT','MY_ROLE'),'DB_OWNER',null,'LAST_ANALYZED<=sysdate-1') into ret from dual;
   return ret;
end;

Function created.

5.加上policy,對dml語句和select都生效
SQL>  begin
   dbms_rls.add_policy (
OBJECT_SCHEMA => 'HAOZHU_USER',
object_name => 'TESTFGAC',
policy_name => 'my_p',
policy_function => 'HAO_SECURE',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
update_check    => TRUE
);
end;
/

PL/SQL procedure successfully completed.

6.測試
SQL> conn hao/hao
Connected.
SQL> select sys_context('HAO_CONTEXT','MY_ROLE') myrole from dual;

MYROLE
-------------------------

DB_OWNER

SQL> select count(*) from HAOZHU_USER.TESTFGAC;

  COUNT(*)
----------
      1043

SQL> conn another_user/another_user
Connected.
SQL> select count(*) from HAOZHU_USER.TESTFGAC;

  COUNT(*)
----------
       934