Note
This view is available starting with V4.0.0.
Purpose
This view displays information about users and roles in the current tenant.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| USER_NAME | varchar(128) | NO | Username or role name |
| HOST | varchar(128) | NO | Server name |
| PASSWD | varchar(128) | NO | User or role password |
| INFO | varchar(4096) | NO | User or role information |
| PRIV_ALTER | varchar(3) | NO | Whether the user or role has the privilege to modify databases or tables |
| PRIV_CREATE | varchar(3) | NO | Whether the user or role has the privilege to create databases or tables |
| PRIV_DELETE | varchar(3) | NO | Whether the user or role has the privilege to delete records in databases or tables |
| PRIV_DROP | varchar(3) | NO | Whether the user or role has the privilege to drop databases or tables |
| PRIV_GRANT_OPTION | varchar(3) | NO | Whether the user or role has the privilege to grant privileges |
| PRIV_INSERT | varchar(3) | NO | Whether the user or role has the privilege to insert records |
| PRIV_UPDATE | varchar(3) | NO | Whether the user or role has the privilege to update records |
| PRIV_SELECT | varchar(3) | NO | Whether the user or role has the privilege to query records |
| PRIV_INDEX | varchar(3) | NO | Whether the user or role has the privilege to set indexes |
| PRIV_CREATE_VIEW | varchar(3) | NO | Whether the user or role has the privilege to create views |
| PRIV_SHOW_VIEW | varchar(3) | NO | Whether the user or role has the privilege to view views |
| PRIV_SHOW_DB | varchar(3) | NO | Whether the user or role has the privilege to view all databases |
| PRIV_CREATE_USER | varchar(3) | NO | Whether the user or role has the privilege to create users |
| PRIV_SUPER | varchar(3) | NO | Whether the user or role has the privilege of a super user |
| IS_LOCKED | varchar(3) | NO | Whether the user or role is locked |
| PRIV_PROCESS | varchar(3) | NO | Whether the user or role has the privilege to view all threads |
| PRIV_CREATE_SYNONYM | varchar(3) | NO | Whether the user or role has the privilege to create synonyms |
| SSL_TYPE | bigint(20) | NO | The type of SSL standard encryption security |
| SSL_CIPHER | varchar(1024) | NO | The password of the SSL standard encryption security |
| X509_ISSUER | varchar(1024) | NO | The name of the X.509 issuer |
| X509_SUBJECT | varchar(1024) | NO | The name of the X.509 certificate subject |
| TYPE | varchar(4) | NO | The type. Valid values:
|
| PROFILE_ID | bigint(20) | NO | Profile ID |
| PASSWORD_LAST_CHANGED | timestamp(6) | YES | The time when the password was last changed |
| PRIV_FILE | varchar(3) | NO | Whether the user or role has the privilege to view files |
| PRIV_ALTER_TENANT | varchar(3) | NO | Whether the user or role has the privilege to modify tenant information |
| PRIV_ALTER_SYSTEM | varchar(3) | NO | Whether the user or role has the privilege to change server configuration parameters |
| PRIV_CREATE_RESOURCE_POOL | varchar(3) | NO | Whether the user or role has the privilege to create, modify, and delete resource pools |
| PRIV_CREATE_RESOURCE_UNIT | varchar(3) | NO | Whether the user or role has the privilege to create, modify, and delete resource units |
| MAX_CONNECTIONS | bigint(20) | NO | The maximum number of connections |
| MAX_USER_CONNECTIONS | bigint(20) | NO | The maximum number of tenant connections |
| PRIV_REPL_SLAVE | varchar(3) | NO | From server management. Tenants can determine the locations of replication slaves and masters. |
| PRIV_REPL_CLIENT | varchar(3) | NO | Master server management. Tenants can read binary log files used for maintaining the replication database environment. This user is located in the master system, facilitating communication between the host and client. |
| PRIV_DROP_DATABASE_LINK | varchar(3) | NO | Whether the user or role has the privilege to drop database links |
| PRIV_CREATE_DATABASE_LINK | varchar(3) | NO | Whether the user or role has the privilege to create database links |
| PRIV_EXECUTE | varchar(3) | NO | Whether the user or role has the privilege to execute procedures and functions |
| PRIV_ALTER_ROUTINE | varchar(3) | NO | Whether the user or role has the privilege to modify and delete procedures and functions |
| PRIV_CREATE_ROUTINE | varchar(3) | NO | Whether the user or role has the privilege to create procedures and functions |
| PRIV_CREATE_TABLESPACE | varchar(3) | NO | Whether the user or role has the privilege to create, modify, and delete tablespaces |
| PRIV_SHUTDOWN | varchar(3) | NO | Whether the user or role has the privilege to execute the mysqladmin shutdown command |
| PRIV_RELOAD | varchar(3) | NO | Whether the user or role has the privilege to execute flush operations |
| PRIV_REFERENCES | varchar(3) | NO | Whether the user has the permission to create foreign keys. |
| PRIV_CREATE_ROLE | varchar(3) | NO | Whether the user has the permission to create roles. |
| PRIV_DROP_ROLE | varchar(3) | NO | Whether the user has the permission to drop roles. |
| PRIV_TRIGGER | varchar(3) | NO | Whether the user has the permission to activate triggers. |
| PRIV_ENCRYPT | varchar(3) | NO | Whether the user has the permission to call the ENHANCED_AES_ENCRYPT function. |
| PRIV_DECRYPT | varchar(3) | NO | Whether the user has the permission to call the ENHANCED_AES_DECRYPT function. |
Sample query
In the sys tenant, query the user and role information of the current tenant.
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_USERS\G
The query result is as follows:
*************************** 1. row ***************************
USER_NAME: root
HOST: %
PASSWD: *****************************
INFO: system administrator
PRIV_ALTER: YES
PRIV_CREATE: YES
PRIV_DELETE: YES
PRIV_DROP: YES
PRIV_GRANT_OPTION: YES
PRIV_INSERT: YES
PRIV_UPDATE: YES
PRIV_SELECT: YES
PRIV_INDEX: YES
PRIV_CREATE_VIEW: YES
PRIV_SHOW_VIEW: YES
PRIV_SHOW_DB: YES
PRIV_CREATE_USER: YES
PRIV_SUPER: YES
IS_LOCKED: NO
PRIV_PROCESS: YES
PRIV_CREATE_SYNONYM: YES
SSL_TYPE: 0
SSL_CIPHER:
X509_ISSUER:
X509_SUBJECT:
TYPE: USER
PROFILE_ID: -1
PASSWORD_LAST_CHANGED: 2025-02-05 17:45:17.522389
PRIV_FILE: YES
PRIV_ALTER_TENANT: YES
PRIV_ALTER_SYSTEM: YES
PRIV_CREATE_RESOURCE_POOL: YES
PRIV_CREATE_RESOURCE_UNIT: YES
MAX_CONNECTIONS: 0
MAX_USER_CONNECTIONS: 0
PRIV_REPL_SLAVE: YES
PRIV_REPL_CLIENT: YES
PRIV_DROP_DATABASE_LINK: YES
PRIV_CREATE_DATABASE_LINK: YES
PRIV_EXECUTE: YES
PRIV_ALTER_ROUTINE: YES
PRIV_CREATE_ROUTINE: YES
PRIV_CREATE_TABLESPACE: YES
PRIV_SHUTDOWN: YES
PRIV_RELOAD: YES
PRIV_REFERENCES: YES
PRIV_CREATE_ROLE: YES
PRIV_DROP_ROLE: YES
PRIV_TRIGGER: YES
PRIV_ENCRYPT: YES
PRIV_DECRYPT: YES
*************************** 2. row ***************************
USER_NAME: ORAAUDITOR
HOST: %
PASSWD: *****************************
INFO: system administrator
PRIV_ALTER: NO
PRIV_CREATE: NO
PRIV_DELETE: NO
PRIV_DROP: NO
PRIV_GRANT_OPTION: NO
PRIV_INSERT: NO
PRIV_UPDATE: NO
PRIV_SELECT: NO
PRIV_INDEX: NO
PRIV_CREATE_VIEW: NO
PRIV_SHOW_VIEW: NO
PRIV_SHOW_DB: NO
PRIV_CREATE_USER: NO
PRIV_SUPER: NO
IS_LOCKED: YES
PRIV_PROCESS: NO
PRIV_CREATE_SYNONYM: NO
SSL_TYPE: 0
SSL_CIPHER:
X509_ISSUER:
X509_SUBJECT:
TYPE: USER
PROFILE_ID: -1
PASSWORD_LAST_CHANGED: 2025-02-05 17:44:33.714287
PRIV_FILE: NO
PRIV_ALTER_TENANT: NO
PRIV_ALTER_SYSTEM: NO
PRIV_CREATE_RESOURCE_POOL: NO
PRIV_CREATE_RESOURCE_UNIT: NO
MAX_CONNECTIONS: 0
MAX_USER_CONNECTIONS: 0
PRIV_REPL_SLAVE: NO
PRIV_REPL_CLIENT: NO
PRIV_DROP_DATABASE_LINK: NO
PRIV_CREATE_DATABASE_LINK: NO
PRIV_EXECUTE: NO
PRIV_ALTER_ROUTINE: NO
PRIV_CREATE_ROUTINE: NO
PRIV_CREATE_TABLESPACE: NO
PRIV_SHUTDOWN: NO
PRIV_RELOAD: NO
PRIV_REFERENCES: NO
PRIV_CREATE_ROLE: NO
PRIV_DROP_ROLE: NO
PRIV_TRIGGER: NO
PRIV_ENCRYPT: NO
PRIV_DECRYPT: NO
*************************** 3. row ***************************
USER_NAME: roletest
HOST: %
PASSWD:
INFO:
PRIV_ALTER: NO
PRIV_CREATE: NO
PRIV_DELETE: NO
PRIV_DROP: NO
PRIV_GRANT_OPTION: NO
PRIV_INSERT: NO
PRIV_UPDATE: NO
PRIV_SELECT: NO
PRIV_INDEX: NO
PRIV_CREATE_VIEW: NO
PRIV_SHOW_VIEW: NO
PRIV_SHOW_DB: NO
PRIV_CREATE_USER: NO
PRIV_SUPER: NO
IS_LOCKED: YES
PRIV_PROCESS: NO
PRIV_CREATE_SYNONYM: NO
SSL_TYPE: 0
SSL_CIPHER:
X509_ISSUER:
X509_SUBJECT:
TYPE: ROLE
PROFILE_ID: -1
PASSWORD_LAST_CHANGED: 2025-02-27 16:35:07.032215
PRIV_FILE: NO
PRIV_ALTER_TENANT: NO
PRIV_ALTER_SYSTEM: NO
PRIV_CREATE_RESOURCE_POOL: NO
PRIV_CREATE_RESOURCE_UNIT: NO
MAX_CONNECTIONS: 0
MAX_USER_CONNECTIONS: 0
PRIV_REPL_SLAVE: NO
PRIV_REPL_CLIENT: NO
PRIV_DROP_DATABASE_LINK: NO
PRIV_CREATE_DATABASE_LINK: NO
PRIV_EXECUTE: NO
PRIV_ALTER_ROUTINE: NO
PRIV_CREATE_ROUTINE: NO
PRIV_CREATE_TABLESPACE: NO
PRIV_SHUTDOWN: NO
PRIV_RELOAD: NO
PRIV_REFERENCES: NO
PRIV_CREATE_ROLE: NO
PRIV_DROP_ROLE: NO
PRIV_TRIGGER: NO
PRIV_ENCRYPT: NO
PRIV_DECRYPT: NO
3 rows in set