Re: find out on which tables a user has read/write access

Oh I think this is being a tad incredibly picky. I obviously know roles can be granted roles which can be granted roles which then fit nicely in a set of those nice wooden Russian carved dolls that stack up inside each other.

I didn't realize that a sufficient answer to help the asker and also stifle criticism meant writing the whole set of SQL or programs or views for them.

First, anyone who creates nested roles deliberately of their own volition ought to be questioned as to whether they were descendants of those who created the original Gordian Knot. That is a security mess and a bad practice in defeating the granularity of roles defined for specific applications, say, for example. To nest privileges by granting ROLES to ROLES is something for which I cannot offhand think of a single legitimate reason. It would be mindbending to manage, especially with password protected roles and non-default roles.

Please explain what advantages there are in nested role grants beyond Byzantine-level complexity?

I have an open mind about this, I am just clueless as to why one would ever want to do this.

Thanks.

Plus as an end note, the whatever SESSION_x views only show things currently in effect, not all the things the user could do if all roles were activated, unless I am greatly mistaken.

"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message news:3c86a92b.4033423_at_news.freeler.nl...
> DBA_TAB_PRIVS will not tell you directly the privileges you have. You
> can have privileges that have been granted to a role that has been
> granted to a role that has been granted to a role ... that has been
> granted to a role that has been granted to you.
>
> To rephrase the question: is there a generic query (that will respond
> reasonably quick) and that gives you the table privileges granted to
> you, either directly or through a role or a chain of roles.
>
> Oracle itself seems to be able to do this with the system privileges
> with the view SESSION_PRIVS, and the roles granted to you by the view
> SESSION_ROLES. Maybe the text of these views will give you insight in
> how you should go about achieving your goal.
>
> Jaap.
>
> On Wed, 06 Mar 2002 19:59:21 GMT, "RSH" <RSH_Oracle_at_worldnet.att.net>
> wrote:
>
> >DBA_TAB_PRIVS will tell you all table privileges, for users and roles, at
an
> >object grant level.
> >
> >Roles look just like usernames and are treated as such with respect to
this
> >view.
> >
> >You must also examine DBA_ROLE_PRIVS for system privileges that may have
> >been granted, see who's got what roles granted and how they were granted,
> >that table's name escapes me, and finally look for directly granted
system
> >privileges in DBA_SYS_PRIVS, or DBA_USER_PRIVS, or along those lines . I
> >believe that's it, you can check the admin manual and get the correct
table
> >(view, I should say) names for this out of there. I can remember how the
> >stuff is stored, but I am dreadful for remembering the system view names.
> >
> >I am doing fairly well with all my documentation 2,000 miles away.
> >
> >RSH.
> >
> >
> >"Steffen Ramlow" <s.ramlow_at_gmx.de> wrote in message
> >news:a65qpk$bvso9$1_at_ID-54600.news.dfncis.de...
> >> how can i figure out on which tables of a certain schema a user is
allow
> >to
> >> select and (update or insert or delete) rows?
> >>
> >> if the right is directly granted everything is fine - i can use
> >> user_tab_privs, but what about roles and owner and DBA...
> >>
> >> TIA
> >>
> >>
> >
> >
>
Received on Thu Mar 07 2002 - 03:05:01 CST