information_schema.role_table_grants

Purpose

The role_table_grants view displays table-level privileges granted to activated roles in the current session and table-level privileges granted to other roles by these roles.

Columns

Sample query

1. Create a role and a table.

   obclient [test]> CREATE ROLE employee;
   

   obclient [test]> CREATE TABLE tbl1(c1 int, c2 int, c3 int);
   

2. Grant table-level privileges to the role.

   obclient [test]> GRANT DROP,UPDATE,SELECT ON test.tbl1 TO employee WITH GRANT OPTION;
   

3. Grant the role to a user.

   obclient [test]> GRANT employee TO user1;
   

4. Connect to the database as user user1.

5. Activate the employee role in the current session.

   obclient [test]> SET ROLE employee;
   

6. Query the table-level privileges granted to activated roles in the current session.

   obclient [test]> USE information_schema;
   

   obclient [test]> SELECT * FROM information_schema.role_table_grants;
   

   The query result is as follows:

   +---------+--------------+-----------+--------------+---------------+--------------+------------+--------------------+--------------+
   | GRANTOR | GRANTOR_HOST | GRANTEE   | GRANTEE_HOST | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE     | IS_GRANTABLE |
   +---------+--------------+-----------+--------------+---------------+--------------+------------+--------------------+--------------+
   | root    | %            | employee  | %            | def           | test         | tbl1       | Drop,Update,Select | YES          |
   +---------+--------------+-----------+--------------+---------------+--------------+------------+--------------------+--------------+
   1 row in set
   

References

View roles