<None>

From: <pihlab_at_cbr.hhcs.gov.au>
Date: 21 Jul 93 12:11:11 +1000
Message-ID: <1993Jul21.121111.1_at_cbr.hhcs.gov.au>


I really do like the added complexity of managing ROLEs under Oracle7 and the flexibility it gives me, but ...

I need a script to list all system privileges that an Oracle user id has.

How do you determine if an Oracle user id has a particular system privilege eg 'CREATE SESSION'?

I tried:

  SELECT 'Y'
    FROM dba_sys_privs
   WHERE grantee = 'the user id'

         AND privilege = 'CREATE SESSION';

But this failed because some users were granted the privilege via a role rather than directly.

So I tried:

  SELECT 'Y'
    FROM dba_sys_privs S, dba_role_privs R    WHERE S.privilege = 'CREATE SESSION'

         AND ( S.grantee = 'the user id'
               OR
               ( R.grantee = 'the user id'
                 and R.granted_role = S.grantee
               )
             );

But this failed because system privileges can be granted to roles which can be granted to roles can be granted to roles etc can be granted to users and the script needs to be flexible enough to step through the hieracrchy of roles and find ALL system privileges.

So I tried using Oracle's START WITH and CONNECT BY and it wouldn't work on the DBA_* views that I was using above because of Received on Wed Jul 21 1993 - 04:11:11 CEST

Original text of this message