Lesson 10	Password files and the data dictionary
Objective	List the administrative users in the `COIN` database.

Listing Oracle Password File Users with V$PWFILE_USERS

The previous lessons created the password file, configured REMOTE_LOGIN_PASSWORDFILE, granted administrative privileges, and connected to the COIN database as SYSDBA. This lesson completes the verification workflow by showing how to list the administrative users that are represented in the password file.

Oracle Database 23ai supports password file authentication for privileged administrative users. The dynamic performance view V$PWFILE_USERS shows which users are represented in the password file and which administrative privileges those users have been granted. This view is the practical DBA view for checking password-file administrative access.

The core question for this lesson is simple:

Which users can authenticate through the password file with administrative privileges?

The answer is found by querying V$PWFILE_USERS.

What V$PWFILE_USERS Shows

V$PWFILE_USERS is a dynamic performance view that lists users who have password-file administrative privileges. In older lessons, this view may have been described broadly as a data dictionary view. More precisely, it is a dynamic performance view exposed through the V$ family of views.

The view identifies whether a password-file user has administrative privileges such as:

SYSDBA
SYSOPER
SYSASM
SYSBACKUP
SYSDG
SYSKM

Earlier Oracle examples often focused only on SYSDBA and SYSOPER. Modern Oracle administration uses a more detailed separation of duties. For example, a backup administrator may need SYSBACKUP, a Data Guard administrator may need SYSDG, and a key management administrator may need SYSKM.

Connect as a Privileged User Before Querying

V$PWFILE_USERS is not normally queried by ordinary application users. Connect as a sufficiently privileged administrative user before querying it. From the database server, a local DBA may connect with operating system authentication:

SQL> CONNECT / AS SYSDBA
Connected.

A remote DBA can connect to the COIN database through the Oracle Net service name and allow SQL*Plus to prompt for the password:

SQL> CONNECT admin_user@coin AS SYSDBA
Enter password:
Connected.

Do not use older inline password examples such as system/manager as the recommended pattern. Inline passwords can be exposed in terminal history, screenshots, scripts, logs, and copied documentation.

Listing Administrative Users in the COIN Database

The simplest query is:

SELECT *
FROM V$PWFILE_USERS;

For teaching and review, it is better to select the most relevant columns explicitly:

SELECT USERNAME, SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, SYSKM, CON_ID
FROM V$PWFILE_USERS
ORDER BY USERNAME;

A modernized example result could look like this:

USERNAME       SYSDBA  SYSOPER  SYSASM  SYSBACKUP  SYSDG  SYSKM  CON_ID
------------- ------- -------- ------- ---------- ------ ------ ------
SYS           TRUE    TRUE     FALSE   FALSE      FALSE  FALSE      0
ADMIN_USER    TRUE    FALSE    FALSE   FALSE      FALSE  FALSE      0
BACKUP_ADMIN  FALSE   FALSE    FALSE   TRUE       FALSE  FALSE      0
DG_ADMIN      FALSE   FALSE    FALSE   FALSE      TRUE   FALSE      0
KEY_ADMIN     FALSE   FALSE    FALSE   FALSE      FALSE  TRUE       0

The TRUE and FALSE values show which administrative privileges are available to each password-file user. If ADMIN_USER shows TRUE under SYSDBA, then that user can connect AS SYSDBA, assuming the password file and remote login configuration are valid.

Modern Oracle 23ai Columns

The most important columns for this lesson are USERNAME, SYSDBA, and SYSOPER. In Oracle 23ai, however, the view may expose additional administrative privilege and account-state information, depending on the release, password file format, and environment.

Commonly useful columns include:

USERNAME: the database user represented in the password file.
SYSDBA: TRUE if the user can connect with SYSDBA authority.
SYSOPER: TRUE if the user can connect with SYSOPER authority.
SYSASM: TRUE if the user has ASM administrative authority.
SYSBACKUP: TRUE if the user has backup administrative authority.
SYSDG: TRUE if the user has Data Guard administrative authority.
SYSKM: TRUE if the user has key management administrative authority.
CON_ID: identifies the container context in a multitenant environment.

Some environments may also expose account-status and profile-related columns. If you use those columns in a script, test the script against the exact Oracle release and password file format used by the database.

SELECT USERNAME,
       SYSDBA,
       SYSOPER,
       SYSBACKUP,
       SYSDG,
       SYSKM,
       ACCOUNT_STATUS,
       PASSWORD_PROFILE,
       LAST_LOGIN,
       CON_ID
FROM V$PWFILE_USERS
ORDER BY USERNAME;

