Auditing / FGA

Server Setup:-

To Enable:-

audit_trail = none/false/os/db/true/db,extended/xml/xml,extended

To disable:-

audit_trail = none/false

db/true - Auditing i enabled and audit records are stored in AUD$.

db,extended - Auditing records are stored in AUD$ and two more columns sql_bind, sql_text are included.

xml - Auditing records are stored as xml files in auidt_file_dest directory.

xml,extended - with sql_bin and sql_text.

os - Auditing records are stored in audit_file_dest.

audit_sys_operations = true/false -> If set as true then it will also audit the operations perform by sysdba/sysoper and sys use on the database. If false then it will audit only other user.

Eg:-

> alter system set audit_trail = db scope=spfile;
> shut immediate;
> startup;
> audit all by manzoor by access; -- where manzoor is username.
> audit select table , update table, delete table, insert table by
manzoor by access;
> audit execute procedure by manzoor by access;


The audit trail records are stored in AUD$, you can query the result using the dba_views.

dba_audit_trail -> standard auditing AUD$
dba_fga_audit_trail -> Fine grained Auditing FGA_LOG$
dba_common_audit_trail -> Both standard and FGA
V$xml_audit_trail -> if xml or xml,extended

Maintenance:-

Auditing should be planned carefully to control the quantity of audit information. Only audit specific operations or objects of interest.

The database audit trail must be deleted or archived on a regular basis to prevent the sys.aud$ table growing to a unacceptable size. Only dba should have maintenance access to the audit trail. Auditing modifications of the data in the audit trail itself can be achieved using the following statement.

> audit insert, delete, select, update on sys.aud$ by access;

The os audit files should be secured at the os level by assigning the correct file permission.

Fine Grained Auditing

Fine grained auditing extends orcale standard auditing capabilities by allowing the user to audit action based on user_defined predicates. It is independent of the audit_trail parameter. The FGA records are stored in FA_LOG$ table.

begin
dbms_fga.add_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'FGAEXP',
audit_condition => 'SAL > 50000',
audit_column => 'SAL'
);
end;
/

Here we have created a fga policy to audit if any users executes a query to list the rows and if those rows contains the sal > 50000 then it will get recorded in the fga_log$ and can be viewed through the dba_fga_audit_trail column.

SQL> select * from emp;

EMPNO NAME SAL
---------- ------------------------------ ----------
1 Anif 40000
2 Jameel 50000
3 Kannan 60000

SQL> select * from emp where name = 'Jameel';

EMPNO NAME SAL
---------- ------------------------------ ----------
2 Jameel 50000

SQL> select * from emp where name = 'Anif';

EMPNO NAME SAL
---------- ------------------------------ ----------
2 Jameel 40000


SQL> select sql_text from dba_fga_audit_trail;

SQL_TEXT
-----------------------------------------
select * from emp
select * from emp where name = 'Kannan'

Here we can see that the query "select * from emp where name = 'Anif'" is not get
listed in the dba_fga_audit_trail view because the salary is less than 50000.

Auditing DML Operations:

begin
dbms_fga.add_policy
(
OBJECT_SCHEMA => 'MANZOOR',
OBJECT_NAME => 'EMP',
POLICY_NAME => 'FGA_DML',
AUDIT_CONDITION => NULL,
AUDIT_COLUMN => 'SAL',
STATEMENT_TYPES => 'SELECT,INSERT,UPDATE,DELETE'
);
end;
/

SQL> select sql_text from dba_fga_audit_trail;


SQL_TEXT
-----------------------------------------------
select * from emp where name = 'Anif'
insert into emp values (4,'Sahrmi',55555)
update emp set sal = 45000 where name = 'Anif'
delete from emp where sal=45000

here whenever the sal column is being affected by any DML operations it will be audited.


Refer below for more info...
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/auditing.htm