1

I have enabled default auditing in one of my database. Oracle 11g. Audit_trail is set to DB, other than that for a particular schema 'test' I have set:

audit all on test by access

With the above setting, I know I can get to know what action has been performed (alter,create,update,etc) but will I be able to fetch the SQL statments (all DML operations) with the exact SQL statement fired from that schema from the audit records? Let me know how?

If the above is not possible, what is required to fetch the exact SQL statement that is run by the schema.

1 Answer 1

1

AUDIT_TRAIL

If you want the SQL text to be captured, you need to use the extended option for audit_trail (db, extended or xml, extended).

alter system set AUDIT_TRAIL=db, extended scope=spfile;

or

alter system set AUDIT_TRAIL=xml, extended scope=spfile;

Changing this setting requires a database restart to take effect.

For FGA you can specify a different audit trail, for example: audit_trail => DBMS_FGA.DB_EXTENDED, that way you can leave audit_trail on DB. If you use FGA, you do not even need to use AUDIT, just create the policy you need.

4
  • Won't FGA help in meeting my requirement without changing it to xml?
    – tesla747
    May 28, 2016 at 17:45
  • @tesla747 You do not need to change it to XML, there is db, extended. Updated my answer for FGA. May 28, 2016 at 17:53
  • As mentioned here orafaq.com/wiki/DBMS_FGA -- this should simply work right? No need to change to extended or XML. Simply create a policy on a schema to capture everything, it will work right?
    – tesla747
    May 28, 2016 at 17:58
  • @tesla747 That is what I wrote also. May 29, 2016 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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