If a column is not available in a particular database release or password file format, simplify the query to the core administrative privilege columns.

Understanding the Result Set

A password file result set should be read as a security inventory. Each row represents an account that can potentially authenticate through the password file with an elevated administrative privilege.

In the example output:

SYS has core administrative authority.
ADMIN_USER has SYSDBA.
BACKUP_ADMIN has SYSBACKUP.
DG_ADMIN has SYSDG.
KEY_ADMIN has SYSKM.

The SYSTEM user appears in V$PWFILE_USERS only if it has been explicitly granted a password-file administrative privilege such as SYSDBA or SYSOPER. The DBA role alone does not place a user in the password file and does not allow a user to connect AS SYSDBA.

Historical Note: INTERNAL and Truncated Headings

Older Oracle examples sometimes displayed an INTERNAL row in password file output. Do not treat INTERNAL as a current Oracle 23ai administrative account. The modern administrative connection model uses privileges such as SYSDBA and SYSOPER.

Older tools also displayed shortened column headings such as SYSDB and SYSOP. Those were truncated display headings for SYSDBA and SYSOPER. Modern queries should select and format columns so that the privilege names remain clear.

A legacy-style output such as this should be interpreted historically, not copied as the current model:

USERNAME                      SYSDB SYSOP
------------------------------ ----- -----
INTERNAL                       TRUE  TRUE
SYS                            TRUE  TRUE
SYSTEM                         TRUE  FALSE

In Oracle 23ai, the rewritten lesson should focus on named administrative users and explicit administrative privileges, not on INTERNAL.

How Grants Appear in V$PWFILE_USERS

When a user is granted a password-file administrative privilege, the grant becomes visible through V$PWFILE_USERS. For example:

GRANT SYSDBA TO admin_user;

SELECT USERNAME, SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM
FROM V$PWFILE_USERS
WHERE USERNAME = 'ADMIN_USER';

If the grant is active, the result should show SYSDBA = TRUE for ADMIN_USER.

A separate SYSOPER grant appears independently:

GRANT SYSOPER TO admin_user;

SELECT USERNAME, SYSDBA, SYSOPER
FROM V$PWFILE_USERS
WHERE USERNAME = 'ADMIN_USER';

This distinction matters because SYSDBA and SYSOPER are not ordinary roles and are not automatically implied by the DBA role.

Password File Information View

V$PWFILE_USERS tells you who is represented in the password file. V$PASSWORDFILE_INFO tells you about the password file itself. Use it to check the file name and format:

SELECT FILE_NAME, FORMAT
FROM V$PASSWORDFILE_INFO;

This view complements V$PWFILE_USERS. For a modern Oracle 23ai environment, the password file should use a modern format when possible, especially if the environment depends on newer administrative account behavior.

REMOTE_LOGIN_PASSWORDFILE and EXCLUSIVE Mode

A value of EXCLUSIVE for REMOTE_LOGIN_PASSWORDFILE binds the password file to a database and allows named administrative users to exist in the password file. This is the normal recommended setting for lessons that teach named administrative accounts and password-file authentication.

SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE

If a shared password file must be used, protect the SYS password with a strong password policy and understand the limitations of shared password file administration. In most modern instructional examples, use EXCLUSIVE because it supports named administrative accounts more clearly.

Security and Auditing Meaning

V$PWFILE_USERS should be part of a DBA security review because it shows which accounts can authenticate through the password file with elevated administrative privileges. A DBA should review this view periodically and remove unnecessary grants.

Use the view to answer questions such as:

Who can connect AS SYSDBA?
Who can connect AS SYSOPER?
Who has backup, Data Guard, or key management administrative authority?
Does the password file contain only named users who still require those privileges?
Does the password file support the format and administrative controls expected for the database?

Good administrative practice includes:

Use named administrative accounts instead of shared credentials.
Grant only the administrative privilege required for the task.
Review V$PWFILE_USERS regularly.
Use Oracle Unified Auditing where appropriate to monitor privileged logins and actions.
Verify password file metadata with V$PASSWORDFILE_INFO.
Use REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE when named password-file users are required.

Summary

The objective of this lesson is to list the administrative users in the COIN database. In Oracle 23ai, the primary view for this task is V$PWFILE_USERS. This dynamic performance view lists users represented in the password file and shows their administrative privileges.

The recommended query is:

SELECT USERNAME, SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, SYSKM, CON_ID
FROM V$PWFILE_USERS
ORDER BY USERNAME;

The result should be treated as a privileged-access inventory. Any user with SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM has elevated administrative authority and should be reviewed as part of the database security model.