Note
This view was introduced in OceanBase Database V4.2.4.
Purpose
The information_schema.role_column_grants view displays the column privileges of activated roles granted to the user in the current session, and the column privileges of other roles granted to these roles.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| GRANTOR | null | NO | The username of the account that grants privileges to the role. The value of this column is always NULL in OceanBase Database. |
| GRANTOR_HOST | null | NO | The host name of the account that grants privileges to the role. The value of this column is always NULL in OceanBase Database. |
| GRANTEE | varchar(32) | NO | The username of the account corresponding to the role. |
| GRANTEE_HOST | varchar(255) | NO | The host name of the account corresponding to the role. |
| TABLE_CATALOG | varchar(3) | NO | The name of the catalog on which the role is granted privileges. The value of this column is always def. |
| TABLE_SCHEMA | varchar(64) | NO | The name of the database containing the table on which the role is granted privileges. |
| TABLE_NAME | varchar(64) | NO | The name of the table on which the role is granted privileges. |
| COLUMN_NAME | varchar(64) | NO | The name of the column in the table on which the role is granted privileges. |
| PRIVILEGE_TYPE | varchar(31) | NO | The privileges granted. The value can be any privileges on a column of a specified table. All privileges on one column are displayed in each row. |
| IS_GRANTABLE | varchar(3) | NO | Indicates whether the privileges of the role can be granted 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 column-level privileges to the role.
obclient [test]> GRANT INSERT (c1, c2), SELECT (c1) ON test.tbl1 TO employee;Assign the role to the
user1user.obclient [test]> GRANT employee TO user1;Connect to the database as the
user1user.Activate the
employeerole in the current session.obclient [test]> SET ROLE employee;Query the column 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_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