Solved

Table privileges -- DBA_TAB_PRIVS

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MULTIPLE DATE QUERY 15 91
exp/imp 25 87
Oracle Insert not working 10 32
how to find out the count of records based on the subfolders paths 11 32
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…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

828 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