All Tables Required to Find Privs for a User 2

[tekdudedude]

Hello,

Are there any views other than these to display what privileges a user has?

DBA_AQ_AGENT_PRIVS             VIEW
DBA_COL_PRIVS                  VIEW
DBA_PRIV_AUDIT_OPTS            VIEW
DBA_REPGROUP_PRIVILEGES        VIEW
DBA_ROLE_PRIVS                 VIEW
DBA_RSRC_CONSUMER_GROUP_PRIVS  VIEW
DBA_RSRC_MANAGER_SYSTEM_PRIVS  VIEW
DBA_SYS_PRIVS                  VIEW
DBA_TAB_PRIVS                  VIEW

Thanks,

TD

 

[SantaMufasa]

TD,

Here is a query that gives a comprehensive list of "any views...to display what privileges a user has":

select object_name
from dba_objects
where object_name like '%PRIV%'
  and owner in ('SYS','SYSTEM','PUBLIC')
  and object_type = 'SYNONYM';

OBJECT_NAME
------------------------------
ALL_COL_PRIVS
ALL_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD
ALL_REPGROUP_PRIVILEGES
ALL_TAB_PRIVS
ALL_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD
COLUMN_PRIVILEGES
DBA_AQ_AGENT_PRIVS
DBA_COL_PRIVS
DBA_PRIV_AUDIT_OPTS
DBA_REPGROUP_PRIVILEGES
DBA_ROLE_PRIVS
DBA_RSRC_CONSUMER_GROUP_PRIVS
DBA_RSRC_MANAGER_SYSTEM_PRIVS
DBA_SYS_PRIVS
DBA_TAB_PRIVS
DBMS_RESOURCE_MANAGER_PRIVS
GV$ENABLEDPRIVS
ORA_PRIVILEGE_LIST
QUEUE_PRIVILEGES
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
SESSION_PRIVS
SYSTEM_PRIVILEGE_MAP
TABLE_PRIVILEGES
TABLE_PRIVILEGE_MAP
USER_AQ_AGENT_PRIVS
USER_COL_PRIVS
USER_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
USER_REPGROUP_PRIVILEGES
USER_ROLE_PRIVS
USER_RSRC_CONSUMER_GROUP_PRIVS
USER_RSRC_MANAGER_SYSTEM_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD
V$ENABLEDPRIVS

41 rows selected.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[tekdudedude]

Santa,

Wow - very nice.

Thanks,

TD

 

[Thargy]

Santa,

I just used the above script "in anger" to solve a privileges and permissions problem. It was much appreciated.

Regards

T

Grinding away at things Oracular

 

[SantaMufasa]

Tharg,

At this point, I'm interested in knowing more about the "in anger" part...What's up?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Hi Dave, the phrase "in anger" is often used to imply "in earnest" it does not mean that the person was irritated
e.g. I have practiced setting up a database but have never had to create one "in anger"

HTH
Jim

 

[Thargy]

Santa,

I had an error reported which I thought was due to a privileges issue, and I wanted to programatically check every single privilege granted to a role, versus those expected.

I grant the role select, insert, update and delete on every table in the source schema. This role is then awarded to another user. This other user appeared unable to "see" one of the source tables, and none of the views.

I attempted to difference the two possible sets of privileges by generating a query to return the table names and privileges by querying user_tables and then using the MINUS operator to only show those privileges which had not been granted, even though I thought they had been.

I used your list of %PRIV% synonyms to guide me along the way - hence my thanks. It turned out to be caused by a disused user which still had the above mentioned role granted to it. Once I dropped the user, the privileges immediately worked as expected.

Nonetheless, I was surprised to see that granting the same role to two users could cause such an effect, strange don't you think? I can't see any good reason why 100 users can's all be granted the same role...

Problem solved, but I'm a bit puzzled.

Regards

T

Grinding away at things Oracular