How to check privileges of users/roles on sys tables or views?

user_13117733 0
Viewed 60

I want to know, who and what are the roles/users having "SELECT" access on 'dba_role_privs' object. I am really confused as Some users can do "select on it" and some user not be able to query on this.

I have tried this below query after referring the data dictionary (select * from dict;). It says whoever having 'SELECT_CATALOG_ROLE' can able to do "SELECT" on this 'DBA_ROLE_PRIVS'.

select * from DBA_TAB_PRIVS where table_name='DBA_ROLE_PRIVS';  --SELECT_CATALOG_ROLE

However, after granting the privilege also, still I am not able to query on this DBA_ROLE_PRIVS.

But for some user, I can able to query on this DBA_ROLE_PRIVS even though this 'SELECT_CATALOG_ROLE' is not provided.

Could you please provide me a solution on this,

1.How to check who are the users and roles have privilege to access this 'DBA_TAB_PRIVS '?

2.How to grant privilege to this 'DBA_TAB_PRIVS ' object ?

And also provide some tips and tricks to solve and how to approach, where and all to check if problem occurs .

Thanks

roles privileges oracle sql
1 Answers

Any user or role with the SELECT ANY DICTIONARY system privilege, or any user or role with a direct SELECT grant on the view will be able to select from it.

Use the DBA_TAB_PRIVS view to see who has direct SELECT privileges, and use DBA_SYS_PRIVS to see which users or roles have the SELECT ANY DICTIONARY privilege.

select * from dba_tab_privs where table_name='DBA_TAB_PRIVS';
select * from dba_sys_privs where privilege='SELECT ANY DICTIONARY';

To grant access to a view, login as a the owner (SYS, in this case), a user with SYSDBA privileges, or a user with the GRANT ANY PRIVILEGE or GRANT ANY OBJECT PRIVILEGE privileges, and use the GRANT command:

grant select on DBA_TAB_PRIVS to [username];

where [username] is the name of the user to which you want to grant the privilege.

user_129134910
Related