Skip to Main Content
  • Questions
  • How to audit all Select and DML by a user?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, John.

Asked: November 19, 2020 - 8:49 pm UTC

Answered by: Chris Saxon - Last updated: November 20, 2020 - 9:15 am UTC

Category: Database Administration - Version: 12.2.0.3

Viewed 100+ times

You Asked

Good Afternoon,

How can we audit all select and DML statements by a user? I tried this: AUDIT ALL BY JCANTU;

Then I ran a few selects, but the select didn't appear in the audit trail so I ended up just doing a SQL Trace.

Is audit all supposed to create an audit log if I select a table so that the audit log shows that I performed a select operation and it logs the table that the user selected?

Thanks,

and we said...

Some things to check:

Does this apply to current sessions or new ones?

From the docs:

Track the issuance of SQL statements in subsequent user sessions. You can track the issuance of a specific SQL statement or of all SQL statements authorized by a particular system privilege. Auditing operations on SQL statements apply only to subsequent sessions, not to current sessions.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/AUDIT-Traditional-Auditing.html#GUID-ADF45B07-547A-4096-8144-50241FA2D8DD

What is the audit_trail set to?

Has the database been migrated to unified auditing?

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';


If so, you should use this to audit actions. If not, consider moving this anyway as this is the recommended method.

and you rated our response

  (1 rating)

Reviews

November 20, 2020 - 12:33 pm UTC

Reviewer: A reader

Hello Chris,

We are using traditional auditing with these values:

audit_trail=os
AUDIT_SYSLOG_LEVEL = 'LOCAL1.WARNING'

Yes, the user connected after I executed "audit all by jcantu;"

Knowing this, do you expect Oracle to create an audit log showing the select operation and the table name that the user selected?

We should be moving to the new auditing method soon.

Thanks

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.