Oracle 视图 USER_USERS 官方解释,作用,如何使用详细说明

本站中文解释

Oracle视图是Oracle数据库中使用的抽象化的表,用于为用户提供具有特定功能的查询结果。USER_USERS是Oracle数据库中描述当前用户下所有用户信息的视图。

使用方法:
1. 使用select语句查询USER_USERS视图
例如:

SELECT USERNAME, USER_ID, CREATED
FROM USER_USERS;

2. 可以将USER_USERS视图的查询结果用作其他查询的基础,来查询更详细的用户信息
例如:

SELECT *
FROM USER_USERS U, USER_USER_ROLES R
WHERE U.USER_ID = R.USER_ID;

这条查询语句能够将用户表与用户角色表关联查询,从而得到更详细的用户信息。

官方英文解释

USER_USERS describes the current user.

Column Datatype NULL Description

USERNAME

VARCHAR2(128)

NOT NULL

Name of the user

USER_ID

NUMBER

NOT NULL

ID number of the user

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 USERPASSWORD 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 USERACCOUNT 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 USERACCOUNT 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 USERACCOUNT 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

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.

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).

For more information about creating proxy user accounts and authorizing users to connect through them, 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)

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_SHARDFoot 1

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_VIOLATIONFoot 1

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.

Footnote 1 This column is available starting with Oracle Database 21c.

See Also:

  • “DBA_USERS”

  • Using
    Oracle Sharding
    for more information about sharded database management


数据运维技术 » Oracle 视图 USER_USERS 官方解释,作用,如何使用详细说明