General information
Title:
Recursively retrieve a users privs
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
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only