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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, John.

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

Last updated: November 24, 2020 - 1:41 am UTC

Version: 12.2.0.3

Viewed 10K+ times! This question is

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 Chris 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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, November 20, 2020 - 12:33 pm UTC

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
Connor McDonald
November 24, 2020 - 1:41 am UTC

I once wrote a parser for the syslog entries to map them into a database table. I checked back on that code, because it tried to locate/parse any columns from each line of syslog. This is the list I came up with at the time (plus the host and the length tags)

ACTION
AUTH$GRANTEE
CLIENT_TERMINAL
CLIENT_USER
COMMENT$TEXT
DATABASE_USER
DBID
ENTRYID
LOGOFF$DEAD
LOGOFF$LREAD
LOGOFF$LWRITE
LOGOFF$PREAD
OBJ$CREATOR
OBJ$NAME
OBJ$PRIVILEGES
OS$USERID
PRIV$USED
PRIVILEGE
RETURNCODE
SESSIONCPU
SESSIONID
STATEMENT
STATUS
TERMINAL
USERHOST
USERID
NEW$OWNER
NEW$NAME
SES$ACTIONS
SES$LABEL

So whilst you should get object and statement (number), but unless the SQL text is in the action, then I think you're out of luck.

If you audit into the database, then you can get SQL text with:

AUDIT SELECT ANY TABLE BY [myuser] BY ACCESS;

because we have a SQL_TEXT column in DBA_AUDIT_TRAIL

More to Explore

Security

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