Oracle 视图 V$PWFILE_USERS 官方解释，作用，如何使用详细说明

本站中文解释

Oracle视图V$PWFILE_USERS用于查询pwfile中的用户记录。它用于显示 Oracle 数据库中所有有外部密码文件认证的用户。该视图可以为管理员提供有关用户认证的关键信息，例如密码文件中存在哪些用户，以及每个用户是否正处于OPEN状态。

在使用Oracle视图V$PWFILE_USERS之前，首先需要启用外部身份验证。使用ALTER SYSTEMSET 配置参数REMOTE_LOGIN_passwordfile = “EXCLUSIVE”：

SQL > ALTER SYSTEM SET REMOTE_LOGIN_passwordfile = “EXCLUSIVE”;

接着，可以使用以下SQL语句查询V$PWFILE_USERS视图：

SQL > SELECT * FROM V$PWFILE_USERS;

此查询语句将产生以下结果：

USERNAME STATUS
——— ——–
User1 OPEN
User2 OPEN
User3 OPEN

官方英文解释

V$PWFILE_USERS lists all users in the password file, and indicates whether the user has been granted the SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM privileges.

Column	Datatype	Description
`USERNAME`	`VARCHAR2(128)`	Name of the user that is contained in the password file
`SYSDBA`	`VARCHAR2(5)`	Indicates whether the user can connect with `SYSDBA` privileges (`TRUE`) or not (`FALSE`)
`SYSOPER`	`VARCHAR2(5)`	Indicates whether the user can connect with `SYSOPER` privileges (`TRUE`) or not (`FALSE`)
`SYSASM`	`VARCHAR2(5)`	Indicates whether the user can connect with `SYSASM` privileges (`TRUE`) or not (`FALSE`)
`SYSBACKUP`	`VARCHAR2(5)`	Indicates whether the user can connect with `SYSBACKUP` privileges (`TRUE`) or not (`FALSE`)
`SYSDG`	`VARCHAR2(5)`	Indicates whether the user can connect with `SYSDG` privileges (`TRUE`) or not (`FALSE`)
`SYSKM`	`VARCHAR2(5)`	Indicates whether the user can connect with `SYSKM` privileges (`TRUE`) or not (`FALSE`)
`ACCOUNT_STATUS`	`VARCHAR2(30)`	Account status: `OPEN` `EXPIRED` `EXPIRED (GRACE)` `LOCKED (TIMED)` `LOCKED` `EXPIRED & LOCKED (TIMED)` `EXPIRED & LOCKED` `EXPIRED (GRACE) & LOCKED`
`PASSWORD_PROFILE`	`VARCHAR2(128)`	Password profile name
`LAST_LOGIN`	`TIMESTAMP(9) WITH TIME ZONE`	The time of the last user login
`LOCK_DATE`	`DATE`	Date the account was locked if account status was `LOCKED`
`EXPIRY_DATE`	`DATE`	Date of expiration of the account
`EXTERNAL_NAME`	`VARCHAR2(1024)`	Shows Certificate DN or Principal Name of externally authenticated users
`AUTHENTICATION_TYPE`	`VARCHAR2(8)`	Indicates the authentication mechanism for the user: `EXTERNAL` – `CREATE` `USER` `user1` `IDENTIFIED` `EXTERNALLY;` `GLOBAL` – `CREATE` `USER` `user2` `IDENTIFIED` `GLOBALLY;` `PASSWORD` – `CREATE` `USER` `user3` `IDENTIFIED` `BY` `password;`
`COMMON`	`VARCHAR2(3)`	This column has a value of `YES` if an administrative privilege (for example, `SYSDBA`) was granted with `CONTAINER=ALL`. Otherwise, the column has a value of `NO`.
`PASSWORD_VERSIONS`^Foot 1	`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: `10G`: If an old case-insensitive ORCL hash exists `11G`: If a SHA-1 hash exists `12C`: If a de-optimized PBKDF2-based hash 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.
`CON_ID`	`NUMBER`	The ID of the container to which the data pertains. Possible values include: `0`: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. `1`: This value is used for rows containing data that pertain to only the root n: Where n is the applicable container ID for the rows containing data

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