oracle - list users with access to certain tables

  • I'm sure this has been asked before but I can't seem to find the relevant details for the following.

    Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it is limited and does not meet all my needs), if not does anyone have some queries for answering the following?

    1. List all users who have been assigned a particular role?
    2. List all roles given to a user?
    3. List all privileges given to a user?
    4. List which tables a certain role gives SELECT access to?
    5. List all tables a user can SELECT from?
    6. List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.
  • List all users who have been assigned a particular role

    -- Change 'DBA' to the required role
    select * from dba_role_privs where granted_role = 'DBA'
    

    List all roles given to a user

    -- Change '[email protected] to the required user
    select * from dba_role_privs where grantee = 'PHIL';
    

    List all privileges given to a user

    select
      lpad(' ', 2*level) || granted_role "User, his roles and privileges"
    from
      (
      /* THE USERS */
        select 
          null     grantee, 
          username granted_role
        from 
          dba_users
        where
          username like upper('%&enter_username%')
      /* THE ROLES TO ROLES RELATIONS */ 
      union
        select 
          grantee,
          granted_role
        from
          dba_role_privs
      /* THE ROLES TO PRIVILEGE RELATIONS */ 
      union
        select
          grantee,
          privilege
        from
          dba_sys_privs
      )
    start with grantee is null
    connect by grantee = prior granted_role;
    

    Note: Taken from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html

    List which tables a certain role gives SELECT access to?

    -- Change 'DBA' to the required role.
    select * from role_tab_privs where role='DBA' and privilege = 'SELECT';
    

    List all tables a user can SELECT from?

    --Change 'PHIL' to the required user
    select * from dba_tab_privs where GRANTEE ='PHIL' and privilege = 'SELECT';
    

    List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.

    -- Change 'TABLENAME' below
    select Grantee,'Granted Through Role' as Grant_Type, role, table_name
    from role_tab_privs rtp, dba_role_privs drp
    where rtp.role = drp.granted_role
    and table_name = 'TABLENAME' 
    union
    select Grantee,'Direct Grant' as Grant_type, null as role, table_name
    from dba_tab_privs
    where table_name = 'TABLENAME' ;
    

    This is a good start, but #3 doesn't include object privileges, #5 doesn't include `SELECT` privileges available due to a role and #6 is missing.

    Opps, need some CONNECT BY .. PRIOR for #6

    Does your answer for #5 include tables that user can select through a role they have been assigned?

    Does this work if the user gets the privilege from a role that's been granted another role?

  • There are many ways to get the information you want using:

    data dictionary views

    present in oracle.

    You can just query the views and retrieve the details: For example:

    select * from DBA_COL_PRIVS;

    select * from ALL_COL_PRIVS;

    select * from USER_COL_PRIVS;

    This tells you:

    DBA view describes all column object grants in the database. ALL view describes all column object grants for which the current user or PUBLIC is the object owner, grantor, or grantee. USER view describes column object grants for which the current user is the object owner, grantor, or grantee.

    For more info,check this out

    Hope this helps.

    This doesn't seem to answer the question: how can a DBA find out what a specific arbitrary user can access?

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM

Tags used