Solved

Table privileges -- DBA_TAB_PRIVS

Posted on 2008-10-15
5
2,959 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 74

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 74

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 48

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 74

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

627 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