Fine Grained Auditing (FGA) is independant to oracle standard audit

在oracle-base上找到一篇介绍FGA的文章,取了片段引用过来。原文可以参考http://www.oracle-base.com/articles/10g/Auditing_10gR2.php
FGA与oracle standard audit是相对独立的,具体可以参考引用的例子,长久以来错误的以为oracle standard audit是FGA的基础,惭愧啊。
Fine Grained Auditing (FGA)
Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine grained auditing is used.

First, create a test table.
CONN audit_test/password CREATE TABLE emp ( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) ); INSERT INTO emp (empno, ename, sal) VALUES (9999, ‘Tim’, 1); INSERT INTO emp (empno, ename, sal) VALUES (9999, ‘Larry’, 50001); COMMIT;
The following policy audits any queries of salaries greater than £50,000.
CONN sys/password AS sysdba BEGIN DBMS_FGA.add_policy( object_schema => ‘AUDIT_TEST’, object_name => ‘EMP’, policy_name => ‘SALARY_CHK_AUDIT’, audit_condition => ‘SAL > 50000’, audit_column => ‘SAL’); END; /
Querying both employees proves the auditing policy works as expected.
CONN audit_test/password SELECT sal FROM emp WHERE ename = ‘Tim’; SELECT sal FROM emp WHERE ename = ‘Larry’; CONN sys/password AS SYSDBA SELECT sql_text FROM dba_fga_audit_trail; SQL_TEXT —————————————— SELECT sal FROM emp WHERE ename = ‘Larry’ 1 row selected. SQL>
Extra processing can be associated with an FGA event by defining a database procedure and associating this to the audit event. The following example assumes the FIRE_CLERK procedure has been defined:
BEGIN DBMS_FGA.add_policy( object_schema => ‘AUDIT_TEST’, object_name => ‘EMP’, policy_name => ‘SALARY_CHK_AUDIT’, audit_condition => ‘SAL > 50000’, audit_column => ‘SAL’, handler_schema => ‘AUDIT_TEST’, handler_module => ‘FIRE_CLERK’, enable => TRUE); END; /
The DBMS_FGA package contains the following procedures:
ADD_POLICY
DROP_POLICY
ENABLE_POLICY
DISABLE_POLICY
In Oracle9i fine grained auditing was limited queries, but in Oracle 10g it has been extended to include DML statements, as shown by the following example.
— Clear down the audit trail. CONN sys/password AS SYSDBA TRUNCATE TABLE fga_log$; SELECT sql_text FROM dba_fga_audit_trail; no rows selected. — Apply the policy to the SAL column of the EMP table. BEGIN DBMS_FGA.add_policy( object_schema => ‘AUDIT_TEST’, object_name => ‘EMP’, policy_name => ‘SAL_AUDIT’, audit_condition => NULL, — Equivalent to TRUE audit_column => ‘SAL’, statement_types => ‘SELECT,INSERT,UPDATE,DELETE’); END; / — Test the auditing. CONN audit_test/password SELECT * FROM emp WHERE empno = 9998; INSERT INTO emp (empno, ename, sal) VALUES (9998, ‘Bill’, 1); UPDATE emp SET sal = 10 WHERE empno = 9998; DELETE emp WHERE empno = 9998; ROLLBACK; — Check the audit trail. CONN sys/password AS SYSDBA SELECT sql_text FROM dba_fga_audit_trail; SQL_TEXT ————————————– SELECT * FROM emp WHERE empno = 9998 INSERT INTO emp (empno, ename, sal) VALUES (9998, ‘Bill’, 1) UPDATE emp SET sal = 10 WHERE empno = 9998 DELETE emp WHERE empno = 9998 4 rows selected. — Drop the policy. CONN sys/password AS SYSDBA BEGIN DBMS_FGA.drop_policy( object_schema => ‘AUDIT_TEST’, object_name => ‘EMP’, policy_name => ‘SAL_AUDIT’); END; /