Solved

Table privileges -- DBA_TAB_PRIVS

Posted on 2008-10-15
5
2,717 Views
Last Modified: 2009-07-29
What privileges does DBA_TAB_PRIVS contain? I read that it can be used to find all grants to a particular table but I don't find one table that I created.
Does it only list explicitly granted privileges? How can I find whether a particular user has a certain privilege on a table?
0
Comment
Question by:jhshukla
  • 3
5 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 22723152
it holds other privileges too

grants to users or to roles on all objects (not just tables, despite the name) are in that view.

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 22723171
for more information, here is the documentation on that view


http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4151.htm#i1627646
0
 
LVL 47

Expert Comment

by:schwertner
ID: 22728957
You will begin so:

SQL> descr dba_tab_privs
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -----------------
 GRANTEE                                                                  NOT NULL VARCHAR2(30)
 OWNER                                                                    NOT NULL VARCHAR2(30)
 TABLE_NAME                                                               NOT NULL VARCHAR2(30)
 GRANTOR                                                                  NOT NULL VARCHAR2(30)
 PRIVILEGE                                                                NOT NULL VARCHAR2(40)
 GRANTABLE                                                                         VARCHAR2(3)
 HIERARCHY                                                                         VARCHAR2(3)



If you want to see all grants to user SCOTT:

SELECT privilege
FROM   dba_tab_privs
WHERE  grantor   <> 'SCOTT'
   AND   grantee     = 'SCOTT';

All
SELECT privilege
FROM   dba_tab_privs ;


 
0
 
LVL 9

Author Comment

by:jhshukla
ID: 22736883
Thank you both for the responses. but it still does not answer my question.
from the link: "describes the object grants for which the current user is the object owner, grantor, or grantee."

Does this mean that the table contains only explicit grants? Does it not include privileges obtained via some rule? e.g. by default creator/owner gets all privs on that object so those are not listed; privileges gained by "grant select on any table" (plz excuse syntax errors) are not listed; etc.

What I want to see is not "grants" but "privileges."
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 22737509
yes,  dba_tab_privs only shows explict grants to users and to roles.

"implicit" grants such as your example of an owner's privileges aren't visible anywhere,  they just "are".
inherited grants are not listed either, you would have to derive those yourself.  For instance, select on some table granted to a role, where that role is then granted to a user,  the user's grant would not be shown.


system privileges like "create table" or "select any table"  are found in dba_sys_privs and like the object grants above, it lists explicit grants and not inherited ones.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now