Re: Defined Roles

I believe that the SYS.DBA_ROLE_PRIVS view has exactly what you need. You'll either want to connect as a user that has SELECT_CATALOG_ROLE (accounts that have been granted DBA, such as SYSTEM, would have this) or SYS, then issue one of the following queries:

For a list of roles and the users to whom each is granted, use: SELECT GRANTED_ROLE, GRANTEE, ADMIN_OPTION, DEFAULT_ROLE FROM SYS.DBA_ROLE_PRIVS
ORDER BY GRANTED_ROLE, GRANTEE For a list of users and the roles each is granted, use: SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE FROM SYS.DBA_ROLE_PRIVS
ORDER BY GRANTEE, GRANTED_ROLE Note that when ADMIN_OPTION is shown as "YES" the corresponding user has the ability to grant that role to another user. Additionally, having DEFAULT_ROLE set to "NO" means that the user must explicitly enable the role before they can take advantage of the access it provides.

Craig

Turkbear <john.g_at_dot.spamfree.com> wrote in message news:<1099408531.tSbpsrTvWRplZSHlvCecyQ_at_teranews>...
> dbennet_at_gmail.com wrote:
>
> >I need to print the defined roles and users for our Oracle databases
> >(vs 7.3.3)., preferably also a list of roles and who belongs to the
> >roles. This needs to be done for our auditors.
> >
> >I am not a DBA, so I am not overly fimiliar with Oracle, and am hoping
> >to save a bit of money by not having our vendor do this for us.
> >Thanks.
> >
> >Darryl
> There are several views ( at least in modern ( meaning post 7) versions of Oracle ):
> USER_ROLE_PRIVS
> DBA_ROLES
> ROLE_ROLE_PRIVS
> ROLE_SYS_PRIVS
> ROLE_TAB_PRIVS
> SESSION_ROLES
>
> ..
>
>
> Try them to see which works best for what you need..
>
> Some may require DBA privs to access.
Received on Tue Nov 02 2004 - 20:02:47 CST