Oracle FGA Fine-grained audit

Using FGA is a good choice if you want to audit the table, at a certain time, which people, what DML statements to manipulate.

Sql> select * from V$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-production
CORE 11.2.0.1.0 Production
TNS for Linux:version 11.2.0.1.0-production
Nlsrtl Version 11.2.0.1.0-production

Sql>drop table Test Purge;

Sql>create table Test AS SELECT * from Dba_objects;

Sql> exec dbms_fga. Add_policy (object_schema=> ' lcam_test ',object_name=> ' TEST ',policy_name=> ' fga_1 ', Enable=>TRUE, Statement_types=> ' Update,delete ', AUDIT_TRAIL=>DBMS_FGA. Db+dbms_fga. Extended,audit_column_opts=>dbms_fga. Any_columns); --statement_types can be set select,insert,delete,update

sql> Col sql_text Format A80
Sql> Select Sql_text,timestamp from Dba_fga_audit_trail order by TIMESTAMP;

sql> Update test set subobject_name=object_id where Rownum=1;
Sql> Select Sql_text,timestamp from Dba_fga_audit_trail order by TIMESTAMP;
Update test set subobject_name=object_id where Rownum=1 January-December-14

sql> Update test set subobject_name=object_id where rownum<100;--can be seen as audited by SQL statement, not by line change Audit
Sql> Select Sql_text,timestamp from Dba_fga_audit_trail order by TIMESTAMP;
Update test set subobject_name=object_id where rownum<100 January-December-14
Update test set subobject_name=object_id where Rownum=1 January-December-14

Sql> Delete from sys.fga_log$;
Sql> commit;
Sql> Select Sql_text,timestamp from Dba_fga_audit_trail order by TIMESTAMP;

Sql>

There are also ways to invalidate, activate, and delete an audit:

EXEC dbms_fga. Disable_policy (object_schema=> ' lcam_test ',object_name=> ' TEST ',policy_name=> ' FGA_1 ');
EXEC dbms_fga. Enable_policy (object_schema=> ' lcam_test ',object_name=> ' TEST ',policy_name=> ' fga_1 ');
EXEC dbms_fga. Drop_policy (object_schema=> ' lcam_test ',object_name=> ' TEST ',policy_name=> ' fga_1 ');

The location of the official documentation is:

Oracle? Database PL/SQL Packages and Types Reference eachg Release 2 (11.2) DBMS_FGA

Table 66-2 add_policy Procedure Parameters

Parameter	Description	Default Value
object_schema	the schema of the object to be audited.  ( If NULL, the current log-on user schema is assumed.)	NULL
object_name	the name of the object to being audited.	-
policy_name	the unique name of the policy.	-
audit_condition	a condition in A row that indicates A monitoring condition.  NULL  is allowed and acts as  TRUE .	NULL
audit_column	the columns to being checked for access. These can include OLS hidden columns or object type columns. The default,  NULL , causes audit if any column is accessed or affected.	NULL
handler_schema	the schema that contains the event Handler. The default,  NULL , causes the current schema to be used.	NULL
handler_module	the function name of the event Handler Includes the package name if necessary. This function was invoked only after the first row, matches the audit condition in the query is processed. If The procedure fails with an exception, the user SQL statement would fail as well.	NULL
enable	enables The policy if TRUE, which is the default.	TRUE
statement_types	the SQL statement types to which This policy is applicable:  INSERT ,   UPDATE , DELETE , or  SELECT  only.	select
audit_trail	destination (DB or XML) of fine grained audit Records. ALSO Specifies whether to populate  lsqltext  and  lsqlbind  in  fga_log$ .	db+ EXTENDED
audit_column_opts	Establishes whether a statement is audited if the query references any column specified in the Audit_column para Meter or only if all such columns is referenced.	ANY_COLUMNS

Oracle FGA Fine-grained audit