Lesson 1

Oracle Password File Management (orapwd, SYSDBA, and SYSOPER Explained)

This module is about Oracle password files. Password files provide a mechanism for authenticating database administrators who connect remotely over a network. Without a password file, Oracle cannot verify the identity of a DBA attempting to connect with SYSDBA or SYSOPER privileges through Oracle Net Services — the connection is rejected before it reaches the database.

When you have completed this module, you should be able to do the following:

Create a new password file using the orapwd utility
Connect to a database as SYSDBA or SYSOPER
Grant SYSDBA and SYSOPER privileges to DBAs
Configure a database to use a password file using REMOTE_LOGIN_PASSWORDFILE
Rebuild a password file when it is corrupted or lost
Select from V$PWFILE_USERS to see who has SYSDBA or SYSOPER privileges
Grant SYSBACKUP privileges for RMAN backup operations (Oracle 12c+)
Grant SYSDG privileges for Oracle Data Guard operations (Oracle 12c+)
Grant SYSKM privileges for Transparent Data Encryption key management (Oracle 12c+)
Grant SYSRAC privileges for Oracle RAC cluster operations (Oracle 12c+)

Password files play an important role in database security, especially when you are using tools such as Oracle Enterprise Manager Cloud Control to remotely manage a database over a network. This module covers password file creation, privilege management, error diagnosis, and the shared password file architecture introduced in Oracle 12.2 for Real Application Clusters environments.

What Is an Oracle Password File

An Oracle password file is a binary file stored on the database server that contains the encrypted credentials of users who have been granted SYSDBA or SYSOPER administrative privileges. It exists independently of the data dictionary — which means Oracle can authenticate these privileged users even when the database is not open, such as during startup, shutdown, or recovery operations when the data dictionary is unavailable.

This independence from the data dictionary is the defining characteristic of password file authentication. A DBA connecting as SYSDBA to start a closed database cannot be authenticated against the data dictionary because the data dictionary lives inside the database being started. The password file provides the authentication mechanism for exactly this scenario.

Password File Location by Platform

The default password file location depends on the operating system and Oracle version:

Linux and Unix: $ORACLE_HOME/dbs/orapw<SID> — for example, /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwORCL
Windows: %ORACLE_HOME%\database\PWD<SID>.ora — for example, C:\app\oracle\product\19.0.0\dbhome_1\database\PWDORCL.ora
Oracle 12.2+ with ASM: The password file can be stored in an Oracle ASM disk group, enabling a single shared password file for all nodes in a Real Application Clusters environment. The ASM path takes the form +DATA/orapwORCL.

In Oracle 23ai, the password file remains supported in all three locations. The location is also configurable through the db_files and ASM configuration parameters when deploying on Oracle Cloud Infrastructure.

The REMOTE_LOGIN_PASSWORDFILE Initialization Parameter

The REMOTE_LOGIN_PASSWORDFILE initialization parameter controls how Oracle uses the password file. It accepts three values:

NONE — Oracle ignores any password file. Remote privileged connections are not permitted. Attempting to grant SYSDBA with this setting produces ORA-01994.
EXCLUSIVE — The password file is used exclusively by one database. Multiple users can be listed in the password file. This is the correct setting for most single-instance databases and for the primary database in a Data Guard configuration.
SHARED — The password file can be shared by multiple databases. Only the SYS password is stored. This value is deprecated in Oracle 12c in favor of the ASM-based shared password file approach.

To check the current setting:

SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;

To set it in the SPFILE:

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE SCOPE = SPFILE;

A restart is required for the parameter change to take effect. The recommended setting for a single-instance production database is EXCLUSIVE.

Oracle Managed Files (OMF) — Brief Context

Oracle Managed Files (OMF) simplifies database file management by having Oracle handle all operating system operations and file naming automatically. While OMF is primarily a database file management feature rather than a password file feature, it is relevant to this module because password files created using OMF conventions benefit from the same automatic lifecycle management. Key advantages of OMF include:

Automatic cleanup of the filesystem when database objects such as tablespaces and redo log members are dropped.
Standardized, Oracle-controlled naming of database files, eliminating the need for DBAs to manage file naming conventions manually.

OMF is configured using the DB_CREATE_FILE_DEST initialization parameter and is fully compatible with Oracle ASM. The remainder of this module focuses on password file creation and privilege management.

Creating a Password File with orapwd

The orapwd command-line utility creates the Oracle password file and sets the initial password for the SYS user. It must be run from the operating system command prompt — not from within SQL*Plus — by a user with appropriate OS-level permissions, typically the oracle OS user on Linux or a member of the ORA_DBA Windows group.

By default, the user SYS is the only user that has SYSDBA and SYSOPER privileges stored in the password file. Creating a password file by means of orapwd enables remote users to connect with administrative privileges through Oracle Net Services (formerly known as SQL*Net in Oracle 7 and 8 environments).

