Re: Auditing query

From: Adam Musch <>
Date: Wed, 24 Feb 2010 10:26:58 -0600
Message-ID: <>

Dick did answer the original question; if CREATE SESSION is being audited at the system level or user level, well, there you are.

There's a clarification needed. Do you need the list of users which have been directly granted CREATE SESSION? That's easy -- look for that in DBA_SYS_PRIVS. However, if you need the list of users which inherit CREATE SESSION through a role, you'll need a hierarchical query including DBA_USERS (to get users, not roles for the starting point), DBA_ROLE_PRIVS (user to role and role to role) and DBA_SYS_PRIVS (role -> Create session). Plus, just because a user has CREATE SESSION doesn't mean they can log in -- if the account is locked (also in DBA_USERS), it doesn't matter that the user has CREATE SESSION. It's not a trivial exercise, and I found it easier when I had to do it to unwind the stack, as my dependency graph had only four levels and, back in the 8i days, had loops in it. Thanks, Oracle Applications!

On Wed, Feb 24, 2010 at 9:11 AM, J. Dex <> wrote:
> Thanks.  Maybe I am expecting the wrong thing or there is no way to query it
> or I have something set up wrong.   I was trying to find something that
> would show each users name individually listed and that create session has
> been set for them.  This comes back in my databases exactly as it does here
> without any names explicitly listed and just says "create session".

Adam Musch
Received on Wed Feb 24 2010 - 10:26:58 CST

Original text of this message