I want to retrieve a user's privs along it's role's privs and it's role's role's privs and so on...

I could do:

select ORA_ROLES.GRANTED_ROLE ROLE_OR_USER_NAME
from SYS.DBA_ROLE_PRIVS ORA_ROLES
where ORA_ROLES.GRANTEE = 'TOTO'

union

select ORA_ROLES1.GRANTED_ROLE ROLE_OR_USER_NAME
from SYS.DBA_ROLE_PRIVS ORA_ROLES1
,SYS.DBA_ROLE_PRIVS ORA_ROLES2
where ORA_ROLES1.GRANTEE = ORA_ROLES2.GRANTED_ROLE
and ORA_ROLES2.GRANTEE = 'TOTO'

union

select ORA_ROLES1.GRANTED_ROLE ROLE_OR_USER_NAME
from SYS.DBA_ROLE_PRIVS ORA_ROLES1
,SYS.DBA_ROLE_PRIVS ORA_ROLES2
,SYS.DBA_ROLE_PRIVS ORA_ROLES3
where ORA_ROLES1.GRANTEE = ORA_ROLES2.GRANTED_ROLE
and ORA_ROLES2.GRANTEE = ORA_ROLES3.GRANTED_ROLE
and ORA_ROLES3.GRANTEE = 'TOTO'

union

select ORA_ROLES1.GRANTED_ROLE ROLE_OR_USER_NAME
from SYS.DBA_ROLE_PRIVS ORA_ROLES1
,SYS.DBA_ROLE_PRIVS ORA_ROLES2
,SYS.DBA_ROLE_PRIVS ORA_ROLES3
,SYS.DBA_ROLE_PRIVS ORA_ROLES4
where ORA_ROLES1.GRANTEE = ORA_ROLES2.GRANTED_ROLE
and ORA_ROLES2.GRANTEE = ORA_ROLES3.GRANTED_ROLE
and ORA_ROLES3.GRANTEE = ORA_ROLES4.GRANTED_ROLE
and ORA_ROLES4.GRANTEE = 'TOTO'



But this is limited to 3 level of nesting and is far from 'elegant'.

Do you have a better way ?

Thanks