Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have some table and I have enabled auditing on this table. Currently, I am collecting the data about who has inserted records and at what time. Now I also want to log what was the exact query he/she used to insert the record. I created a trigger which at present only logs the user and date to audit data collection table. This is code to the trigger:

create or replace trigger auditer
after insert on user01.sometab
for each row
insert into sys.auditlogs values(user,sysdate)

How can I use the same trigger to access the insert statement and insert that statement into the audit table? Is it even possible?

share|improve this question
1  
SYS is a special internal account, you should never issue DDL or DML against its objects. –  Vincent Malgrat May 14 '13 at 15:12
    
@VincentMalgrat Unless you want to purge the audit trail with truncate sys.aud$ or delete sys.fga_log$ ;-) –  Yasir Arsanukaev May 14 '13 at 15:27
    
@YasirArsanukaev Nice try, but there is the DBMS_AUDIT_MGMT package. I'm pretty sure there is a package/command to manipulate every sys objects you need to modify :) –  Vincent Malgrat May 14 '13 at 15:39
    
@Vincent jeez, you've just severely reduced my natural destructive potential! –  Yasir Arsanukaev May 14 '13 at 15:46
add comment

1 Answer 1

up vote 6 down vote accepted

You can meet all your requirements with Standard Database Auditing or with Fine-Grained Auditing.

For the standard auditing to also capture the SQL statements, you would set AUDIT_TRAIL initialization parameter to DB,EXTENDED or XML,EXTENDED (See "Settings for the AUDIT_TRAIL Initialization Parameter" in Database Security Guide). The audit records would include timestamps and SQL text, and could be queried from SYS.AUD$ table if AUDIT_TRAIL value is set to DB,EXTENDED or DB, or read from OS files if AUDIT_TRAIL is set to XML,EXTENDED or XML. In the latter case you can determine where the audit files are located based on the value of the initialization parameter AUDIT_FILE_DEST:

SQL> show parameter audit

NAME                 TYPE        VALUE
-------------------- ----------- ------------------------------
audit_file_dest      string      /u01/app/oracle/admin/orcl/adump

As far as FGA is concerned, I have already described it in my other post here. You would just need to set the other value for the audit_trail parameter when creating the FGA with DBMS_FGA.ADD_POLICY procedure, e. g. DBMS_FGA.DB_EXTENDED.

share|improve this answer
    
Thank you very much :) –  Rajat Saxena May 14 '13 at 15:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.