Oracle7 - user system privileges

X-NEWS: hhcs comp.databases.oracle: 5219 Relay-Version: VMS News - V6.1 26/02/93 VAX/VMS; site hhcs.gov.au Path: hhcs.gov.au!cbr.hhcs.gov.au!pihlab Newsgroups: comp.databases.oracle
Subject: <None>
Message-ID: <1993Jul21.121111.1_at_cbr.hhcs.gov.au> From: pihlab_at_cbr.hhcs.gov.au
Date: 21 Jul 93 12:11:11 +1000
Organization: Health, Housing, Local Government and Community Services Nntp-Posting-Host: cnb01v
Nntp-Posting-User: wattc
Lines: 39

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.

It seemed logical that the query I wanted would be quite useful to DBAs etc so there should be a DBA_* view provided but I haven't been able to find one that does what I need.

So I tried:   

  SELECT 'Y'
    FROM dba_sys_privs S
   WHERE S.privilege = 'CREATE_SESSION'

         AND (  S.grantee = 'the user id'
             OR
                S.grantee IN (SELECT R.granted_role
                                FROM dba_role_privs R
                              START WITH R.grantee = 'the user id'
                              CONNECT BY R.grantee = R.granted_role
                             )


             );

But it dies with the message:

   ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUP BY, etc.

I haven't used START WITH and CONNECT BY in scripts before.

Can someone please either provide me with a script that does what I need or at least point me in the right direction.

Thankyou.

Bruce... pihlab_at_cbr.hhcs.gov.au   Received on Wed Jul 21 1993 - 04:33:24 CEST