Warning: Using orapwd and granting SYSDBA gives other users Oracle superuser privileges. The SYSDBA privilege provides unrestricted access to every object in every schema, the ability to start and stop the database, and the ability to perform all recovery operations. Grant it only to users who require full DBA authority.

orapwd Syntax and Parameters

The modernized syntax for Oracle 19c and later:

orapwd FILE=$ORACLE_HOME/dbs/orapwORCL \
       PASSWORD=<sys_password> \
       ENTRIES=10 \
       FORMAT=12

Key parameters:

FILE — the full path and filename of the password file to create.
PASSWORD — the password for the SYS user. In Oracle 19c+, this must meet the password complexity requirements defined by the database profile.
ENTRIES — the maximum number of privileged users the password file can contain. In Oracle 12c+, this parameter is largely advisory — the password file grows dynamically — but it sets the initial allocation.
FORMAT — specifies the password file format. FORMAT=12 is required for case-sensitive password support and for storing the additional Oracle 12c+ administrative privileges (SYSBACKUP, SYSDG, SYSKM, SYSRAC). The legacy format 11 does not support these privileges.

On Windows, replace the forward slash with a backslash and use the Windows path convention:

orapwd FILE=%ORACLE_HOME%\database\PWDORCL.ora PASSWORD=<sys_password> ENTRIES=10 FORMAT=12

Case-Sensitive Passwords and FORMAT=12

Prior to Oracle 12c, Oracle passwords were stored and compared in a case-insensitive manner — the password Tiger was treated identically to TIGER. From Oracle 12c onward, SEC_CASE_SENSITIVE_LOGON=TRUE is the default, and passwords are case-sensitive. The password file format must match this security posture.

Using FORMAT=12 when running orapwd creates a password file that stores case-sensitive hashed credentials. If you create a password file with the legacy format on an Oracle 19c or 23ai database, privileged users may be unable to authenticate remotely because the stored hash does not match the case-sensitive credential they provide. Always use FORMAT=12 on Oracle 12c and later databases.

Administrative Privileges — SYSDBA and SYSOPER

Oracle provides two traditional administrative privilege levels and four additional specialized administrative privileges introduced in Oracle 12c. All are stored in the password file when REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE.

SYSDBA Privileges

The SYSDBA privilege is the highest level of database authority. It was previously granted by connecting as CONNECT INTERNAL in Oracle 7 and 8 — that syntax was deprecated in Oracle 9i and removed in Oracle 10g. All operations that previously required CONNECT INTERNAL are now performed by connecting as / AS SYSDBA.

SYSDBA provides:

Database startup and shutdown
CREATE DATABASE
ARCHIVELOG and NOARCHIVELOG mode changes
All RMAN backup and recovery operations
Unrestricted access to all data in all schemas
CREATE SPFILE
ALTER DATABASE OPEN, MOUNT, RESETLOGS, and FLASHBACK

When a user connects as SYSDBA, they connect into the SYS schema regardless of their own schema.

SYSOPER Privileges

The SYSOPER privilege allows a DBA to perform general database maintenance operations without the ability to view user data. This makes it appropriate for operators who need to start and stop the database or perform routine maintenance but should not have access to sensitive business data stored in user schemas.

SYSOPER provides:

Database startup and shutdown
ALTER DATABASE OPEN, MOUNT, BACKUP, and ARCHIVELOG
CREATE SPFILE
RESTRICTED SESSION
Recovery operations that do not require looking at user data

When a user connects as SYSOPER, they connect into the PUBLIC schema — not SYS — preventing access to the SYS-owned data dictionary tables.

Oracle 12c+ Administrative Privileges

Oracle 12c introduced four targeted administrative privileges that follow the principle of least privilege — granting only the authority needed for a specific administrative role rather than full SYSDBA access:

SYSBACKUP — grants the authority to perform RMAN backup and recovery operations. A backup operator can connect as SYSBACKUP and run all RMAN commands without having SYSDBA access to user data.
SYSDG — grants the authority to perform Oracle Data Guard operations including switchover, failover, and log transport management. The Oracle Data Guard Broker uses SYSDG for its administrative connections.
SYSKM — grants the authority to manage Transparent Data Encryption (TDE) keystores and master encryption keys. Separating key management authority from SYSDBA supports compliance requirements where the encryption key administrator must be distinct from the database administrator.
SYSRAC — grants the authority to perform Oracle Real Application Clusters management operations including instance registration and cluster interconnect configuration.

All four require FORMAT=12 in the password file. They are granted using the same GRANT syntax as SYSDBA and SYSOPER.

Granting Administrative Privileges

Granting SYSDBA and SYSOPER

Once the password file exists and REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE is set, you can grant SYSDBA and SYSOPER to database users. These grants must be issued by a user already connected as SYSDBA:

-- Grant SYSDBA to a DBA user
GRANT SYSDBA TO dba_user;

-- Grant SYSOPER to an operator user
GRANT SYSOPER TO ops_user;

-- Grant Oracle 12c+ targeted privileges
GRANT SYSBACKUP TO backup_operator;
GRANT SYSDG     TO dataguard_admin;
GRANT SYSKM     TO encryption_admin;

Each grant adds the user's credentials to the password file. The entries count set when orapwd was run controls the initial capacity, but in Oracle 12c+ the file grows dynamically as additional users are granted privileged access.

Viewing Privileged Users with V$PWFILE_USERS

The dynamic performance view V$PWFILE_USERS shows all users whose credentials are stored in the password file. It is valid in Oracle 19c, 21c, and 23ai. Query it as SYSDBA to audit which accounts hold administrative privileges:

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

Each column returns TRUE or FALSE indicating whether the user holds that specific privilege. SYS will always appear with SYSDBA=TRUE. Users granted SYSBACKUP or SYSDG will show TRUE in those columns only if the password file was created with FORMAT=12.

ORA-01994 — GRANT Failed

The most common error encountered when setting up password file authentication is ORA-01994. It occurs when orapwd has not yet been executed or when REMOTE_LOGIN_PASSWORDFILE is set to NONE:

SQL> GRANT SYSDBA TO scott;

ORA-01994: GRANT failed: cannot add users to public password file

Root Cause — REMOTE_LOGIN_PASSWORDFILE Misconfiguration

The specific root cause of ORA-01994 in the context of this module is that REMOTE_LOGIN_PASSWORDFILE is not set to EXCLUSIVE. Verify the current setting first:

SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;

If the value is NONE or SHARED, or if no password file exists at the expected location, the GRANT SYSDBA statement will fail with ORA-01994.

Diagnosis and Resolution

The general causes of ORA-01994 and their resolution steps in order of likelihood:

Password file does not exist: Run orapwd to create the password file at the correct location for your platform. Verify the file exists before attempting the grant.
REMOTE_LOGIN_PASSWORDFILE=NONE: Change the parameter to EXCLUSIVE in the SPFILE and restart the database instance.
Insufficient privileges: The user issuing the GRANT SYSDBA statement must themselves be connected as SYSDBA. A user with the standard DBA role cannot grant SYSDBA.
Syntax errors: Review the GRANT statement. The correct syntax is GRANT SYSDBA TO <username> — not GRANT SYSDBA ON or other variations.
Target user does not exist: Verify the grantee username exists in the database using SELECT USERNAME FROM DBA_USERS WHERE USERNAME = '<name>'.
Password file format mismatch: If you are granting SYSBACKUP, SYSDG, SYSKM, or SYSRAC and receiving ORA-01994, the password file may have been created with the legacy format. Recreate it using FORMAT=12.
Role hierarchy issues: Roles cannot be granted SYSDBA or SYSOPER — these privileges can only be granted directly to individual users. Attempting to grant them to a role produces ORA-01994.
Resource limitations: In older Oracle versions, if the password file was created with a low ENTRIES value and that limit has been reached, ORA-01994 may occur. Recreate the password file with a higher ENTRIES value or rely on Oracle 12c+ dynamic growth behavior.

After resolving the root cause, review the Oracle alert log for additional diagnostic messages. Oracle logs all privilege grant failures with contextual information that supplements the error code:

-- View the alert log location
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

Shared Password Files in Oracle RAC (12.2+)

In Oracle Real Application Clusters environments prior to Oracle 12.2, each node maintained its own copy of the password file. Keeping these files synchronized across nodes was a manual administrative task — if the files fell out of sync, privileged connections would succeed on some nodes and fail on others.

Oracle 12.2 introduced support for storing the password file in an Oracle ASM disk group, making it a single shared file accessible by all RAC nodes through the ASM infrastructure. The shared password file eliminates the synchronization problem entirely — any change to a privileged user's credentials or any new grant of SYSDBA is immediately visible across all nodes.

To migrate an existing password file to ASM:

-- Copy the existing password file to ASM using ASMCMD
ASMCMD> pwcopy --asm /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwORCL +DATA/orapwORCL

-- Update the initialization parameter to reference the ASM location
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE SCOPE = SPFILE;

The ASM-based shared password file requires FORMAT=12 and is the recommended configuration for all new Oracle RAC deployments on Oracle 12.2 and later, including Oracle 19c and 23ai.

In the next lesson, you will learn how to create a new Oracle password file using orapwd and configure the REMOTE_LOGIN_PASSWORDFILE parameter to enable privileged remote connections.