?
Solved

ALL USER WITH SELECT ANY ATBLE

Posted on 2011-10-10
4
Medium Priority
?
245 Views
Last Modified: 2012-08-13
Hi guys,
How can find all users in a table with select any table priv
0
Comment
Question by:nirvairghuman
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 168 total points
ID: 36944219
This should give you the list:

select grantee
from dba_sys_privs
where privilege = 'SELECT ANY TABLE';

Keep in mind that could give you roles and then you would have to find the users that have that role.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 332 total points
ID: 36944272
this will find all users that have the privilege, whether granted directly,  or inherited through a role,
including roles granted through roles in a hierarchy.


SELECT grantee
  FROM (SELECT     grantee, granted_role, SYS_CONNECT_BY_PATH(grantee, '/'), CONNECT_BY_ISLEAF l
              FROM dba_role_privs
        START WITH granted_role IN (SELECT grantee
                                      FROM dba_sys_privs
                                     WHERE privilege = 'SELECT ANY TABLE')
        CONNECT BY granted_role = PRIOR grantee)
 WHERE l = 1
UNION
SELECT grantee
  FROM dba_sys_privs
 WHERE privilege = 'SELECT ANY TABLE' AND grantee IN (SELECT username FROM dba_users)
0
 

Author Comment

by:nirvairghuman
ID: 36944468
Guys,

1. I want to grant select any table to a role A and want to grant role A to some of the users.
2.  Want to revoke select any table from users.
means want to grant select any table through role

which step I should complete firstly 1 or 2.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 332 total points
ID: 36944491
doesn't matter,  neither is dependent on or influenced by the other
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

809 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