使用FGA實現SELECT觸發器功能

FGA可以實現細粒度審計(對列、行進行過濾)，甚至對SELECT操作進行審計。在實現細粒度審計時，可以指定handler，從而實現類似觸發器的功能。

本文介紹使用FGA實現對SELECT操作的觸發器功能。
侷限：
發現該功能無法獲取當前行資料。

注意：
（1）handler必須使用如下介面：
PROCEDURE ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 )  AS ...
（2）該過程在處理滿足審計條件的第一行時被呼叫；如果該過程丟擲異常，使用者SQL將失敗。

--1,db version
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 5 21:57:41 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

--2,create test table
create table fga_test(x int,y int);

insert into fga_test
select rownum ra,rownum rb from dba_users;
commit;

create table fga_log(txt varchar2(100),crt_date date default sysdate);

create or replace procedure fga_trigger(p_object_schema VARCHAR2, p_object_name VARCHAR2, p_policy_name VARCHAR2)
as
   l_txt varchar2(100);
begin
    l_txt := p_object_schema||'.'||p_object_name||'.'||p_policy_name;
    insert into fga_log(txt) values(l_txt);
    commit;
end;
/

--3,fga
begin
DBMS_FGA.ADD_POLICY (
   object_schema      =>  'MH',
   object_name        =>  'FGA_TEST',
   policy_name        =>  'MH_TEST_POLICY',
   audit_condition    =>  'x > 5',
   audit_column       =>  'X',
   handler_schema     =>   NULL,
   handler_module     =>   NULL,
   enable             =>   TRUE,
   statement_types    =>  'SELECT',
   audit_trail        =>   DBMS_FGA.DB + DBMS_FGA.EXTENDED,
   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);
end;
/

SQL> select * from fga_test where x<4;

         X          Y
---------- ----------
         1          1
         2          2
         3          3

SQL> select * from fga_log;

no rows selected

SQL> select * from fga_test where x<7;

         X          Y
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6

6 rows selected.

SQL> select * from fga_log;

no rows selected

SQL> r
  1* select * from fga_log

no rows selected

SQL> select count(*) from fga_test;

  COUNT(*)
----------
        73

SQL> select * from fga_log;

no rows selected

SQL> select x from fga_test where x=10;

         X
----------
        10

SQL> select * from fga_log;

no rows selected

--4,fga with handler

begin
DBMS_FGA.DROP_POLICY (
   object_schema      =>  'MH',
   object_name        =>  'FGA_TEST',
   policy_name        =>  'MH_TEST_POLICY');

DBMS_FGA.ADD_POLICY (
   object_schema      =>  'MH',
   object_name        =>  'FGA_TEST',
   policy_name        =>  'MH_TEST_POLICY',
   audit_condition    =>  'x > 5',
   audit_column       =>  'X',
   handler_schema     =>   'MH',
   handler_module     =>   'FGA_TRIGGER',
   enable             =>   TRUE,
   statement_types    =>  'SELECT',
   audit_trail        =>   DBMS_FGA.DB + DBMS_FGA.EXTENDED,
   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);
end;
/

SQL> select * from fga_log;

no rows selected

SQL> select count(*) from fga_test;

select count(*) from fga_test
                     *
ERROR at line 1:
ORA-00600: internal error code, arguments: [4412], [0x3DB6AB84], [0x0], [], [],
[], [], []

SQL> SQL> select * from mh.fga_log;

TXT                              CRT_DATE
---------------------------------------------
MH.FGA_TEST.MH_TEST_POLICY       06-APR-15

==>看樣子handler生效了，看看自治事務是否能解決ORA-00600錯誤。

--5,fga with autonomous_transaction handler:

create or replace procedure fga_trigger(p_object_schema VARCHAR2, p_object_name VARCHAR2, p_policy_name VARCHAR2)
as
PRAGMA AUTONOMOUS_TRANSACTION;
   l_txt varchar2(100);
begin
    l_txt := p_object_schema||'.'||p_object_name||'.'||p_policy_name;
    insert into fga_log(txt) values(l_txt);
    commit;
end;
/

begin
DBMS_FGA.DROP_POLICY (
   object_schema      =>  'MH',
   object_name        =>  'FGA_TEST',
   policy_name        =>  'MH_TEST_POLICY');

DBMS_FGA.ADD_POLICY (
   object_schema      =>  'MH',
   object_name        =>  'FGA_TEST',
   policy_name        =>  'MH_TEST_POLICY',
   audit_condition    =>  'x > 5',
   audit_column       =>  'X',
   handler_schema     =>   'MH',
   handler_module     =>   'FGA_TRIGGER',
   enable             =>   TRUE,
   statement_types    =>  'SELECT',
   audit_trail        =>   DBMS_FGA.DB + DBMS_FGA.EXTENDED,
   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);
end;
/

SQL>
SQL> select count(*) from fga_test;

  COUNT(*)
----------
        73

SQL>  select * from mh.fga_log;

TXT                              CRT_DATE
---------------------------------------------
MH.FGA_TEST.MH_TEST_POLICY       06-APR-15
MH.FGA_TEST.MH_TEST_POLICY       06-APR-15

SQL> select count(*) from fga_test where x<4;

  COUNT(*)
----------
         3

SQL>  select * from mh.fga_log;

TXT                              CRT_DATE
---------------------------------------------
MH.FGA_TEST.MH_TEST_POLICY       06-APR-15
MH.FGA_TEST.MH_TEST_POLICY       06-APR-15

 

SQL> select * from fga_test where x=10;

         X          Y
---------- ----------
        10         10

SQL>  select * from mh.fga_log;

TXT                              CRT_DATE
---------------------------------------------
MH.FGA_TEST.MH_TEST_POLICY       06-APR-15
MH.FGA_TEST.MH_TEST_POLICY       06-APR-15
MH.FGA_TEST.MH_TEST_POLICY       06-APR-15