Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question
    
What have you tried? –  a_horse_with_no_name Mar 12 '13 at 12:24
add comment

2 Answers 2

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 'PHIL@ 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' ;
share|improve this answer
    
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. –  Leigh Riffel Mar 13 '12 at 15:17
    
Opps, need some CONNECT BY .. PRIOR for #6 –  Phil Mar 13 '12 at 15:54
    
Does your answer for #5 include tables that user can select through a role they have been assigned? –  dgf Mar 15 '12 at 10:06
add comment

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.

share|improve this answer
    
This doesn't seem to answer the question: how can a DBA find out what a specific arbitrary user can access? –  Jon of All Trades Jun 11 at 16:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.