Note
This view is introduced since OceanBase Database V4.0.0.
Purpose
The oceanbase.DBA_OB_USERS view displays 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 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 | Whether the account is locked. |
| 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 | Standby server management. The tenant can determine the locations of the replication slave and master servers. |
| PRIV_REPL_CLIENT | varchar(3) | NO | Primary server management. The tenant can read binary log files used to maintain the replication database environment. This user resides in the master system, facilitating communication between the host 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. |
Sample query
In the sys tenant, view the user and role information within the current tenant.
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_USERS\G
The query results are 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