Configuring Oracle to Use a Password File

After a password file has been created for an Oracle database, the database must also be configured to use that file for remote privileged authentication. The initialization parameter that controls this behavior is REMOTE_LOGIN_PASSWORDFILE. This parameter tells Oracle whether it should check a password file when a privileged administrator connects remotely as SYSDBA, SYSOPER, or another administrative user.

This lesson continues the password file workflow from the previous lesson. Lesson 5 focused on creating the password file for the COIN database with the orapwd utility. This lesson explains how to configure Oracle Database 23ai to use that password file. The key setting is:

REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

The password file may exist on disk, but Oracle still needs to know whether password file authentication is enabled. That decision is controlled by REMOTE_LOGIN_PASSWORDFILE. For most modern databases that require remote privileged administration, the recommended value is EXCLUSIVE.

What REMOTE_LOGIN_PASSWORDFILE Controls

The REMOTE_LOGIN_PASSWORDFILE parameter controls whether Oracle checks a password file for remote administrative logins. Ordinary user connections are authenticated through normal database authentication mechanisms. Privileged administrative connections are different because they may be needed when the database is mounted, being started, being shut down, being recovered, or being administered remotely.

For example, a local DBA may be able to connect from the database server with operating system authentication:

sqlplus / as sysdba

A remote DBA connecting over Oracle Net cannot rely on that same local operating system authentication path. A remote privileged connection normally needs a password file, a valid administrative privilege, and a compatible REMOTE_LOGIN_PASSWORDFILE setting.

The password file keeps track of database user names that have been granted administrative privileges such as SYSDBA and SYSOPER. In modern Oracle environments, it may also support other administrative privileges, including SYSBACKUP, SYSDG, SYSKM, and SYSASM, depending on the database architecture and password file format.

Parameter Values

The REMOTE_LOGIN_PASSWORDFILE parameter has three values:

• EXCLUSIVE
• SHARED
• NONE

These values do not create the password file. The password file is created with orapwd. The parameter tells Oracle whether to use the password file and how it should treat it.

EXCLUSIVE

The EXCLUSIVE setting tells Oracle that one database uses the password file. This is the normal recommended setting for modern database administration because it allows the password file to contain named administrative users in addition to SYS.

With EXCLUSIVE, a DBA can grant administrative privileges to named users. For example, a backup administrator can be granted SYSBACKUP, while a Data Guard administrator can be granted SYSDG. This is better than forcing all administrators to share the SYS password.

For the COIN database, use EXCLUSIVE unless a specific architecture requires another setting.

SHARED

The SHARED setting allows one password file to be shared by more than one database. This option exists for specialized or legacy configurations, but it is not usually the best choice for a new Oracle 23ai instructional database.

The important limitation is that a shared password file restricts password file maintenance. If the password file is shared, Oracle cannot freely update it for one database without affecting the sharing arrangement. This can interfere with administrative user changes, password changes, and modern privilege separation.

Use SHARED only when you have a clear reason to share one password file across multiple databases and understand the operational limits.

NONE

The NONE setting disables password file authentication. If REMOTE_LOGIN_PASSWORDFILE is set to NONE, then Oracle does not use the password file for remote privileged authentication.

This setting may be appropriate for tightly controlled systems that do not allow remote administrative authentication through a password file. However, it is not appropriate for a database that requires remote SYSDBA, SYSOPER, SYSBACKUP, or similar administrative connections.

Recommended Setting for the COIN Database

For the COIN database in this lesson, the recommended setting is:

REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

This setting supports the normal Oracle DBA workflow:

1. Create the password file with orapwd.
2. Set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE.
3. Restart the database instance if the parameter was changed in the SPFILE or initialization file.
4. Grant administrative privileges to named users as needed.
5. Verify the parameter and password file users with SQL queries.

The important point is that EXCLUSIVE supports named administrative accounts. Named accounts improve auditability and reduce dependence on shared use of the SYS account.

Configuring the Parameter with an SPFILE

Modern Oracle databases commonly use an SPFILE rather than a manually edited init.ora file. The SPFILE is a server parameter file that Oracle reads during startup. If the database is using an SPFILE, use ALTER SYSTEM to set the parameter.

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

The SCOPE=SPFILE clause means that the change is written to the SPFILE and becomes effective after the instance is restarted. This parameter is not dynamically modifiable for a running instance, so a restart is required.

SHUTDOWN IMMEDIATE;
STARTUP;

After restart, Oracle rereads the SPFILE and applies the configured REMOTE_LOGIN_PASSWORDFILE value.

Configuring the Parameter with init.ora

Some older databases, training environments, or manually managed installations may still use a text initialization parameter file named init.ora. In that case, the parameter can be placed directly in the initialization file:

REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

After editing the text initialization file, restart the instance so the file is reread:

SHUTDOWN IMMEDIATE;
STARTUP;

In Oracle 23ai administration, the SPFILE approach is more common, but DBAs still need to recognize the legacy init.ora syntax because older course material, migration notes, and troubleshooting documentation may refer to it.

Checking the Current Parameter Value

Before changing the parameter, check the current value:

SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE

A typical result should show the parameter name, type, and value. If the value is EXCLUSIVE, then the database is configured to use an exclusive password file. If the value is NONE, password file authentication is disabled. If the value is SHARED, the database is using a shared password file configuration.

Verifying Password File Users

After the password file exists and the parameter is configured, use V$PWFILE_USERS to inspect which users are represented in the password file and which administrative privileges they have.

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

This query helps confirm whether administrative users have been granted the expected privileges. For example, a user granted SYSBACKUP should appear with the SYSBACKUP column marked appropriately. A user granted SYSDBA should appear with SYSDBA marked.

If an expected user does not appear in V$PWFILE_USERS, verify that the administrative privilege was actually granted and that REMOTE_LOGIN_PASSWORDFILE is not set to NONE or an unsuitable shared configuration.

Checking Password File Information

Oracle also provides V$PASSWORDFILE_INFO, which reports information about the password file itself. This view is useful for checking the file name and format.

SELECT FILE_NAME, FORMAT
FROM V$PASSWORDFILE_INFO;

The FORMAT value may show values such as LEGACY, 12, or 12.2. For a modern Oracle 23ai deployment, a 12.2 format password file is preferred when supported by the environment and compatibility requirements.

SYSDBA and SYSOPER Authentication

Oracle Database uses database-specific password files to keep track of users who have been granted powerful administrative privileges. Two of the most important privileges are SYSDBA and SYSOPER.

1. SYSOPER allows an administrator to perform operational tasks such as STARTUP, SHUTDOWN, ALTER DATABASE OPEN, ALTER DATABASE MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, and RECOVER. It also includes the RESTRICTED SESSION privilege.
2. SYSDBA is broader and more powerful. It includes the ability to perform the most privileged database administration operations, including database creation, recovery, and access to the database with the highest administrative authority.

Because these privileges are powerful, they should be granted carefully. A well-managed password file should not become a shortcut for shared administrative access. Instead, it should support named administrative users whose actions can be audited and reviewed.

Relationship Between ORAPWD and REMOTE_LOGIN_PASSWORDFILE

The orapwd utility creates the password file. The REMOTE_LOGIN_PASSWORDFILE parameter tells Oracle whether to use it. These are related but separate tasks.

A simplified workflow is:

1. Use orapwd to create the password file.
2. Set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE.
3. Restart the database if the parameter was changed.
4. Grant administrative privileges to named users.
5. Verify the configuration with SHOW PARAMETER, V$PWFILE_USERS, and V$PASSWORDFILE_INFO.

Do not confuse orapwd with ORADIM. ORADIM is a Windows utility used to create, modify, start, and stop Oracle instances and services on Windows. It does not replace orapwd, and it is not the central utility for password file creation.

Oracle Multitenant Context

In a multitenant database, the password file is associated with the container database, or CDB. Pluggable databases, or PDBs, depend on the CDB-level administrative infrastructure for remote privileged authentication.

