Re: Roles and SQL

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 01 Feb 1999 18:10:01 GMT
Message-ID: <36b8ed4b.94380271@inet16.us.oracle.com>

On Mon, 1 Feb 1999 17:23:47 -0000, "Julian Cowking" <jcowking_at_hrms.co.uk> wrote:

>I am a newbie to Oracle so, this is probably school boy stuff.
>I need to build a list of database roles and a list of database roles to
>which a specified user is assigned.
>Are they stored in system tables as they are in SQL Server?
>Can you help?
>
>Thanks in advance
>JTC
>

That information is stored in the data dictionary. Look at dba_roles and dba_role_privs.

SQL> desc dba_roles

 Name                            Null?    Type
 ------------------------------- -------- ----
 ROLE                            NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                        VARCHAR2(8)

SQL> select * from dba_roles;

ROLE                           PASSWORD
------------------------------ --------
CONNECT                        NO
RESOURCE                       NO
DBA                            NO
SELECT_CATALOG_ROLE            NO
EXECUTE_CATALOG_ROLE           NO
DELETE_CATALOG_ROLE            NO
EXP_FULL_DATABASE              NO
IMP_FULL_DATABASE              NO
RECOVERY_CATALOG_OWNER         NO
AQ_ADMINISTRATOR_ROLE          NO
AQ_USER_ROLE                   NO
SNMPAGENT                      NO
CTXAPP                         NO
PLUSTRACE                      NO
SITE_ADMIN                     NO
SITE_NEWS_ADMIN                NO
WEBDB_DEVELOPER                NO
WEBDB_MENU_UPDATE              NO
OWS_DEFAULT_ROLE               NO
OWS_STANDARD_ROLE              NO
OWS_ADMIN_ROLE                 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
2 where grantee = 'CLBECK';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
CLBECK                         CONNECT                        NO  YES
CLBECK                         DBA                            NO  YES
CLBECK                         RESOURCE                       NO  YES

hope this helps.

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Feb 01 1999 - 12:10:01 CST