information_schema.role_column_grants

Purpose

The role_column_grants view displays the column privileges granted to activated roles for the current user and other roles granted to the activated 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 column-level privileges to the role.

   obclient [test]> GRANT INSERT (c1, c2), SELECT (c1) ON test.tbl1 TO employee;
   

3. Grant the role to a 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. View the column privileges granted to activated roles for the current user.

   obclient [test]> USE information_schema;
   

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

   The query result is as follows:

   +---------+--------------+----------+--------------+---------------+--------------+------------+-------------+----------------+--------------+
   | GRANTOR | GRANTOR_HOST | GRANTEE  | GRANTEE_HOST | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
   +---------+--------------+----------+--------------+---------------+--------------+------------+-------------+----------------+--------------+
   |    NULL |         NULL | employee | %            | def           | test          | tbl1      | c1          | Select,Insert  | NO           |
   |    NULL |         NULL | employee | %            | def           | test          | tbl1      | c2          | Insert         | NO           |
   +---------+--------------+----------+--------------+---------------+--------------+------------+-------------+----------------+--------------+
   2 rows in set
   

References

View roles