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

On 16 jul, 10:56, Ronny <nitely..._at_ist-einmalig.de> wrote:
> 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/wns...
> )
>
> 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

A connect_by_root <expression> can be simulated on 9i by using:

replace(sys_connect_by_path(decode(level, 1, <expression>), '~'), '~')

Regards,
Rob. Received on Mon Jul 16 2007 - 04:18:13 CDT