DBA_USERS

7.106 DBA_USERS

DBA_USERS describes all users of the database.

Related View

USER_USERS describes the current user. This view does not display the PASSWORD, PROFILE, PASSWORD_VERSIONS, EDITIONS_ENABLED, AUTHENTICATION_TYPE, and LAST_LOGIN columns.

Column	Datatype	NULL	Description
`USERNAME`	`VARCHAR2(128)`	`NOT NULL`	Name of the user
`USER_ID`	`NUMBER`	`NOT NULL`	ID number of the user
`PASSWORD`	`VARCHAR2(4000)`		This column is deprecated in favor of the `AUTHENTICATION_TYPE` column
`ACCOUNT_STATUS`	`VARCHAR2(32)`	`NOT NULL`	Account status: `OPEN` The account is open. `EXPIRED` The password for the account is expired, either because the `PASSWORD_LIFE_TIME` limit was reached or because the password was expired by the `ALTER` `USER` ... `PASSWORD` `EXPIRE` command. The user can log in with the expired password, then change the password. `EXPIRED(GRACE)` The password for the account is expired because the `PASSWORD_LIFE_TIME` limit was reached, but the password change grace period (`PASSWORD_GRACE_TIME`) has not yet elapsed. The user can log in with the expired password, but will receive an `ORA-28002` warning as a reminder that the password must soon be changed. If the `PASSWORD_GRACE_TIME` elapses, the user can log in with the expired password, then change the password. `LOCKED` The account is locked, either by the `ALTER` `USER` ... `ACCOUNT` `LOCK` command, or because the number of consecutive failed login attempts exceeded the `FAILED_LOGIN_ATTEMPTS` limit and the value of `PASSWORD_LOCK_TIME` is `UNLIMITED`. The account can be unlocked by the `ALTER` `USER` ... `ACCOUNT` `UNLOCK` command. `LOCKED(TIMED)` The account is locked because the number of consecutive failed login attempts exceeded the `FAILED_LOGIN_ATTEMPTS` limit and the `PASSWORD_LOCK_TIME` has not yet elapsed. The account can be unlocked either by the `ALTER` `USER` ... `ACCOUNT` `UNLOCK` command or by waiting until the `PASSWORD_LOCK_TIME` has elapsed. `EXPIRED & LOCKED` The password for the account is expired, as described for the `EXPIRED` account status, and the account is locked as described for the `LOCKED` account status. The account can first be unlocked as described for the `LOCKED` account status, then the password can be changed as described for the `EXPIRED` account status. `EXPIRED(GRACE) & LOCKED` The password for the account is expired, as described for the `EXPIRED(GRACE)` account status, and the account is locked as described for the `LOCKED` account status. The account can first be unlocked as described for the `LOCKED` account status, then the password can be changed as described for the `EXPIRED(GRACE)` account status. `EXPIRED & LOCKED(TIMED)` The password for the account is expired, as described for the `EXPIRED` account status, and the account is locked as described for the `LOCKED(TIMED)` account status. The account can first be unlocked as described for the `LOCKED(TIMED)` account status, then the password can be changed as described for the `EXPIRED` account status. `EXPIRED(GRACE) & LOCKED(TIMED)` The password for the account is expired, as described for the `EXPIRED(GRACE)` account status, and the account is locked as described for the `LOCKED(TIMED)` account status. The account can first be unlocked as described for the `LOCKED(TIMED)` account status, then the password can be changed as described for the `EXPIRED(GRACE)` account status. `OPEN & IN ROLLOVER` The account is in the password rollover period. The user can log in with either the earlier password or the new password. However, at the time the user logs in, the server recalculates whether the account is still in its password rollover period. If the password rollover period has elapsed, then the login will succeed only if the new password was specified, and the account status will change to `OPEN`. `EXPIRED & IN ROLLOVER` The account is in the password rollover period and the password is expired as described for the `EXPIRED` account status. The user can log in with either the earlier password or the new password. However, at the time the user logs in, the server recalculates whether the account is still in its password rollover period. If the password rollover period has elapsed, then the login will succeed only if the new password was specified, and the account status will change to `EXPIRED`. After logging in, the user will be prompted to change the password. `LOCKED & IN ROLLOVER` The account is in the password rollover period and is also locked as described for the `LOCKED` account status. The account can be unlocked as described for the `LOCKED` account status, after which the user can log in as described for the `OPEN & IN ROLLOVER` account status. `EXPIRED & LOCKED & IN ROLLOVER` The account is in the password rollover period, its password is expired as described for the `EXPIRED` account status, and the account is locked as described for the `LOCKED` account status. The account can be unlocked as described for the `LOCKED` account status, after which the user can log in as described for the `EXPIRED & IN ROLLOVER` account status. `LOCKED(TIMED) & IN ROLLOVER` The account is in the password rollover period and is also locked as described for the `LOCKED(TIMED)` account status. The account can be unlocked as described for the `LOCKED(TIMED)` account status, after which the user can log in with either the earlier password or the new password. However, at the time the user logs in, the server recalculates whether the account is still in its password rollover period. If the password rollover period has elapsed, then the login will succeed only if the new password was specified. `EXPIRED & LOCKED(TIMED) & IN ROL` The account is in the password rollover period, its password is expired as described for the `EXPIRED` account status, and the account is locked as described for the `LOCKED(TIMED)` account status. The account can be unlocked as described for the `LOCKED(TIMED)` account status, after which the user can log in as described for the `EXPIRED & IN ROLLOVER` account status.
`LOCK_DATE`	`DATE`		Date the account was locked if account status was `LOCKED`
`EXPIRY_DATE`	`DATE`		Date of expiration of the account
`DEFAULT_TABLESPACE`	`VARCHAR2(30)`	`NOT NULL`	Default tablespace for data
`TEMPORARY_TABLESPACE`	`VARCHAR2(30)`	`NOT NULL`	Name of the default tablespace for temporary tables or the name of a tablespace group
`LOCAL_TEMP_TABLESPACE`	`VARCHAR2(30)`		Default local temporary tablespace for the user
`CREATED`	`DATE`	`NOT NULL`	User creation date
`PROFILE`	`VARCHAR2(128)`	`NOT NULL`	User resource profile name
`INITIAL_RSRC_CONSUMER_GROUP`	`VARCHAR2(128)`		Initial resource consumer group for the user
`EXTERNAL_NAME`	`VARCHAR2(4000)`		User external name. For centrally managed users, if the database user mapping is an exclusive mapping, then this will be the directory service DN for the user. If this database user is a shared schema, it will be the DN of a group.
`PASSWORD_VERSIONS`	`VARCHAR2(12)`		Shows the list of versions of the password hashes (also known as "verifiers") existing for the account. The values for this column can include: `11G`: If a SHA-1 hash exists `12C`: If a de-optimized PBKDF2-based hash exists `HTTP`: If an MD5 hash (for HTTP Digest authentication) exists For more information about the 12C verifier, see Oracle Database Concepts. Note that any combination of these verifiers can exist for any given account.
`EDITIONS_ENABLED`	`VARCHAR2(1)`		Indicates whether editions have been enabled for the corresponding user (`Y`) or not (`N`)
`AUTHENTICATION_TYPE`	`VARCHAR2(8)`		Indicates the authentication mechanism for the user: `NONE` - The user has not been configured for an authentication method `EXTERNAL` - `CREATE` `USER` `user1` `IDENTIFIED` `EXTERNALLY;` `GLOBAL` - `CREATE` `USER` `user2` `IDENTIFIED` `GLOBALLY;` `PASSWORD` - `CREATE` `USER` `user3` `IDENTIFIED` `BY` `password;`
`PROXY_ONLY_CONNECT`	`VARCHAR2(1)`		Indicates whether a user can connect directly (`N`) or whether the account can only be proxied (`Y`) by users who have proxy privileges for this account (that is, by users who have been granted the "connect through" privilege for this account). Note: Setting `PROXY_ONLY_CONNECT` for users is deprecated in this release, and may be desupported in a future release. Oracle recommends that you instead use schema-only accounts. For more information about schema-only accounts, see Oracle Database Security Guide.
`COMMON`	`VARCHAR2(3)`		Indicates whether a given user is common. Possible values `YES` if a user is common `NO` if a user is local (not common)
`LAST_LOGIN`	`TIMESTAMP(9) WITH TIME ZONE`		The time of the last user login This column is not populated when a user connects to the database with administrative privileges, that is, `AS` `{` `SYSASM` `\|` `SYSBACKUP` `\|` `SYSDBA` `\|` `SYSDG` `\|` `SYSOPER` `\|` `SYSRAC` `\|` `SYSKM` `}`.
`ORACLE_MAINTAINED`	`VARCHAR2(1)`		Denotes whether the user was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql). A user for which this column has the value `Y` must not be changed in any way except by running an Oracle-supplied script.
`INHERITED`	`VARCHAR2(3)`		Indicates whether the user definition was inherited from another container (`YES`) or not (`NO`)
`DEFAULT_COLLATION`	`VARCHAR2(100)`		Default collation for the user’s schema
`IMPLICIT`	`VARCHAR2(3)`		Indicates whether this user is a common user created by an implicit application (`YES`) or not (`NO`)
`ALL_SHARD`	`VARCHAR2(3)`		In a sharded database, the value in this column indicates whether the user was created with shard DDL enabled. The possible values are: `YES`: The user was created with shard DDL enabled. The user exists on all shards and the shard catalog. `NO`: The user was created without shard DDL enabled. The user exists only in the database in which the user was created. In a non-sharded database, the value in this column is always `NO`.
`EXTERNAL_SHARD`	`VARCHAR2(3)`		In a federated sharded database, the value in this column indicates whether the user is an external shard user (`YES`) or not (`NO`). In other types of databases, the value in this column is always `NO`.
`PASSWORD_CHANGE_DATE`	`DATE`		Date on which the user's password was last set This column is populated only when the value of the `AUTHENTICATION_TYPE` column is `PASSWORD`. Otherwise, this column is null.
`MANDATORY_PROFILE_VIOLATION`	`VARCHAR2(3)`		If the value in this column is `YES`, then the user account password violates the mandatory profile password complexity requirements and must be changed before the grace period expires. Otherwise, the value in this column is `NO`.
`PROTECTED`	`VARCHAR2(3)`		Indicates whether the user is a protected user (`YES`) or not (`NO`) A protected user can be managed only by another protected user or a common user.
`READ_ONLY`	`VARCHAR2(3)`		Indicates whether write privileges for the user are disabled (`YES`) or enabled (`NO`)
`DICTIONARY_PROTECTED`	`VARCHAR2(3)`		Indicates whether the user's schema is dictionary protected (`YES`) or not (`NO`) See Also: Oracle Database Security Guide for more information about dictionary-protected schemas