information_schema.role_table_grants

Purpose

The information_schema.role_table_grants view displays the table-level privileges of activated roles granted to the user in the current session, and the table-level privileges of other roles granted to 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. Assign the role to the user1 user.

   obclient [test]> GRANT employee TO user1;
   

4. Connect to the database as the user1 user.

5. Activate the employee role in the current session.

   obclient [test]> SET ROLE employee;
   

6. Query the table-level privileges of the activated role assigned to the user 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