Topic
  • 1 reply
  • Latest Post - ‏2012-09-19T10:45:09Z by rmantyla
Tom_Glaser
Tom_Glaser
27 Posts

Pinned topic Table Authorities....Query?

‏2012-08-09T16:02:24Z |
Hi,

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.

Thanks,

Tom Glaser
Updated on 2012-09-19T10:45:09Z at 2012-09-19T10:45:09Z by rmantyla
  • rmantyla
    rmantyla
    4 Posts

    Re: Table Authorities....Query?

    ‏2012-09-19T10:45:09Z  
    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;

    Regards,
    Risto