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

From: Ronny <nitelyjoy_at_ist-einmalig.de>
Date: Tue, 17 Jul 2007 02:23:37 -0700
Message-ID: <1184664217.277156.172300@g37g2000prf.googlegroups.com>

Hey Rob, great! Works like a charm!

So my new query is:

SELECT t.rootuser, s.PRIVILEGE

    FROM (SELECT DISTINCT rootuser, granted_role

                     FROM (SELECT     REPLACE
                                         (SYS_CONNECT_BY_PATH
                                                           (DECODE
(LEVEL,
                                                                    1,
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; Oracle 9i and 10g are covered now.

It is technically possible to also rewrite SYS_CONNECT_BY_PATH (new feature in Oracle 9i), to make my (new) SQL running on Oracle 8i?

Thanks,
Ronny Received on Tue Jul 17 2007 - 04:23:37 CDT