Checking role privileges 1

[071]

Hello,

I would like to check if one of my users has the following role privileges...

grant select on sys.v_$parameter to ARTROLE
/
grant select on sys.dba_free_space to ARTROLE
/
grant select on sys.dba_tablespaces to ARTROLE
/
grant select on sys.v_$session to ARTROLE
/
grant select on sys.v_$lock to ARTROLE
/


Can anyone tell me what the SQL is to do this ?

I have googled this but can't seem to find info on querying roles within Oracle...

Many thanks.

Cheers,
71

 

[carp]

Try:

SELECT privilege, table_name, grantable
  FROM dba_tab_privs
 WHERE grantee = 'ARTROLE';

Of course, this will only give you the privileges granted explicitly to the role. Other privileges may be accessible via roles granted to the role:

SELECT granted_role
  FROM dba_role_privs
 WHERE grantee = 'ARTROLE';

If this query returns no rows, you are done. Otherwise, you will need trace out what privileges are granted to the roles that are granted to ARTROLE.

 

[Turkbear]

Hi,
To get a list of USERS who have had ARTROLE granted to them, try querying

user_role_privs

Like:

Select USERNAME,ADMIN_OPTION,DEFAULT_ROLE 
from
USE_ROLE_PRIVS
where
GRANTED_ROLE = 'ARTROLE';

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[carp]

Turkbear -
Won't that just tell you if the account you're currently logged in under has been granted the role?

071 -
Another view you might want to look at would be role_role_privs. This will tell you which roles are granted to other roles.

 

[071]

Thanks Guys,

Carp, your first query only returned the following:

PRIVILEGE TABLE_NAME GRA
---------------------------------------- ------------------------------ ---
SELECT V_$LOG NO
SELECT V_$SESSION NO
SELECT V_$LOCK NO
SELECT V_$SYSSTAT NO
SELECT V_$ROLLNAME NO
SELECT V_$ROLLSTAT NO
SELECT V_$PARAMETER NO
SELECT V_$ROWCACHE NO
SELECT V_$LIBRARYCACHE NO
SELECT V_$DATABASE NO
SELECT V_$INSTANCE NO

PRIVILEGE TABLE_NAME GRA
---------------------------------------- ------------------------------ ---
SELECT V_$WAITSTAT NO
SELECT V_$ARCHIVE_DEST NO
SELECT V_$SYSTEM_EVENT NO
SELECT DBA_ROLES NO
SELECT DBA_TAB_PRIVS NO
SELECT DBA_USERS NO
SELECT DBA_JOBS NO
SELECT DBA_FREE_SPACE NO
SELECT DBA_DATA_FILES NO
SELECT DBA_TABLESPACES NO
SELECT DBA_TEMP_FILES NO

However, I know for a fact that this role should contain the privileges above ?



Cheers,
71

 

[071]

Oops, busy day...didn't see all of them

Thanks again, top marks

Cheers,
71

 

[Turkbear]

Hi,
Good catch carp, I was too quick to pick a query and forgot about the DBA_ version ( which you cited, even )





To Paraphrase:"The Help you get is proportional to the Help you give.."