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