Oracle教程

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_VERSIONSFoot 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 1 This column is available starting with Oracle Database 21c.


数据运维技术 » Oracle 视图 V$PWFILE_USERS 官方解释，作用，如何使用详细说明
分享到：

相关推荐