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

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 Wed Mar 06 2002 - 17:52:29 CST