Overview
The role_column_grants view displays the column privileges of activated roles granted to the user in the current session, as well as the column privileges of other roles granted to these roles.
Columns
Field |
Type |
Nullable |
Description |
|---|---|---|---|
| GRANTOR | null | NO | The username part of the account authorized with this role. In OceanBase AI Database, this value is always NULL. |
| GRANTOR_HOST | null | NO | The host part of the account granted this role. In OceanBase AI Database, this value is always NULL. |
| GRANTEE | varchar(32) | NO | The username part of the account corresponding to this role. |
| GRANTEE_HOST | varchar(255) | NO | The hostname part of the account corresponding to this role. |
| TABLE_CATALOG | varchar(3) | NO | The name of the directory authorized to this role. This value is always 'def'. |
| TABLE_SCHEMA | varchar(64) | NO | The name of the database to which the table authorized for this role belongs. |
| TABLE_NAME | varchar(64) | NO | The name of the table to which this role has been granted access. |
| COLUMN_NAME | varchar(64) | NO | Column names of the tables authorized to this role. |
| PRIVILEGE_TYPE | varchar(31) | NO | The granted privilege. This value can be any privilege granted at the column level of a specified table. Each row lists all privileges for a particular column. |
| IS_GRANTABLE | varchar(3) | NO | Whether the privileges of this 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 on the column to the role.
obclient [test]> GRANT INSERT (c1, c2), SELECT (c1) ON test.tbl1 TO employee;Grant the role to the 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;View the column privileges of activated roles granted 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