FGA (fine-grained auditing) : Fine-grain auditing (FGA) allows us to audit users accessing data of a certain criteria. As per standard auditing we can audit select, insert, update and delete operations. We use the package dbms_fga to add, remove, enable and disable FGA auditing policies, it is virtually impossible to bypass these policies, one nice feature is that we can attach handlers (like a trigger) to the policies which can execute procedures.
Setting FGA at schema level: Below is the code to set FGA at schema level in which all tables are audited.
CURSOR Get_Tab IS
WHERE Owner='TEST';----->Schema name
FOR t IN Get_Tab LOOP
dbms_fga.Add_Policy(Object_scHema => 'TEST',Object_Name => t.Table_Name,
Policy_Name => 'chk_' ||t.Table_Name,
Statement_Types => 'insert,update,delete,select');
Now, do some activities in test schema:
SQL> conn test/test@mytest
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SQL> select * from a;
SQL> select * from b;;
select * from b;
ERROR at line 1:
ORA-00911: invalid character
SQL> select * from b;
6 rows selected.
SQL> update a set id=0;
4 rows updated.
SQL> delete from a ;
4 rows deleted.
SQL> insert into b values(22);
1 row created.
Executing FGA on 'test':
select db_user ,scn, sql_text from dba_fga_audit_trail where db_user='TEST';
Above output is displaying all SQL_TEXT fired on schema test for all tables.