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

Try:
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:
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.

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

user_role_privs

Like:

 

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

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.

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

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

Thanks again, top marks

Cheers,
71

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.."