Roles definition

[nnani]

Hello all,

How can we see the role definition.
What all privileges are granted to a role?

The roles system table does not show any of this data.

[JimKnicely]

Hi,

You can find the privileges for a role in the V_CATALOG.GRANTS table.

Example:

Code: Select all

dbadmin=> CREATE TABLE test (c1 INT);
CREATE TABLE

dbadmin=> CREATE ROLE test_role;
CREATE ROLE

dbadmin=> GRANT USAGE ON SCHEMA schema1 TO test_role;;
GRANT PRIVILEGE

dbadmin=> GRANT SELECT ON test TO test_role;
GRANT PRIVILEGE

dbadmin=> SELECT grantor, privileges_description, object_name, object_type, grantee FROM v_catalog.grants WHERE grantee = 'test_role';
 grantor | privileges_description | object_name | object_type |  grantee  
---------+------------------------+-------------+-------------+-----------
 dbadmin | USAGE                  | schema1    | SCHEMA      | test_role
 dbadmin | SELECT                 | test        | TABLE       | test_role
(2 rows)

[nnani]

That worked like a charm

Thanks Jim