Home » SQL & PL/SQL » SQL & PL/SQL » roles granted to a role (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0)
roles granted to a role [message #669511] Thu, 26 April 2018 13:25 Go to next message
wtolentino
Messages: 309
Registered: March 2005
Senior Member

how can find what are the roles that was granted to a role?

thanks.
Re: roles granted to a role [message #669512 is a reply to message #669511] Thu, 26 April 2018 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67303
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's a general way to find what you are searching for:
SQL> col table_name format a23
SQL> col comments format a90
SQL> select table_name, comments from dict where lower(comments) like '%role%' order by 1;
TABLE_NAME              COMMENTS
----------------------- ------------------------------------------------------------------------------------------
ALL_COL_PRIVS           Grants on columns for which the user is the grantor, grantee, owner,
                         or an enabled role or PUBLIC is the grantee
ALL_COL_PRIVS_RECD      Grants on columns for which the user, PUBLIC or enabled role is the grantee
ALL_TAB_PRIVS           Grants on objects for which the user is the grantor, grantee, owner,
                         or an enabled role or PUBLIC is the grantee
ALL_TAB_PRIVS_RECD      Grants on objects for which the user, PUBLIC or enabled role is the grantee
COLUMN_PRIVILEGES       Grants on columns for which the user is the grantor, grantee, owner, or
                         an enabled role or PUBLIC is the grantee
DBA_AUDIT_OBJECT        Audit trail records for statements concerning objects, specifically: table, cluster, view,
                         index, sequence,  [public] database link, [public] synonym, procedure, trigger, rollback
                        segment, tablespace, role, user
DBA_ROLES               All Roles which exist in the database
DBA_ROLE_PRIVS          Roles granted to users and roles
DBA_SCHEDULER_JOB_ROLES All scheduler jobs in the database by database role
DBA_SYS_PRIVS           System privileges granted to users and roles
ROLE_ROLE_PRIVS         Roles which are granted to roles
ROLE_SYS_PRIVS          System privileges granted to roles
ROLE_TAB_PRIVS          Table privileges granted to roles
SESSION_ROLES           Roles which the user currently has enabled.
TABLE_PRIVILEGES        Grants on objects for which the user is the grantor, grantee, owner,
                         or an enabled role or PUBLIC is the grantee
USER_AUDIT_OBJECT       Audit trail records for statements concerning objects, specifically: table, cluster, view,
                         index, sequence,  [public] database link, [public] synonym, procedure, trigger, rollback
                        segment, tablespace, role, user
USER_ROLE_PRIVS         Roles granted to current user
Have a closer look at those starting with ROLE.
Re: roles granted to a role [message #669515 is a reply to message #669512] Thu, 26 April 2018 14:04 Go to previous messageGo to next message
wtolentino
Messages: 309
Registered: March 2005
Senior Member
thanks so much.

there is a role name R_LPA_EXECUTE that i know was been granted to another role. i tried to use this data dictionary ROLE_ROLE_PRIVS however there was no rows found. i think since the account that i am using can only see what was been granted to it i will not be able to see it. and i do not have access to the dba_ data dictionary view. if i am to build a query and have our dba run it is it the DBA_ROLE_PRIVS that i should be using

for example:
select * from dba_role_privs
where role = 'R_LPA_EXECUTE'





[Updated on: Thu, 26 April 2018 14:08]

Report message to a moderator

Re: roles granted to a role [message #669516 is a reply to message #669515] Thu, 26 April 2018 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67303
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If this is one of the roles you have access and you are not a DBA then you cannot access to this information and have to rely on your DBA.

Re: roles granted to a role [message #669517 is a reply to message #669515] Thu, 26 April 2018 14:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2977
Registered: January 2010
Location: Connecticut, USA
Senior Member
wtolentino wrote on Thu, 26 April 2018 15:04

for example:
select * from dba_role_privs
where role = 'R_LPA_EXECUTE'
There is no column ROLE in DBA_ROLE_PRIVS, so what you posted would have failed.

SY.
Re: roles granted to a role [message #669537 is a reply to message #669517] Fri, 27 April 2018 08:44 Go to previous messageGo to next message
wtolentino
Messages: 309
Registered: March 2005
Senior Member
i know that i will not be able to see the roles that were not accessible to my account. if i will request this to the dba:

select * from role_role_privs
where role = 'R_LPA_EXECUTE';

that will give me the info that i am looking for right? it's just that i have to build the query and thee dba will execute it.

thanks.

Re: roles granted to a role [message #669539 is a reply to message #669537] Fri, 27 April 2018 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67303
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
that will give me the info that i am looking for right?

Only if the role is granted to you and only if you want the roles granted to R_LPA_EXECUTE (which does not seem to be your question although you didn't post it clearly: "there is a role name R_LPA_EXECUTE that i know was been granted to another role").
For a DBA, it is:
select granted_role from dba_role_privs where granted_role='R_LPA_EXECUTE';
Re: roles granted to a role [message #669545 is a reply to message #669539] Fri, 27 April 2018 13:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2977
Registered: January 2010
Location: Connecticut, USA
Senior Member
Did you mean:

select grantee from dba_role_privs where granted_role='R_LPA_EXECUTE';

SY.
Re: roles granted to a role [message #669546 is a reply to message #669545] Fri, 27 April 2018 14:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67303
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sure! Smile

Re: roles granted to a role [message #669554 is a reply to message #669511] Sat, 28 April 2018 15:55 Go to previous message
EdStevens
Messages: 1233
Registered: September 2013
Senior Member
Pete Finnigan long ago wrote a procedure to recurse down through roles granted to roles granted to roles granted to roles granted to roles . .. .

http://www.petefinnigan.com/tools.htm
Previous Topic: Executing the run time file at SQLPLUS
Next Topic: how to use variable to store comma seperated values
Goto Forum:
  


Current Time: Fri Aug 14 06:46:54 CDT 2020