Lesson 12

This module explained how Oracle password files support privileged administrative authentication in Oracle Database 23ai. A password file allows Oracle to authenticate users who connect with administrative privileges such as SYSDBA, SYSOPER, SYSBACKUP, SYSDG, and SYSKM. These privileges are needed for operations that may occur before the database is fully open, including startup, shutdown, mount, recovery, backup administration, Data Guard administration, and key management.

The central idea is that ordinary database authentication depends on the database being available. A password file exists outside the data dictionary, so it can support privileged authentication even when the database is not open. This solves the administrative bootstrap problem: a DBA may need to connect as SYSDBA in order to start or recover a database, but the database cannot authenticate ordinary users until it is already open.

Across the module, the password-file workflow followed this sequence:

1. Explain why password files are necessary for remote privileged authentication.
2. Identify the correct location and name for the password file.
3. Create the password file with the orapwd utility.
4. Configure REMOTE_LOGIN_PASSWORDFILE so Oracle can use the password file.
5. Connect to the database with AS SYSDBA or AS SYSOPER.
6. Grant administrative privileges to named DBA accounts.
7. Review password-file users with V$PWFILE_USERS.
8. Inspect password file metadata with V$PASSWORDFILE_INFO.
9. Rebuild the password file when it must be repaired, resized, reformatted, relocated, or synchronized.

An Oracle password file is a privileged authentication file. It is not a normal application data file, and it is not a replacement for the Oracle data dictionary. Its purpose is narrower and more sensitive: it stores authentication information for users who have been granted administrative connection privileges.

When a user connects normally, Oracle authenticates that user through the configured database authentication path. When a user connects AS SYSDBA or AS SYSOPER over the network, Oracle may need the password file. This is especially important when the database is in NOMOUNT, MOUNT, recovery, or shutdown-related states.

The password file is created with orapwd. In modern Oracle 23ai examples, the preferred approach is to avoid placing the password on the operating system command line and allow orapwd to prompt interactively. A typical file-system example is:

orapwd FILE=$ORACLE_HOME/dbs/orapwCOIN ENTRIES=10 FORMAT=12.2

The FILE parameter identifies the password file location, ENTRIES reserves capacity for administrative users, and FORMAT=12.2 represents a modern password file format when compatibility allows.

Creating a password file is not enough. Oracle must also be configured to use it. The initialization parameter REMOTE_LOGIN_PASSWORDFILE controls whether Oracle uses password file authentication for remote privileged connections.

The normal instructional setting for named administrative accounts is:

REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

The parameter has three major values:

1. EXCLUSIVE: Oracle uses an exclusive password file that can contain named administrative users. This is the normal setting for modern single-database teaching examples.
2. SHARED: Oracle uses a shared password file in specialized configurations, but this setting has limitations and is not the normal teaching baseline.
3. NONE: Oracle ignores the password file, disabling password-file-based remote privileged authentication.

In a database that uses an SPFILE, the parameter can be set with:

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

A restart may be required for the change to take effect.

This module modernized older INTERNAL connection examples into the current Oracle administrative model. In Oracle 23ai, a DBA does not use CONNECT INTERNAL. Instead, the DBA connects using an administrative privilege such as SYSDBA or SYSOPER.

A local DBA may connect through operating system authentication:

sqlplus / AS SYSDBA

A remote DBA can connect through an Oracle Net service name:

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

The AS SYSDBA clause changes the connection type. Without that clause, the user receives a normal database session:

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

That session may have normal database privileges, but it is not a SYSDBA administrative session.

Password files are most useful when they support named administrative accounts. Instead of forcing every DBA to share SYS, Oracle allows selected users to receive explicit administrative privileges.

Examples include:

GRANT SYSDBA TO admin_user;
GRANT SYSOPER TO ops_user;
GRANT SYSBACKUP TO backup_admin;
GRANT SYSDG TO dg_admin;
GRANT SYSKM TO key_admin;

These are not ordinary roles. They are special administrative privileges that affect how a user can connect and what operations the user can perform. The DBA role does not automatically include SYSDBA or SYSOPER. Those privileges must be granted explicitly.

This distinction supports least privilege. A backup administrator may need SYSBACKUP, a Data Guard administrator may need SYSDG, and a key management administrator may need SYSKM. Not every administrator should receive full SYSDBA authority.

V$PWFILE_USERS is the primary view for reviewing password-file administrative users. It lists users represented in the password file and identifies which administrative privileges each user has been granted.

A useful review query is:

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

Column	Description
USERNAME	Name of the user represented in the password file.
SYSDBA	If TRUE, the user can connect with SYSDBA administrative authority.
SYSOPER	If TRUE, the user can connect with SYSOPER administrative authority.
SYSASM	If TRUE, the user has ASM administrative authority.
SYSBACKUP	If TRUE, the user has backup administrative authority.
SYSDG	If TRUE, the user has Data Guard administrative authority.
SYSKM	If TRUE, the user has key management administrative authority.
CON_ID	Identifies the container context in a multitenant environment.

