Sunday, May 20, 2018

How to Enable Table level Auditing in Oracle Database

Below DB parameter needs to be set

Show parameter audit_trail=db

audit insert, update, delete on schema.tablename by ACCESS;

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE     BY user
 BY ACCESS;

audit insert, update, delete on apps.xyz by ACCESS;

Once Audit is enabled the audit trail trace file can viewed to see the transaction being recorded to trace.

Also at database level following query can give the details of transactions being done on the table for which audit has been enabled

select os_username,username,userhost,owner,obj_name,action_name,timestamp from dba_audit_object
where owner='APPS' and obj_name like 'XYZ'





No comments: