Note
This view is introduced since OceanBase Database V4.0.0.
Purpose
The oceanbase.DBA_OB_DATABASE_PRIVILEGE view displays the database privileges of the current user.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| USER_ID | bigint(20) | NO | The ID of the user. |
| USERNAME | varchar(128) | NO | The username. |
| DATABASE_NAME | varchar(128) | NO | The name of the database. |
| GMT_CREATE | timestamp(6) | NO | The time when the user was created. |
| GMT_MODIFIED | timestamp(6) | NO | The time when the user was modified. |
| PRIV_ALTER | varchar(3) | NO | Whether the privilege to modify databases or tables exists. |
| PRIV_CREATE | varchar(3) | NO | Whether the privilege to create databases or tables exists. |
| PRIV_DELETE | varchar(3) | NO | Whether the privilege to delete database or table records exists. |
| PRIV_DROP | varchar(3) | NO | Whether the privilege to drop databases or tables exists. |
| PRIV_GRANT_OPTION | varchar(3) | NO | Whether the privileges to grant to others exists. |
| PRIV_INSERT | varchar(3) | NO | Whether the privilege to insert records exists. |
| PRIV_UPDATE | varchar(3) | NO | Whether the privilege to update records exists. |
| PRIV_SELECT | varchar(3) | NO | Whether the privilege to query records exists. |
| PRIV_INDEX | varchar(3) | NO | Whether the privilege to set indexes exists. |
| PRIV_CREATE_VIEW | varchar(3) | NO | Whether the privilege to create views exists. |
| PRIV_SHOW_VIEW | varchar(3) | NO | Whether the privilege to query views exists. |
| PRIV_EXECUTE | varchar(3) | NO | Whether the privilege to execute procedures and functions exists. |
| PRIV_ALTER_ROUTINE | varchar(3) | NO | Whether the privilege to modify or drop procedures and functions exists. |
| PRIV_CREATE_ROUTINE | varchar(3) | NO | Whether the privilege to create procedures and functions exists. |
| PRIV_REFERENCES | varchar(3) | NO | Whether the privilege to create foreign keys exists. |
| PRIV_TRIGGER | varchar(3) | NO | Whether the privilege to activate triggers exists. |
Sample query
A user tenant queries the database privileges of users within the current tenant.
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_DATABASE_PRIVILEGE;
The query result is as follows:
+---------+----------+--------------------+----------------------------+----------------------------+------------+-------------+-------------+-----------+-------------------+-------------+-------------+-------------+------------+------------------+----------------+--------------+--------------------+---------------------+-----------------+--------------+
| 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 |
+---------+----------+--------------------+----------------------------+----------------------------+------------+-------------+-------------+-----------+-------------------+-------------+-------------+-------------+------------+------------------+----------------+--------------+--------------------+---------------------+-----------------+--------------+
| 200001 | root | information_schema | 2025-02-14 10:30:18.564339 | 2025-02-14 10:30:18.564339 | YES | YES | YES | YES | NO | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES |
| 200001 | root | mysql | 2025-02-14 10:30:18.563281 | 2025-02-14 10:30:18.563281 | YES | YES | YES | YES | NO | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES |
| 200001 | root | oceanbase | 2025-02-14 10:30:18.559445 | 2025-02-14 10:30:18.559445 | YES | YES | YES | YES | NO | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES |
| 200001 | root | test | 2025-02-14 10:30:18.564339 | 2025-02-14 10:30:18.564339 | YES | YES | YES | YES | NO | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES |
| 200001 | root | __public | 2025-02-14 10:30:18.562225 | 2025-02-14 10:30:18.562225 | YES | YES | YES | YES | NO | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES |
| 200001 | root | __recyclebin | 2025-02-14 10:30:18.561169 | 2025-02-14 10:30:18.561169 | YES | YES | YES | YES | NO | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES | YES |
| 513996 | test2 | db_test | 2025-02-21 11:02:48.969973 | 2025-02-21 11:02:48.969973 | NO | NO | NO | NO | NO | NO | NO | YES | NO | NO | NO | NO | NO | NO | NO | NO |
+---------+----------+--------------------+----------------------------+----------------------------+------------+-------------+-------------+-----------+-------------------+-------------+-------------+-------------+------------+------------------+----------------+--------------+--------------------+---------------------+-----------------+--------------+
7 rows in set