INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

List Users and Roles of a table

List Users and Roles of a table

(OP)
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.

RE: List Users and Roles of a table

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

Bill
Lead Application Developer
New York State, USA

RE: List Users and Roles of a table

(OP)
Hi Bill,

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

RE: List Users and Roles of a table

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

RE: List Users and Roles of a table

(OP)
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.

RE: List Users and Roles of a table

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

Bill
Lead Application Developer
New York State, USA

RE: List Users and Roles of a table

(OP)
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';

RE: List Users and Roles of a table

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

RE: List Users and Roles of a table

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'.

RE: List Users and Roles of a table

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.

RE: List Users and Roles of a table

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close