Hierarchical SQL: lack of CONNECT_BY_ROOT feature in Oracle <=9i

From: Ronny <nitelyjoy_at_ist-einmalig.de>
Date: Mon, 16 Jul 2007 01:56:56 -0700
Message-ID: <1184576216.763347.255500@o61g2000hsh.googlegroups.com>

Hello SQL developers out there, I have a little challenge for your lunchtime:

I successfully wrote an SQL query for Oracle 10g that reveals all sys privileges granted to users (disregarding all roles in between). Because roles can be granted to roles themselves, it must be a hierarchical query.

Because Oracle 9i lacks the CONNECT_BY_ROOT feature, it leads to an "ORA-00923: FROM keyword not found where expected" on a version 9 server.
( ==> new features in 10gR1: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/wnsql.htm#i972833
)

Can someone experienced in hierarchical SQL rewrite the following SQL query for me, running well on Oracle 9i (or even 8i)?

SELECT t.rootuser, s.PRIVILEGE

    FROM (SELECT DISTINCT rootuser, granted_role

                     FROM (SELECT
                              CONNECT_BY_ROOT r.grantee as rootuser,
grantee, granted_role
                              FROM dba_role_privs r
                           CONNECT BY r.grantee = PRIOR
r.granted_role) rp,
                          dba_users u
                    WHERE u.username = rp.rootuser) t,
         dba_sys_privs s

   WHERE t.granted_role = s.grantee
UNION
SELECT grantee AS root, PRIVILEGE

    FROM dba_sys_privs
   WHERE grantee IN (SELECT username

                       FROM dba_users)

ORDER BY 1, 2; Thank you very much for your help,
Ronny Received on Mon Jul 16 2007 - 03:56:56 CDT