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?
Best Answer
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 toDB,EXTENDED
orXML,EXTENDED
(See "Settings for theAUDIT_TRAIL
Initialization Parameter" in Database Security Guide). The audit records would include timestamps and SQL text, and could be queried fromSYS.AUD$
table ifAUDIT_TRAIL
value is set toDB,EXTENDED
orDB
, or read from OS files ifAUDIT_TRAIL
is set toXML,EXTENDED
orXML
. In the latter case you can determine where the audit files are located based on the value of the initialization parameterAUDIT_FILE_DEST
: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 withDBMS_FGA.ADD_POLICY
procedure, e. g.DBMS_FGA.DB_EXTENDED
.