Does someone have a query that shows the relationships between roles, users and tables? I'd like to be able to run a query and see if user zzzz has SELECT authority against table yyyy; or something similiar to this.
rmantyla 270005PEBG4 Posts
Re: Table Authorities....Query?2012-09-19T10:45:09ZThis is the accepted answer. This is the accepted answer.Hi Tom,
The PRIV column of SYS_RELAUTH table stores information about granted privileges of a user or a role.
Its value is the sum of granted privileges: select = 1, insert = 2, delete = 4, update = 8 and references = 16.
So all privileges is 31 (1 + 2 + 4 + 8 + 16).
This query can be used to see all granted privileges on tables joining information stored in system tables
SYS_USERS, SYS_TABLES and SYS_RELAUTH:
select name as user_name, table_name, table_schema, table_catalog, sys_relauth.priv
from sys_tables, sys_users, sys_relauth
where sys_users.id = sys_relauth.ur_id and sys_relauth.rel_id = sys_tables.id;