Purpose
This view displays all users in the current tenant.
Applicability
This view is available only in OceanBase Database Enterprise Edition.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| USERNAME | VARCHAR2(128) | NO | The username. |
| USER_ID | NUMBER(38) | NO | The ID of the user. |
| PASSWORD | VARCHAR2(128) | NO | This column is not recommended to be used with the AUTHENTICATION_TYPE column. |
| ACCOUNT_STATUS | VARCHAR2(30) | NO | The account status:
|
| LOCK_DATE | DATE | NO | The date when the account was locked. |
| EXPIRY_DATE | DATE | NO | The expiration date of the account. |
| DEFAULT_TABLESPACE | VARCHAR2(30) | NO | The default tablespace for data. |
| TEMPORARY_TABLESPACE | VARCHAR2(30) | NO | The name of the default temporary tablespace or temporary tablespace group. |
| LOCAL_TEMP_TABLESPACE | VARCHAR2(30) | NO | The name of the local temporary tablespace.
NoteThis column is available starting with V4.2.1 BP2. |
| CREATED | DATE | NO | The date when the user was created. |
| PROFILE | VARCHAR2(128) | NO | The name of the user resource profile.
NoteThis column is available starting with V4.2.1 BP2. |
| INITIAL_RSRC_CONSUMER_GROUP | VARCHAR2(30) | NO | The initial resource consumer group of the user. |
| EXTERNAL_NAME | VARCHAR2(4000) | NO | The external name of the user. |
| PASSWORD_VERSIONS | VARCHAR2(12) | NO | The hash algorithm version of the password.
NoteThis column is available starting with V4.2.1 BP2. |
| EDITIONS_ENABLED | VARCHAR2(1) | NO | Specifies whether to enable editions for the user.
NoteThis column is available starting with V4.2.1 BP2. |
| AUTHENTICATION_TYPE | VARCHAR2(8) | NO | The authentication mechanism of the user.
NoteThis column is available starting with V4.2.1 BP2. |
| PROXY_ONLY_CONNECT | VARCHAR2(1) | NO | Specifies whether to connect only through a proxy user.
NoteThis column is available starting with V4.2.1 BP2. |
| COMMON | VARCHAR2(3) | NO | Specifies whether the user is a common user.
NoteThis column is available starting with V4.2.1 BP2. |
| LAST_LOGIN | TIMESTAMP(9) WITH TIME ZONE | NO | The last login time of the user.
NoteThis column is available starting with V4.2.1 BP2. |
| ORACLE_MAINTAINED | VARCHAR2(1) | NO | Specifies whether the script is created and maintained by Oracle mode.
NoteThis column is available starting with V4.2.1 BP2. |
| INHERITED | VARCHAR2(3) | NO | Specifies whether the user is inherited from another container.
NoteThis column is available starting with V4.2.1 BP2. |
| DEFAULT_COLLATION | VARCHAR2(100) | NO | Default character set
NoteThis column is available starting with V4.2.1 BP2. |
| IMPLICIT | VARCHAR2(3) | NO | Indicates whether the user was created by an implicit application.
NoteThis column is available starting with V4.2.1 BP2. |
| ALL_SHARD | VARCHAR2(3) | NO | Indicates whether the user was created in a sharded environment.
NoteThis column is available starting with V4.2.1 BP2. |
| PASSWORD_CHANGE_DATE | DATE | YES | The last time the password was changed.
NoteThis column is available starting with V4.2.1 BP2. |
Sample query
Query all database users in the current tenant.
obclient> SELECT * FROM SYS.DBA_USERS\G
The query result is as follows:
*************************** 1. row ***************************
USERNAME: SYS
USERID: 200003
PASSWORD: ***************************
ACCOUNT_STATUS: OPEN
LOCK_DATE: NULL
EXPIRY_DATE: NULL
DEFAULT_TABLESPACE: NULL
TEMPORARY_TABLESPACE: NULL
LOCAL_TEMP_TABLESPACE: NULL
CREATED: 23-JUL-24
PROFILE: DEFAULT
INITIAL_RSRC_CONSUMER_GROUP: NULL
EXTERNAL_NAME: NULL
PASSWORD_VERSIONS: NULL
EDITIONS_ENABLED: N
AUTHENTICATION_TYPE: PASSWORD
PROXY_ONLY_CONNECT: N
COMMON: NO
LAST_LOGIN: NULL
ORACLE_MAINTAINED: N
INHERITED: NO
DEFAULT_COLLATION: USING_NLS_COMP
IMPLICIT: NO
ALL_SHARD: NO
PASSWORD_CHANGE_DATE: 23-JUL-24
*************************** 2. row ***************************
USERNAME: LBACSYS
USERID: 200004
PASSWORD: *******************************
ACCOUNT_STATUS: LOCKED
LOCK_DATE: NULL
EXPIRY_DATE: NULL
DEFAULT_TABLESPACE: NULL
TEMPORARY_TABLESPACE: NULL
LOCAL_TEMP_TABLESPACE: NULL
CREATED: 23-JUL-24
PROFILE: DEFAULT
INITIAL_RSRC_CONSUMER_GROUP: NULL
EXTERNAL_NAME: NULL
PASSWORD_VERSIONS: NULL
EDITIONS_ENABLED: N
AUTHENTICATION_TYPE: PASSWORD
PROXY_ONLY_CONNECT: N
COMMON: NO
LAST_LOGIN: NULL
ORACLE_MAINTAINED: N
INHERITED: NO
DEFAULT_COLLATION: USING_NLS_COMP
IMPLICIT: NO
ALL_SHARD: NO
PASSWORD_CHANGE_DATE: 23-JUL-24
*************************** 3. row ***************************
USERNAME: ORAAUDITOR
USERID: 200005
PASSWORD: *******************************
ACCOUNT_STATUS: LOCKED
LOCK_DATE: NULL
EXPIRY_DATE: NULL
DEFAULT_TABLESPACE: NULL
TEMPORARY_TABLESPACE: NULL
LOCAL_TEMP_TABLESPACE: NULL
CREATED: 23-JUL-24
PROFILE: DEFAULT
INITIAL_RSRC_CONSUMER_GROUP: NULL
EXTERNAL_NAME: NULL
PASSWORD_VERSIONS: NULL
EDITIONS_ENABLED: N
AUTHENTICATION_TYPE: PASSWORD
PROXY_ONLY_CONNECT: N
COMMON: NO
LAST_LOGIN: NULL
ORACLE_MAINTAINED: N
INHERITED: NO
DEFAULT_COLLATION: USING_NLS_COMP
IMPLICIT: NO
ALL_SHARD: NO
PASSWORD_CHANGE_DATE: 23-JUL-24
3 rows in set
References
- Query all users visible to the current user: ALL_USERS
- Query information about the current user: USER_USERS
- For more information about users and privileges, see Overview of users and privileges.