"audit create session by session" vs. "audit create session by access"?

kupa picture kupa · Feb 9, 2011 · Viewed 17.4k times · Source

When I enabling auditing for create session by the following way:

audit create session by session;

Then I am querying the following:

select * from dba_priv_audit_opts;

The result is:

USERNAME | PROXY_NAME | AUDIT_OPTION   | SUCCESS   | FAILURE  |
...............................................................
 -       | -          | CREATE SESSION | BY ACCESS | BY ACCESS|

But, when I enabling auditing for create session by the following way:

 audit create session by access;

Then I am querying the following:

select * from dba_priv_audit_opts;

The result is the same:

USERNAME | PROXY_NAME | AUDIT_OPTION   | SUCCESS   | FAILURE  |
...............................................................
 -       | -          | CREATE SESSION | BY ACCESS | BY ACCESS|

Why? Do you have any idea?

Answer

Vincent Malgrat picture Vincent Malgrat · Feb 9, 2011

11gR2 and above:

BY SESSION is effectively disabled and all auditing is done per access.

11gR1 and below:

The difference between BY SESSION and BY ACCESS is that when you specify BY SESSION Oracle will try to merge multiple audit entries into one record when the session and the action audited match.

It only works for SQL statements other than DDL though, From the above link:

If you specify statement options or system privileges that audit data definition language (DDL) statements, then the database automatically audits by access regardless of whether you specify the BY SESSION clause or BY ACCESS clause.

Since CREATE SESSION is a DDL statement Oracle audits this statement by access.