Re: Question about Roles

Refer to the Oracle documentation to find out which roles are created for you. Some roles created by Oracle include CONNECT,RESOURCE,DBA. Once you know these, the other roles in DBA_ROLES have to be created by the CREATE ROLE command. Roles are not objects so they don't have an owner (although some argue that all roles are "owned" by SYS). Since they do not have an owner, you can not tell who created them (unless you audit the db).

Once you know which roles you are interested in, then check DBA_ROLE_PRIVS. This will tell you which roles have been granted to a specific user or other role. Then check DBA_SYS_PRIVS and DBA_TAB_PRIVS. It sounds like you already know about these.

HTH,
Brian

Jim Katsos wrote:
>
> Two question really ! (there tougher than they seem)
>
> 1.Using the dba_% views or otherwise. How can I distinguish between oracle
> system roles i.e CONNECT and those created by the 'CREATE ROLE' command.
>
> I can't do this using DBA_ROLES.
>
> 2. Once I have distinguished these I would like to know what sys privs,tab
> privs or for that matter other roles these roles actually have granted to
> them. Yes. I know about
>
> ROLE_ROLE_PRIVS
> ROLE_SYS_PRIVS
> ROLE_TAB_PRIVS
>
> but these don't seem to have the info I need. (I've got a headache)
>
> Any help would be appreciated.
>
> --
> Jim Katsos
> Quest Software Pty Ltd
> http://www.quests.com
> tel +61 3 9811 8068
> mob 0411088578
Received on Fri Jan 21 2000 - 08:42:10 CST