Oracle: list roles granted to a user 

Joined:
04/09/2007
Posts:
573

April 07, 2010 15:46:14
SQL> desc user_role_privs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(30)
 GRANTED_ROLE                                       VARCHAR2(30)
 ADMIN_OPTION                                       VARCHAR2(3)
 DEFAULT_ROLE                                       VARCHAR2(3)
 OS_GRANTED                                         VARCHAR2(3)

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
PET_STORE                      DBA                            NO  NO  NO
PET_STORE                      DBA_READ_ONLY                  NO  YES NO
PET_STORE                      PET_WRITE                      NO  YES NO
PET_STORE                      PET_READ_ONLY                  NO  YES NO

SQL> desc dba_role_privs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRANTEE                                            VARCHAR2(30)
 GRANTED_ROLE                              NOT NULL VARCHAR2(30)
 ADMIN_OPTION                                       VARCHAR2(3)
 DEFAULT_ROLE                                       VARCHAR2(3)

SQL> select * from dba_role_privs where grantee = 'PET_STORE';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
PET_STORE                      DBA                            NO  NO
PET_STORE                      PET_WRITE                      NO  YES
PET_STORE                      DBA_READ_ONLY                  NO  YES
PET_STORE                      PET_READ_ONLY                  NO  YES


Further reference:
Oracle Database Security Guide (11g Release 2)
[ Comment  | Tags ]
 
Easy email testing with http://www.ximailstop.com