Note
This view is available starting with V4.3.3 for V4.3x versions and V4.2.4 for V4.2.x versions.
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
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| GRANTOR | varchar(97) | YES | The username part of the account that granted the privilege to the role. |
| GRANTOR_HOST | varchar(256) | YES | The host part of the account that granted the privilege to the role. |
| GRANTEE | varchar(32) | NO | The username part of the account corresponding to the role. |
| GRANTEE_HOST | varchar(255) | NO | The host part of the account corresponding to the role. |
| TABLE_CATALOG | varchar(3) | NO | The name of the catalog to which the role has been granted privileges. This value is always def. |
| TABLE_SCHEMA | varchar(64) | NO | The name of the database to which the role has been granted privileges. |
| TABLE_NAME | varchar(64) | NO | The name of the table to which the role has been granted privileges. |
| PRIVILEGE_TYPE | varchar(90) | NO | The privilege granted. This value can be any privilege granted at the table level. |
| IS_GRANTABLE | varchar(3) | NO | Indicates whether the role can grant privileges to other users. |
Sample query
Create a role and a table.
obclient [test]> CREATE ROLE employee;obclient [test]> CREATE TABLE tbl1(c1 int, c2 int, c3 int);Grant table-level privileges to the role.
obclient [test]> GRANT DROP,UPDATE,SELECT ON test.tbl1 TO employee WITH GRANT OPTION;Grant the role to a user.
obclient [test]> GRANT employee TO user1;Connect to the database as user
user1.Activate the
employeerole in the current session.obclient [test]> SET ROLE employee;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