This means that password file planning should be handled at the CDB level. A DBA should not think of each PDB as having an independently managed operating system password file. The CDB owns the password file context, and administrative authentication must be understood from that level.

This is especially important when teaching Oracle 23ai. Multitenant architecture is no longer an optional side topic. It is part of the normal Oracle database architecture, and the password file should be explained in that context.

Oracle Cloud Context

For standard Oracle databases on Oracle Cloud Infrastructure, including manually managed databases and Database Cloud Service deployments, REMOTE_LOGIN_PASSWORDFILE remains relevant when remote privileged administration is required. The DBA may still need to understand password files, administrative privileges, and remote authentication behavior.

Autonomous Database is different. In Autonomous Database, Oracle manages much of the underlying database administration. Wallet-based connectivity, Identity and Access Management integration, and cloud service controls reduce the need for DBAs to manage password files directly. Do not assume that an Autonomous Database administrator performs the same operating system password file tasks used in a manually managed database.

Security Risks of Password Files

A password file is part of the database security boundary. Anyone who can read, copy, replace, or tamper with the file may be able to interfere with privileged authentication. For that reason, the file must be protected with operating system permissions and strong administrative procedures.

Common password file security risks include:

1. An intruder could steal, copy, or attack the password file.
2. A weak administrative password could be guessed.
3. A password found in a dictionary could be vulnerable to attack.
4. A short password could be easier to crack if an attacker obtains a password hash.
5. Shared use of the SYS account could make accountability difficult.
6. Improper file permissions could expose the password file to unauthorized operating system users.

The solution is not merely to create the password file. The DBA must also control access to it, use strong administrative passwords, avoid shared credentials, and audit privileged activity.

Best Practices for Oracle 23ai

Use the following best practices when configuring password file authentication in Oracle 23ai:

1. Use REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE for normal named administrative user management.
2. Create the password file with orapwd, not ORADIM.
3. Use a modern password file format, such as FORMAT=12.2, when creating a new Oracle 23ai password file.
4. Use named administrative users instead of sharing the SYS password.
5. Restrict password file permissions at the operating system level.
6. Avoid placing administrative passwords directly on command lines.
7. Verify users with V$PWFILE_USERS.
8. Verify password file information with V$PASSWORDFILE_INFO.
9. Use Oracle Unified Auditing where appropriate to review privileged administrative actions.

Common Configuration Errors

Password File Missing

If the password file was never created, remote administrative authentication can fail even if REMOTE_LOGIN_PASSWORDFILE is set correctly. Create the password file with orapwd and place it in the expected operating system location.

Parameter Set to NONE

If REMOTE_LOGIN_PASSWORDFILE is set to NONE, Oracle does not use the password file. Remote privileged authentication through the password file is disabled.

Database Not Restarted

If the parameter was changed in the SPFILE or initialization file, the database instance must be restarted before the new setting takes effect.

Wrong File Location

The password file must be in the location expected by the Oracle instance. On Linux and Unix systems, this is commonly under $ORACLE_HOME/dbs. On Windows systems, it is commonly under %ORACLE_HOME%\database. If the file is created in the wrong directory, Oracle may not find it.

Confusing ORADIM with ORAPWD

ORADIM manages Oracle services and instances on Windows. orapwd creates and manages the password file. A password file lesson should center on orapwd and REMOTE_LOGIN_PASSWORDFILE, not ORADIM.

Summary

Once a password file has been created for the COIN database, Oracle must be configured to use it. The parameter that controls this behavior is REMOTE_LOGIN_PASSWORDFILE. For a normal Oracle 23ai database that requires remote privileged administration, the recommended setting is EXCLUSIVE.

Use the SPFILE method for modern administration:

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;
STARTUP;

Then verify the configuration:

SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE

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

SELECT FILE_NAME, FORMAT
FROM V$PASSWORDFILE_INFO;

The final concept is simple: orapwd creates the password file, and REMOTE_LOGIN_PASSWORDFILE tells Oracle to use it. Together, they support secure remote administrative authentication for Oracle Database 23ai.

Remote Login Password File - Exercise