Oracle Fine grained auditing / track select records

In regular object-based auditing, the records can show that a user selected from a specific table, along with other helpful information such as timestamp, client machine name, etc.
What it does not record is what data the user selected from the table.

Audit Trail does not record which particular record was selected. Since reading is not a transaction, the facts are not recorded in Oracle' redo logs, rollback segments or anywhere else.

Also we cannot create trigger on select statement, we can create trigger only on insert / update / delete.



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.



Notice that FGA will show the SQL text, regardless of the setting of AUDIT_TRAIL – no “EXTENDED” necessary here



-------------------------

http://www.dba-oracle.com/security/fga_enhancements.htm
http://peerdba.wordpress.com/2011/01/09/fine-grained-auditing-fga/

-------------------------

begin
dbms_fga.drop_policy(
   object_schema => 'LDBO',
   object_name   => 'TRANSACTIONS',
   policy_name   => 'AUDIT_TRANS'
);
END;
/


Below, I create a policy called AUDIT_TRANS that acts as a trigger for any queries against the TRANSACTIONS where anyone views a TRANSACTIONS row where Quantity>1000000.

begin
   dbms_fga.add_policy(
      object_schema   => 'LDBO',
      object_name     => 'TRANSACTIONS',
      policy_name     => 'AUDIT_TRANS',
      audit_condition => 'Quantity>1000000',
      audit_column    => 'QUANTITY',
      handler_schema  => null,
      handler_module  => null,
      enable          => true
   );
end;
/




This was used to turn auditing on only for select statements against the table. The same can be now be rewritten as:

begin
   dbms_fga.add_policy (
      object_schema=>'CLAIM_SCHEMA',
      object_name=>'CLAIMS',
      policy_name=>'LARGE_CLAIM',
      audit_condition=>
        'CLAIM_AMOUNT>500 OR PAID_AMOUNT>500',
      audit_column=>
        'SSN, PROC_CODE',
      statement_types => 'SELECT'

  );
end;
/

To audit insert, delete, and update for the same table on the same policy condition and columns, we can use:

begin
   dbms_fga.add_policy (
      object_schema=>'CLAIM_SCHEMA',
      object_name=>'CLAIMS',
      policy_name=>'LARGE_CLAIM',
      audit_condition=>
        'CLAIM_AMOUNT>500 OR PAID_AMOUNT>500',
      audit_column=>
        'SSN, PROC_CODE',
      statement_types => 'SELECT,INSERT,UPDATE,DELETE'
  );
end;
/

The above code writes an entry into the table fga_log$ when the table is subjected to insert, update, delete, and select statements; when the auditing condition is satisfied and the audit columns are referenced.



select
   timestamp     c1,
   db_user       c2,
   os_user       c3,
   object_schema c4,
   object_name   c5,
   policy_name   c6,
   sql_text      c7
from
   dba_fga_audit_trail
order by
   timestamp;



--------------------------------------

select count(*)  from sys.fga_log$ where dbuid not in ('USER1','USER2');
select dbuid, count(*)  from sys.fga_log$  group by dbuid  where dbuid  not in ('USER1','USER2');
select * from dba_audit_policies;
select * from dba_audit_policies  where  enabled='NO';
select * from dba_audit_policies  where  enabled='YES';
select * from dba_fga_audit_trail where db_user not in ('USER1','USER2');
select distinct object_name, policy_name from dba_fga_audit_trail where db_user not in ('USER1','USER2');
SELECT  policy_name, object_name, statement_type, os_user, db_user FROM dba_fga_audit_trail;
select * from dba_fga_audit_trail where db_user not in ('USER1','USER2');

---------------------------------–syntax for enable and disable of policy –take below select statements and execute.

select ‘begin dbms_fga.disable_policy(object_schema => ”APP_USER”, object_name => ”’ || object_name || ”’, policy_name => ”’ || policy_name ||”’);end; /’
from dba_fga_audit_trail where db_user not in (‘DBA_USER’);