Posted on 2011-10-10
Last Modified: 2012-08-13
Hi guys,
How can find all users in a table with select any table priv
Question by:nirvairghuman
    LVL 34

    Accepted Solution

    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.
    LVL 73

    Assisted Solution

    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
    SELECT grantee
      FROM dba_sys_privs
     WHERE privilege = 'SELECT ANY TABLE' AND grantee IN (SELECT username FROM dba_users)

    Author Comment


    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.
    LVL 73

    Assisted Solution

    doesn't matter,  neither is dependent on or influenced by the other

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    trigger 5 39
    Oracle to_char 21 54
    Oracle Finace 3 17
    adding % symbol to a percentage - oracle query 16 25
    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    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.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    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.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now