Note
This view is available starting with V4.0.0.
Purpose
This view displays the database privileges of users in each tenant.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The tenant ID. |
| USER_ID | bigint(20) | NO | The user ID. |
| USERNAME | varchar(128) | NO | The username. |
| DATABASE_NAME | varchar(128) | NO | The database name. |
| GMT_CREATE | timestamp(6) | NO | The creation time. |
| GMT_MODIFIED | timestamp(6) | NO | The last modification time. |
| PRIV_ALTER | varchar(3) | NO | Indicates whether the user has the privilege to modify databases or tables. |
| PRIV_CREATE | varchar(3) | NO | Indicates whether the user has the privilege to create databases or tables. |
| PRIV_DELETE | varchar(3) | NO | Indicates whether the user has the privilege to delete records in databases or tables. |
| PRIV_DROP | varchar(3) | NO | Indicates whether the user has the privilege to drop databases or tables. |
| PRIV_GRANT_OPTION | varchar(3) | NO | Indicates whether the user has the privilege to grant privileges. |
| PRIV_INSERT | varchar(3) | NO | Indicates whether the user has the privilege to insert records. |
| PRIV_UPDATE | varchar(3) | NO | Indicates whether the user has the privilege to update records. |
| PRIV_SELECT | varchar(3) | NO | Indicates whether the user has the privilege to query records. |
| PRIV_INDEX | varchar(3) | NO | Indicates whether the user has the privilege to create and drop indexes. |
| PRIV_CREATE_VIEW | varchar(3) | NO | Indicates whether the user has the privilege to create views. |
| PRIV_SHOW_VIEW | varchar(3) | NO | Indicates whether the user has the privilege to query views. |
| PRIV_EXECUTE | varchar(3) | NO | Indicates whether the user has the privilege to execute procedures and functions. |
| PRIV_ALTER_ROUTINE | varchar(3) | NO | Indicates whether the user has the privilege to modify or drop procedures and functions. |
| PRIV_CREATE_ROUTINE | varchar(3) | NO | Indicates whether the user has the privilege to create procedures and functions. |
| PRIV_REFERENCES | varchar(3) | NO | Indicates whether the user has the privilege to create foreign keys. |
| PRIV_TRIGGER | varchar(3) | NO | Indicates whether the user has the privilege to activate triggers. |
| PRIV_LOCK_TABLE | varchar(3) | NO | Indicates whether the user has the privilege to lock tables. |
| PRIV_EVENT | varchar(3) | NO | Indicates whether the user has the privilege to create and manage events. |
Sample query
Query the database privileges of users in the tenant with ID 1002.
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_DATABASE_PRIVILEGE WHERE TENANT_ID=1002;
The query result is as follows:
+-----------+---------+----------+--------------------+----------------------------+----------------------------+------------+-------------+-------------+-----------+-------------------+-------------+-------------+-------------+------------+------------------+----------------+--------------+--------------------+---------------------+-----------------+--------------+-----------------+------------+
| TENANT_ID | USER_ID | USERNAME | DATABASE_NAME | GMT_CREATE | GMT_MODIFIED | PRIV_ALTER | PRIV_CREATE | PRIV_DELETE | PRIV_DROP | PRIV_GRANT_OPTION | PRIV_INSERT | PRIV_UPDATE | PRIV_SELECT | PRIV_INDEX | PRIV_CREATE_VIEW | PRIV_SHOW_VIEW | PRIV_EXECUTE | PRIV_ALTER_ROUTINE | PRIV_CREATE_ROUTINE | PRIV_REFERENCES | PRIV_TRIGGER | PRIV_LOCK_TABLE | PRIV_EVENT |
+-----------+---------+----------+--------------------+----------------------------+----------------------------+------------+-------------+-------------+-----------+-------------------+-------------+-------------+-------------+------------+------------------+----------------+--------------+--------------------+---------------------+-----------------+--------------+-----------------+------------+
| 1002 | 200001 | root | information_schema | 2025-02-05 17:45:58.925531 | 2025-02-05 17:45:58.925531 | YES | YES | YES | YES | NO | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES |
| 1002 | 200001 | root | mysql | 2025-02-05 17:45:58.925531 | 2025-02-05 17:45:58.925531 | YES | YES | YES | YES | NO | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES |
| 1002 | 200001 | root | oceanbase | 2025-02-05 17:45:58.922326 | 2025-02-05 17:45:58.922326 | YES | YES | YES | YES | NO | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES |
| 1002 | 200001 | root | test | 2025-02-05 17:45:58.926588 | 2025-02-05 17:45:58.926588 | YES | YES | YES | YES | NO | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES |
| 1002 | 200001 | root | __public | 2025-02-05 17:45:58.924476 | 2025-02-05 17:45:58.924476 | YES | YES | YES | YES | NO | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES |
| 1002 | 200001 | root | __recyclebin | 2025-02-05 17:45:58.924476 | 2025-02-05 17:45:58.924476 | YES | YES | YES | YES | NO | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES |
| 1002 | 500009 | test2 | infotest | 2025-02-20 15:12:19.975322 | 2025-02-20 15:12:19.975322 | NO | NO | NO | NO | NO | NO | NO | YES | NO | NO | NO | NO | NO | NO | NO | NO | NO | NO |
+-----------+---------+----------+--------------------+----------------------------+----------------------------+------------+-------------+-------------+-----------+-------------------+-------------+-------------+-------------+------------+------------------+----------------+--------------+--------------------+---------------------+-----------------+--------------+-----------------+------------+
7 rows in set