Viewing Privileges Granted on Objects

You can view information about privileges, grantors, grantees, and objects by querying these system tables:

An asterisk (*) appended to a privilege indicates that the user can grant the privilege to other users.

You can also view the effective privileges on a specified database object by using the GET_PRIVILEGES_DESCRIPTION metafunction.

Viewing Explicitly Granted Privileges

To view explicitly granted privileges on objects, query the GRANTS table.

The following query returns the explicit privileges for the schema, myschema.

=> SELECT grantee, privileges_description FROM grants WHERE object_name='myschema';
 grantee | privileges_description
---------+------------------------
 Bob     | USAGE, CREATE
 Alice   | CREATE
 (2 rows)

Viewing Inherited Privileges

To view which tables and views inherit privileges from which schemas, query the INHERITING_OBJECTS table.

The following query returns the tables and views that inherit their privileges from their parent schema, customers.

=> SELECT * FROM inheriting_objects WHERE object_schema='customers';
     object_id     |     schema_id     | object_schema |  object_name  | object_type
-------------------+-------------------+---------------+---------------+-------------
 45035996273980908 | 45035996273980902 | customers     | cust_info     | table
 45035996273980984 | 45035996273980902 | customers     | shipping_info | table
 45035996273980980 | 45035996273980902 | customers     | cust_set      | view
 (3 rows)

To view the specific privileges inherited by tables and views and information on their associated grant statements, query the INHERITED_PRIVILEGES table.

The following query returns the privileges that the tables and views inherit from their parent schema, customers.

=> SELECT object_schema,object_name,object_type,privileges_description,principal,grantor FROM inherited_privileges WHERE object_schema='customers';
 object_schema |  object_name  | object_type |                          privileges_description                           | principal | grantor
---------------+---------------+-------------+---------------------------------------------------------------------------+-----------+---------
 customers     | cust_info     | Table       | INSERT*, SELECT*, UPDATE*, DELETE*, ALTER*, REFERENCES*, DROP*, TRUNCATE* | dbadmin   | dbadmin
 customers     | shipping_info | Table       | INSERT*, SELECT*, UPDATE*, DELETE*, ALTER*, REFERENCES*, DROP*, TRUNCATE* | dbadmin   | dbadmin
 customers     | cust_set      | View        | SELECT*, ALTER*, DROP*                                                    | dbadmin   | dbadmin
 customers     | cust_info     | Table       | SELECT                                                                    | Val       | dbadmin
 customers     | shipping_info | Table       | SELECT                                                                    | Val       | dbadmin
 customers     | cust_set      | View        | SELECT                                                                    | Val       | dbadmin
 customers     | cust_info     | Table       | INSERT                                                                    | Pooja     | dbadmin
 customers     | shipping_info | Table       | INSERT                                                                    | Pooja     | dbadmin
 (8 rows)

Viewing Effective Privileges on an Object

To view the current user's effective privileges on a specified database object, user the GET_PRIVILEGES_DESCRIPTION metafunction.

In the following example, user Glenn has set the REPORTER role and wants to check his effective privileges on schema s1 and table s1.articles.

  • Table s1.articles inherits privileges from its schema (s1).
  • The REPORTER role has the following privileges: 
    • SELECT on schema s1
    • INSERT WITH GRANT OPTION on table s1.articles
  • User Glenn has the following privileges:
    • UPDATE and USAGE on schema s1.
    • DELETE on table s1.articles.

GET_PRIVILEGES_DESCRIPTION returns the following effective privileges for Glenn on schema s1:

=> SELECT GET_PRIVILEGES_DESCRIPTION('schema', 's1');
   GET_PRIVILEGES_DESCRIPTION
--------------------------------
 SELECT, UPDATE, USAGE
(1 row)

GET_PRIVILEGES_DESCRIPTION returns the following effective privileges for Glenn on table s1.articles:

=> SELECT GET_PRIVILEGES_DESCRIPTION('table', 's1.articles');
   GET_PRIVILEGES_DESCRIPTION
--------------------------------
 INSERT*, SELECT, UPDATE, DELETE
(1 row)

See Also