Skip to Main Content
  • Questions
  • Auditing logons with V$SESSION.AUDSID in AWR

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Martin.

Asked: November 10, 2016 - 12:16 pm UTC

Last updated: November 10, 2016 - 3:27 pm UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hi Tom,
I have got request from an audit company to monitor logon/off events. Of course in this case it would be really silly question and maybe it really is. The trick is that they want to also track program and module (v$session). This information I cannot find out in any DBA_AUDIT views. There is SESSIONID and OS_PROCESS and I have enabled AWR, so it would be great if I could perform join with V$ACTIVE_SESSION_HISTORY and all be fine. But in V$ACTIVE_SESSION_HISTORY you can find V$SESSION.SID and V$SESSION.SERIAL#.

Auditors wants to see
OSUSER MACHINE SCHEMANAME PROGRAM MODULE LOGON LOGOFF

DBA_AUDIT views are missing this key information or I would expect to have there SID and SERIAL# at least. Who cares about DBA_AUDIT_SESSION.SESSIONID when it says nothing, because you cannot get any information on basis this from history.

I hope that it is because my pure knowledge and Oracle is able to provide it.

Thanks a lot for your hints.

Bottom line: we want to kick off triggers we have implemented since version 9, because of this.


and Chris said...

AWR only samples sessions. So you can't rely on this for auditing.

But it seems to me that UNIFIED_AUDIT_TRAIL has everything you need plus a whole lot more:

http://docs.oracle.com/database/121/REFRN/GUID-B7CE1C02-2FD4-47D6-80AA-CF74A60CDD1D.htm#REFRN29162

You'll need to enable unified auditing first to use this though:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/security/sec_uni_audit/sec_uni_audit.html

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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library