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

Followup  

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.