This view should be part of a DBA security review. Any account listed with elevated administrative authority should still have a valid operational reason for that access.

V$PWFILE_USERS answers the question "who has password-file administrative privileges?" The companion view V$PASSWORDFILE_INFO answers a different question: "where is the password file and what format is it using?"

SELECT FILE_NAME, FORMAT
FROM V$PASSWORDFILE_INFO;

This view is useful when verifying a newly created password file, troubleshooting failed administrative connections, preparing a password file rebuild, or checking whether the password file uses a modern format such as 12.2.

Rebuilding a password file is a controlled maintenance procedure. It may be needed when the file is missing, corrupted, too small, in the wrong format, inconsistent across environments, or no longer aligned with the required administrative users.

The most important rule is to record current password-file users before replacing the file:

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

Also record the file metadata:

SELECT FILE_NAME, FORMAT
FROM V$PASSWORDFILE_INFO;

A controlled file-system rebuild for a training database may follow this pattern:

SQL> CONNECT / AS SYSDBA
Connected.

SHUTDOWN IMMEDIATE;

cd $ORACLE_HOME/dbs

mv orapwCOIN orapwCOIN.bak

orapwd FILE=$ORACLE_HOME/dbs/orapwCOIN FORMAT=12.2 ENTRIES=100 FORCE=Y

STARTUP;

GRANT SYSDBA TO admin_user;
GRANT SYSOPER TO ops_user;
GRANT SYSBACKUP TO backup_admin;

After the rebuild, verify the file with V$PWFILE_USERS, verify metadata with V$PASSWORDFILE_INFO, and test a prompted SYSDBA connection.

Password-file administration is security-sensitive because it controls privileged access to the database instance. A DBA should apply these principles:

1. Use named administrative accounts instead of shared use of SYS or SYSTEM.
2. Grant only the administrative privilege required for the task.
3. Use prompted password entry rather than inline passwords on the command line.
4. Protect the password file with operating system or ASM-level permissions.
5. Use a modern password file format when compatibility allows.
6. Review V$PWFILE_USERS regularly.
7. Use Oracle Unified Auditing or other approved controls to monitor privileged connections.
8. Coordinate password file changes with Data Guard, RAC, ASM, backup automation, and operational monitoring.

The password file is not merely a setup artifact. It is part of the database security boundary.

Oracle 23ai keeps the password-file concepts familiar while encouraging stronger administrative discipline. The essential tools and views remain:

• orapwd creates and manages the password file.
• REMOTE_LOGIN_PASSWORDFILE controls whether Oracle uses the password file.
• V$PWFILE_USERS lists password-file administrative users.
• V$PASSWORDFILE_INFO reports password file metadata.
• AS SYSDBA and AS SYSOPER request privileged administrative connections.

In Oracle Cloud, RAC, ASM, Data Guard, or managed environments, additional platform controls may affect how password files are stored, synchronized, or administered. The core principle remains the same: privileged administrative authentication must be protected, verified, and audited.

This module introduced the following terms:

1. Password file: A privileged authentication file used to authenticate administrative users outside ordinary database authentication.
2. orapwd: The Oracle utility used to create and manage password files.
3. SYSDBA: A special administrative privilege that gives full database instance authority.
4. SYSOPER: A special administrative privilege for operational tasks such as startup, shutdown, mount, open, archive, and complete recovery.
5. SYSBACKUP: A special administrative privilege for backup and recovery administration.
6. SYSDG: A special administrative privilege for Data Guard administration.
7. SYSKM: A special administrative privilege for key management administration.
8. REMOTE_LOGIN_PASSWORDFILE: The initialization parameter that controls whether Oracle uses a password file for remote privileged authentication.
9. V$PWFILE_USERS: The dynamic performance view that lists password-file users and their administrative privileges.
10. V$PASSWORDFILE_INFO: The dynamic performance view that reports password file metadata such as file name and format.

You should now understand how Oracle password files fit into database administration. They allow privileged users to connect remotely when the database is not fully open, support named administrative accounts, enable separation of duties, and provide a reviewable registry of elevated access.

The modern Oracle 23ai pattern is clear:

orapwd FILE=$ORACLE_HOME/dbs/orapwCOIN FORMAT=12.2 ENTRIES=10

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

GRANT SYSDBA TO admin_user;

CONNECT admin_user@coin AS SYSDBA;

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

This pattern creates the password file, enables it, grants administrative authority to a named user, uses a privileged connection, and verifies the result. A DBA who understands this sequence can manage password-file authentication safely in training, on-premises, cloud, RAC, ASM, and Data Guard environments.