Solved

Table privileges -- DBA_TAB_PRIVS

Posted on 2008-10-15
5
2,790 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Next Available Number 2 41
Converting a row into a column 2 51
database upgrade 8 72
dbms_crypto.decrypt   errors out 6 31
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

786 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