Implement fine-grained auditing

Implement fine-grained auditing from Oracle9i, you can use DBMS_FGA to audit the SELECT statement of the specified table, but in 9i, you can only audit the select statement, DML auditing can be implemented in 10 Gb. 1. Before the audit policy takes effect, you must analyze the table, because DBMS_FGA can analyze the table correctly only in CBO mode, so that it can use the CBO optimization mode SQL> analyze table hr. employees compute statistics; the following statement deletes statistics: analyze table table_name delete statistics; 2. You can specify audit_condition to set monitoring conditions (for example, select a part of Records) 3. You can specify audit_column to audit special fields. 4. statement_types includes "SELECT, UPDATE, INSERT. DELETE. If multiple ', 'are required to be separated by 5, only non-SYS users can be audited, SYS users cannot audit Summary of audit role Description ADD_POLICY Procedure Creates an audit policy using the supplied predicate as the audit role Procedure Disables an audit role ydrop_policy Procedure Drops an audit role Procedure Enables an audit policy salary & commission_pct (the following indicates that when two queries are performed simultaneously, audit. Otherwise, audit is not performed.) begindbms_fga.add_policy (OBJECT_SCHEMA => 'hr', OBJECT_NAME => 'ployees', POLICY_NAME => 'policy1 ', AUDIT_CONDITION => 'department _ id = 60', AUDIT_COLUMN => 'salary, commission_pct ', HANDLER_SCHEMA => null, HANDLER_MODULE => null, ENABLE => true, STATEMENT_TYPES => 'select', audit_column_opts => DBMS_FGA.ALL_COLUMNS); end;/conn hr/hrSQL> select 'salary, commission_pct 'from employees (audit only when two fields are queried simultaneously) begindbms_fga.drop_policy (OBJECT_SCHEMA => 'hr', OBJECT_NAME => 'ployees', POLICY_NAME => 'employee Y1 '); end; /salary | commission_pct (which indicates auditing when either of the two is queried) begindbms_fga.add_policy (OBJECT_SCHEMA => 'hr', OBJECT_NAME => 'ployees ', POLICY_NAME => 'employee 2', AUDIT_CONDITION => 'department _ id = 60', AUDIT_COLUMN => 'salary, commission_pct ', HANDLER_SCHEMA => null, HANDLER_MODULE => null, ENABLE => true, STATEMENT_TYPES => 'select', audit_column_opts => DBMS_FGA.ANY_COLUMNS); end;/conn hr/hr (queries are audited in the following three cases) SQL> select 'salary, commission_pct 'from employeesSQL> select 'salary from employeesSQL> select commission_pct' from employees if no partition is written, it is equivalent to ANY_COLUMNS Partition ', POLICY_NAME => 'abc', AUDIT_CONDITION => null, AUDIT_COLUMN => 'salary, commission_pct ', HANDLER_SCHEMA => null, HANDLER_MODULE => null, ENABLE => true, STATEMENT_TYPES => 'select'); end;/SYS verify whether FGA takes effect: SQL> select count (*) from fga_log $; finally, let's take a look at how to view the audit results: select * from dba_audit_policies; -- select db_user, timestamp, SQL _text, SQL _bind from orders; -- Audit Result SELECT text FROM dba_Views where view_name = upper ('dba _ FGA_AUDIT_TRAIL '); -- View record source