List Users and Roles of a table

[pat718]

I have been trying to figure out how to do this for a few days. I thought this would be simple but I keep getting an error. I am trying to list all of the roles AND users that have access to a table in Oracle 11g. I am new to this but I tried this:

SELECT * FROM all_users, dba_role_privs
WHERE table = 'data_mart'

What am I doing wrong? Thanks for your help.

 

[Beilstwh]

select username,granted_role
from all_users,dba_role_privs
where grantee = username


Bill
Lead Application Developer
New York State, USA

 

[pat718]

Hi Bill,

Thanks for your help. But I need to see the users and roles that have access to my data_mart table. Thanks.

 

[Beilstwh]

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 = 'DATA_MART'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'DATA_MART';


Bill
Lead Application Developer
New York State, USA

 

[pat718]

Thanks again Bill. Now I am only getting column names with no rows of data. The column names I am getting are GRANTEE, GRANT TYPE, ROLE, TABLE_NAME. I only need to see all the users and roles for the data_mart table. Thanks.

 

[Beilstwh]

Those are the roles and users with direct grants on the DATA_MART table only.

Bill
Lead Application Developer
New York State, USA

 

[pat718]

This is not working. I only get the column names with no rows of data. Just the column names when I run this query:

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 = 'DATA_MART'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'DATA_MART';

 

[Beilstwh]

is the name of the table "data_mart" or "DATA_MART"? In oracle you should never make objects with lower or mixed case. replace DATA_MART with the exact case of the table.

Bill
Lead Application Developer
New York State, USA

 

[carp]

Try
SELECT p.grantee
FROM dba_tab_privs p
WHERE p.table_name = 'DATA_MART';

This should give you all users and roles with any kind of privilege on the table. It won't tell you which is a user and which is a role, nor will it give you the privilege. But it should give you the list that you have asked for.

Also, unless you used double quotes around the name of the table in your CREATE statement, the table name will be stored in upper case in the data dictionary. So regardless of whether your statement said 'CREATE TABLE data_mart...', 'CREATE TABLE Data_Mart...', or 'CREATE TABLE DATA_MART...', your query should be looking for 'DATA_MART'.

 

[carp]

Just remembered - you will also want to look at dba_col_privs (for users/roles with specific column privileges) as well as dba_sys_privs for any users/roles with *ALL* privs.

 

[carp]

Disregard last post. I think the view name is actually dba_tab_col_privs and you should check dba_sys_privs for *ANY* privs (e.g., SELECT ANY TABLE). Sorry for the confusion.