Note
This view is available starting with V4.0.0.
Overview
Displays user or role information under all tenants in the cluster.
Columns
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | Tenant ID. |
| USER_NAME | varchar(128) | NO | Username or role name. |
| HOST | varchar(128) | NO | The server name. |
| PASSWD | varchar(128) | NO | User or role password. |
| PLUGIN | varchar(64) | NO | The name of the plugin used for password hash calculation. Valid values are as follows:
NoteFor V4.6.x, this field is available starting with V4.6.1. |
| INFO | varchar(4096) | NO | The information about the user or role. |
| PRIV_ALTER | varchar(3) | NO | Indicates whether the user or role has the privilege to modify databases or tables. |
| PRIV_CREATE | varchar(3) | NO | Indicates whether the user or role has the privilege to create databases or tables. |
| PRIV_DELETE | varchar(3) | NO | Indicates whether the user or role has the privilege to delete records in databases or tables. |
| PRIV_DROP | varchar(3) | NO | Indicates whether the user or role has the privilege to drop databases or tables. |
| PRIV_GRANT_OPTION | varchar(3) | NO | Indicates whether the user or role has the privilege to grant privileges. |
| PRIV_INSERT | varchar(3) | NO | Indicates whether the user or role has the privilege to insert records. |
| PRIV_UPDATE | varchar(3) | NO | Indicates whether the user or role has the privilege to update records. |
| PRIV_SELECT | varchar(3) | NO | Indicates whether the user or role has the privilege to query records. |
| PRIV_INDEX | varchar(3) | NO | Indicates whether the user or role has the privilege to create indexes. |
| PRIV_CREATE_VIEW | varchar(3) | NO | Indicates whether the user or role has the privilege to create views. |
| PRIV_SHOW_VIEW | varchar(3) | NO | Indicates whether the user or role has the privilege to query views. |
| PRIV_SHOW_DB | varchar(3) | NO | Indicates whether the user or role has the privilege to query all databases. |
| PRIV_CREATE_USER | varchar(3) | NO | Indicates whether the user or role has the privilege to create users. |
| PRIV_SUPER | varchar(3) | NO | Indicates whether the user or role has the privilege of a superuser. |
| IS_LOCKED | varchar(3) | NO | Indicates whether the user or role is locked. |
| PRIV_PROCESS | varchar(3) | NO | Indicates whether the user or role has the privilege to query all threads. |
| PRIV_CREATE_SYNONYM | varchar(3) | NO | Indicates whether the user or role has the privilege to create synonyms. |
| SSL_TYPE | bigint(20) | NO | Supports SSL standard encryption security types. |
| SSL_CIPHER | varchar(1024) | NO | Supports SSL standard encryption security password. |
| X509_ISSUER | varchar(1024) | NO | X.509 publisher name. |
| X509_SUBJECT | varchar(1024) | NO | X.509 certificate subject name. |
| TYPE | varchar(4) | NO | Type. Valid values:
|
| PROFILE_ID | bigint(20) | NO | Profile ID. |
| PASSWORD_LAST_CHANGED | timestamp(6) | YES | The last time when the password was changed. |
| PRIV_FILE | varchar(3) | NO | Indicates whether the user or role has the privilege to query files. |
| PRIV_ALTER_TENANT | varchar(3) | NO | Indicates whether the user or role has the privilege to modify tenant information. |
| PRIV_ALTER_SYSTEM | varchar(3) | NO | Indicates whether the user or role has the privilege to modify server configuration parameters. |
| PRIV_CREATE_RESOURCE_POOL | varchar(3) | NO | Indicates whether the user or role has the privilege to create, modify, and drop resource pools. |
| PRIV_CREATE_RESOURCE_UNIT | varchar(3) | NO | Indicates whether the user or role has the privilege to create, modify, and drop 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 | Indicates whether the user or role has the privilege to manage a replica server. A tenant can specify the locations of the replica server and the primary server. |
| PRIV_REPL_CLIENT | varchar(3) | NO | Indicates whether the user or role has the privilege to manage a primary server. A tenant can read binary log files for maintaining the replication database environment. The user is located in the primary system and facilitates communication between the host and client. |
| PRIV_DROP_DATABASE_LINK | varchar(3) | NO | Indicates whether the user or role has the privilege to drop database links. |
| PRIV_CREATE_DATABASE_LINK | varchar(3) | NO | Indicates whether the user or role has the privilege to create database links. |
| PRIV_EXECUTE | varchar(3) | NO | Indicates whether the user or role has the privilege to execute procedures and functions. |
| PRIV_ALTER_ROUTINE | varchar(3) | NO | Indicates whether the user or role has the privilege to modify and drop procedures and functions. |
| PRIV_CREATE_ROUTINE | varchar(3) | NO | Indicates whether the user or role has the privilege to create procedures and functions. |
| PRIV_CREATE_TABLESPACE | varchar(3) | NO | Indicates whether the user or role has the privilege to create, modify, and drop tablespaces. |
| PRIV_SHUTDOWN | varchar(3) | NO | Indicates whether the user or role has the privilege to execute the mysqladmin shutdown command. |
| PRIV_RELOAD | varchar(3) | NO | Indicates whether the user or role has the privilege to perform the FLUSH operation. |
| PRIV_REFERENCES | varchar(3) | NO | Indicates whether the user has the privilege to create foreign keys. |
| PRIV_CREATE_ROLE | varchar(3) | NO | Indicates whether the user has the privilege to create roles. |
| PRIV_DROP_ROLE | varchar(3) | NO | Indicates whether the user has the privilege to drop roles. |
| PRIV_TRIGGER | varchar(3) | NO | Indicates whether the user has the privilege to activate triggers. |
| PRIV_ENCRYPT | varchar(3) | NO | Indicates whether the user has the privilege to call the ENHANCED_AES_ENCRYPT function. |
| PRIV_DECRYPT | varchar(3) | NO | Indicates whether the user has the privilege to call the ENHANCED_AES_DECRYPT function. |
| 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. |
| PRIV_CREATE_CATALOG | varchar(3) | NO | Indicates whether the user has the privilege to create CATALOGs. |
| PRIV_USE_CATALOG | varchar(3) | NO | Indicates whether the user has the privilege to use CATALOGs. |
| PRIV_CREATE_SENSITIVE_RULE | varchar(3) | NO | Indicates whether the user has the privilege to create SENSITIVE RULEs. |
| PRIV_PLAINACCESS | varchar(3) | NO | Indicates whether the user has plaintext access to all SENSITIVE RULEs. |
| OLD_PASSWORD | varchar(128) | NO | The hash value (ciphertext) of the old password. This field is valid when the dual-password feature is enabled or during a password rotation period.
NoteThis field is available starting with V4.6.1. |
| OLD_PASSWORD_START_TIME | bigint(20) | NO | The time when the old password became effective. The value -1 indicates that no old password is available. In Oracle tenants, this value is also used to determine whether the old password is still within the rotation period.
NoteThis field is available starting with V4.6.1. |
Sample query
In the sys tenant, view the user and role information under the tenant with ID 1002.
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_USERS WHERE TENANT_ID=1002\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1002
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:48:54.593368
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
OLD_PASSWORD: *********************
OLD_PASSWORD_START_TIME: 1698765432100000
*************************** 2. row ***************************
TENANT_ID: 1002
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:45:58.962446
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
OLD_PASSWORD: *********************
OLD_PASSWORD_START_TIME: 1698765432100000
*************************** 3. row ***************************
TENANT_ID: 1002
USER_NAME: test2
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: NO
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-20 15:11:58.158111
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
OLD_PASSWORD: *********************
OLD_PASSWORD_START_TIME: 1698765432100000
3 rows in set