Re: How to view the content of the role?

From: Ben Harmon <benh_at_dascom.com>
Date: 1997/07/18
Message-ID: <5qokro$bnv@news9.noc.netcom.net>#1/1

Hi Stephen,

I might be able to help a bit. I've been wrestling with the same thing. Hopefully if someone knows better they will respond, correct me, and help us both out. If not, well, here goes:

If I understand your question correctly, you really want to know the privileges associated with a role, or possibly all those associated with a user. Roles are treated like users, so the privilege information for both is stored in the same location.

To do this, I have been writing a package (not quite done yet!) that queries a number of the system tables.

Here's sort of a layout for how to approach this:

Find the user_id of the role or user from dba_users
Query the sys.sysauth$ table for this user_id. This will return all of the system privileges associated with the role or user. The system privileges will be a cryptic number (privilege#), which is translated in sys.system_privilege_map.
The above query will also yield the roles that the role or user has. These will be the privilege#'s that are positive numbers that are equal to the user# in sys.user$ where type = 0.
You'll have to recurse the last two steps for each of the 'subroles' until you have an extracted list of all the system privileges of the original role and all the subroles, as well as a list of all the subroles.

For the next step, let's just say you put the original user_id and all the 'subrole' id's in a temp table called RolesTable.

5) Then you need to find the other privileges to other objects. (Select obj#, privilege# from sys.objauth# where grantee# in (RolesTable, 1)).

Explicitly include 1 in this where clause because it designates an object with public permissions.

I *THINK* that that will give you all the obj#'s and the associated privilege#'s that the original role has permissions to. You can, of course, use WHERE conditions to trim your list.

Oh, by the way, I never found an analogy for the sys.system_privilege_map for non-system privileges. I leave that step for someone else to post. I found the specific translation for the privileges I cared about by looking in the source for the views. If someone has a concise list, PLEASE mail it to me!

Thanks a lot, and I am very ready to have the above torn apart and be shown a better mechanism. I only ask that you be kind and e-mail it to me as well as post it, because I don't check the newsgroup often enough sometimes.

Ben Harmon
ben_at_dascom.com.nospam
**Please remove .nospam suffix if you want to mail me**

In article <33D050A0.7662_at_comp.polyu.edu.hk>, Stephen <c6635500_at_comp.polyu.edu.hk> wrote:
>Hello all,
>
> How to know the contents of the role in Oracle? i.e. when I type
>"select * from dba_role_privs", I only know that the granted_role. I want
>to know what the role means, does anyone tells me how to do it?
>
> Also, what is the difference between Oracle 7 and Oracle 8? Where
>can I get the information about Oracle 8?
>
>Thanks,
>Stephen

Ben Harmon
ben_at_dascom.com.nospam
**Please remove .nospam suffix if you want to mail me**

Received on Fri Jul 18 1997 - 00:00:00 CDT