Note
This view is available starting with V4.0.0.
Overview
Displays the user and role information under the current tenant.
Columns
Field Name |
Data type |
Whether can be NULL |
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 you have the privilege to modify the database or table. |
| PRIV_CREATE | varchar(3) | NO | Whether the user has the privilege to create databases or tables. |
| PRIV_DELETE | varchar(3) | NO | Whether the user has the permission to delete database or table records |
| PRIV_DROP | varchar(3) | NO | Whether the user has the privilege to drop databases or tables |
| PRIV_GRANT_OPTION | varchar(3) | NO | Whether Sub-authorization is Allowed |
| PRIV_INSERT | varchar(3) | NO | Whether the user has the privilege to insert records |
| PRIV_UPDATE | varchar(3) | NO | Whether to grant the permission to update records |
| PRIV_SELECT | varchar(3) | NO | Whether to grant the privilege to query records |
| PRIV_INDEX | varchar(3) | NO | Whether the index creation privilege is granted |
| PRIV_CREATE_VIEW | varchar(3) | NO | Whether the user has the privilege to create views |
| PRIV_SHOW_VIEW | varchar(3) | NO | Whether the user has the privilege to view the view |
| PRIV_SHOW_DB | varchar(3) | NO | Do you have the permission to view all databases? |
| PRIV_CREATE_USER | varchar(3) | NO | Whether the user has the permission to create users |
| PRIV_SUPER | varchar(3) | NO | Superuser Privilege |
| IS_LOCKED | varchar(3) | NO | Locked |
| PRIV_PROCESS | varchar(3) | NO | Is the permission to view all threads granted? |
| PRIV_CREATE_SYNONYM | varchar(3) | NO | Whether the user has the privilege to create synonyms |
| SSL_TYPE | bigint(20) | NO | Supports SSL standard encryption security type |
| SSL_CIPHER | varchar(1024) | NO | Supports SSL standard encryption security passwords |
| X509_ISSUER | varchar(1024) | NO | X.509 Publisher Name |
| X509_SUBJECT | varchar(1024) | NO | X.509 Certificate Subject Name |
| TYPE | varchar(4) | NO | Type:
|
| PROFILE_ID | bigint(20) | NO | Profile ID |
| PASSWORD_LAST_CHANGED | timestamp(6) | YES | Last Password Change |
| PRIV_FILE | varchar(3) | NO | Check file permissions |
| PRIV_ALTER_TENANT | varchar(3) | NO | Whether to modify tenant information |
| PRIV_ALTER_SYSTEM | varchar(3) | NO | Whether to modify server configuration parameters |
| PRIV_CREATE_RESOURCE_POOL | varchar(3) | NO | Whether you have the permission to create, modify, and delete resource pools. |
| PRIV_CREATE_RESOURCE_UNIT | varchar(3) | NO | Whether you have the permission to create, modify, and delete resource units. |
| MAX_CONNECTIONS | bigint(20) | NO | Maximum Connections |
| MAX_USER_CONNECTIONS | bigint(20) | NO | Maximum Tenant Connections |
| PRIV_REPL_SLAVE | varchar(3) | NO | Manage from the secondary server. The tenant can determine the locations of the replication secondary server and the primary server. |
| PRIV_REPL_CLIENT | varchar(3) | NO | Primary server management. Tenants can read binary log files used to maintain the replicated database environment. This user is located in the primary system, facilitating communication between the host and client. |
| PRIV_DROP_DATABASE_LINK | varchar(3) | NO | Whether the user has the privilege to delete database links |
| PRIV_CREATE_DATABASE_LINK | varchar(3) | NO | Whether the user has the privilege to create database links. |
| PRIV_EXECUTE | varchar(3) | NO | Whether the user has the privilege to execute procedures and functions. |
| PRIV_ALTER_ROUTINE | varchar(3) | NO | Whether to grant the privilege to modify and drop procedures and functions |
| PRIV_CREATE_ROUTINE | varchar(3) | NO | Whether the user has the privilege to create procedures and functions |
| PRIV_CREATE_TABLESPACE | varchar(3) | NO | Whether you have the privilege to create, modify, and drop tablespaces. |
| PRIV_SHUTDOWN | varchar(3) | NO | Whether the user has permission to execute the mysqladmin shutdown command |
| PRIV_RELOAD | varchar(3) | NO | Whether the user has the privilege to perform a flush operation. |
| PRIV_REFERENCES | varchar(3) | NO | Whether the user has the privilege to create foreign keys |
| PRIV_CREATE_ROLE | varchar(3) | NO | Whether the role creation permission is granted |
| PRIV_DROP_ROLE | varchar(3) | NO | Whether the role has permission to delete roles |
| PRIV_TRIGGER | varchar(3) | NO | Whether the user has the privilege to activate trigger operations |
| PRIV_ENCRYPT | varchar(3) | NO | Whether the ENHANCED_AES_ENCRYPT function can be called |
| PRIV_DECRYPT | varchar(3) | NO | Whether to enable the ENHANCED_AES_DECRYPT function |
| PRIV_LOCK_TABLE | varchar(3) | NO | Whether the user has the privilege to lock the table. |
| PRIV_EVENT | varchar(3) | NO | Whether the user has the permission to create and manage events |
| PRIV_CREATE_CATALOG | varchar(3) | NO | Whether the user has the privilege to create a CATALOG |
| PRIV_USE_CATALOG | varchar(3) | NO | Whether the user has the privilege to use CATALOG |
| PRIV_CREATE_SENSITIVE_RULE | varchar(3) | NO | Whether the user has the permission to create sensitive rules. |
| PRIV_PLAINACCESS | varchar(3) | NO | Whether explicit access permission is granted for all sensitive rules |
| OLD_PASSWORD | varchar(128) | NO | Hash value (ciphertext) of the old password. This field is not empty when dual-password authentication is enabled or during a password rotation period. |
| OLD_PASSWORD_START_TIME | bigint(20) | NO | The time point at which the old password becomes effective. (-1 indicates no old password is available.) In Oracle-compatible tenants, this value is also used to determine whether an old password is still within its rotation period; expired old passwords will be unusable. |
Sample query
In the sys tenant, view 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