Note
This view is introduced since OceanBase Database V4.0.0.
Purpose
The oceanbase.DBA_OB_USERS view displays the information about all users in the current tenant.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| USER_NAME | varchar(128) | NO | The username or role name. |
| HOST | varchar(128) | NO | The server name. |
| PASSWD | varchar(128) | NO | The user or role password. |
| INFO | varchar(4096) | NO | The information about the user or role. |
| 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 records from databases or tables exists. |
| PRIV_DROP | varchar(3) | NO | Whether the privilege to drop databases or tables exists. |
| PRIV_GRANT_OPTION | varchar(3) | NO | Whether the privilege to grant other privileges 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 create or manage 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 view existing views exists. |
| PRIV_SHOW_DB | varchar(3) | NO | Whether the privilege to view all databases exists. |
| PRIV_CREATE_USER | varchar(3) | NO | Whether the privilege to create users exists. |
| PRIV_SUPER | varchar(3) | NO | Whether the superuser privilege exists. |
| IS_LOCKED | varchar(3) | NO | The account lock status. |
| PRIV_PROCESS | varchar(3) | NO | Whether the privilege to view all threads exists. |
| PRIV_CREATE_SYNONYM | varchar(3) | NO | Whether the privilege to create synonyms exists. |
| SSL_TYPE | bigint(20) | NO | The supported SSL encryption type. |
| SSL_CIPHER | varchar(1024) | NO | The supported SSL encryption cipher. |
| X509_ISSUER | varchar(1024) | NO | The X.509 issuer name. |
| X509_SUBJECT | varchar(1024) | NO | The X.509 certificate subject name. |
| TYPE | varchar(4) | NO | The type. Valid values:
|
| PROFILE_ID | bigint(20) | NO | The profile ID. |
| PASSWORD_LAST_CHANGED | timestamp(6) | YES | The time when the password was last changed. |
| PRIV_FILE | varchar(3) | NO | Whether the privilege to access files exists. |
| PRIV_ALTER_TENANT | varchar(3) | NO | Whether the privilege to modify tenant information exists. |
| PRIV_ALTER_SYSTEM | varchar(3) | NO | Whether the privilege to change server configuration parameters exists. |
| PRIV_CREATE_RESOURCE_POOL | varchar(3) | NO | Whether the privilege to create, modify, or delete resource pools exists. |
| PRIV_CREATE_RESOURCE_UNIT | varchar(3) | NO | Whether the privilege to create, modify, or delete resource units exists. |
| 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 | The privilege for managing replication slaves, allowing tenants to determine the location of replication slaves and masters. |
| PRIV_REPL_CLIENT | varchar(3) | NO | The privilege for managing the master server, allowing tenants to read binary log files for maintaining a replicated database environment. This user exists on the master system and facilitates communication between the master and client. |
| PRIV_DROP_DATABASE_LINK | varchar(3) | NO | Whether the privilege to drop database links exists. |
| PRIV_CREATE_DATABASE_LINK | varchar(3) | NO | Whether the privilege to create database links 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 delete procedures and functions exists. |
| PRIV_CREATE_ROUTINE | varchar(3) | NO | Whether the privilege to create procedures and functions exists. |
| PRIV_CREATE_TABLESPACE | varchar(3) | NO | Whether the privilege to create, modify, or delete tablespaces exists. |
| PRIV_SHUTDOWN | varchar(3) | NO | Whether the privilege to execute the mysqladmin shutdown command exists. |
| PRIV_RELOAD | varchar(3) | NO | Whether the privilege to execute flush operations exists. |
| PRIV_REFERENCES | varchar(3) | NO | Whether the privilege to create foreign keys exists. |
| PRIV_CREATE_ROLE | varchar(3) | NO | Whether the privilege to create roles exists. |
| PRIV_DROP_ROLE | varchar(3) | NO | Whether the privilege to drop roles exists. |
| PRIV_TRIGGER | varchar(3) | NO | Whether the privilege to activate triggers exists. |
| PRIV_ENCRYPT | varchar(3) | NO | Whether the privilege to call the ENHANCED_AES_ENCRYPT function exists. |
| PRIV_DECRYPT | varchar(3) | NO | Whether the privilege to call the ENHANCED_AES_DECRYPT function exists. |
| OLD_PASSWORD | varchar(128) | NO | The hash value (ciphertext) of the old password. This field is valid only when the dual password feature is enabled or during password rotation.
NoteThis field is available starting with V4.4.2 BP1. |
| OLD_PASSWORD_START_TIME | bigint(20) | NO | The time when the old password becomes effective. A value of -1 indicates that no old password is available.
NoteThis field is available starting with V4.4.2 BP1. |
Sample query
A user tenant views the information of users and roles within 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: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 ***************************
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 ***************************
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