Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Table privileges -- DBA_TAB_PRIVS

Posted on 2008-10-15
5
Medium Priority
?
3,092 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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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ā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

876 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