1.7 Creating and Maintaining a Database Password File

«« Previous
Next »»

You can create a database password file using the password file creation utility, ORAPWD. For some operating systems, you can create this file as part of your standard installation.

  • ORAPWD Syntax and Command Line Argument Descriptions

The ORAPWD command creates and maintains a password file.

The syntax of the ORAPWD command is as follows:

orapwd FILE=filename [ENTRIES=numusers] [FORCE={y|n}] [ASM={y|n}] 
[DBUNIQUENAME=dbname] [FORMAT={12|legacy}] [SYSBACKUP={y|n}] [SYSDG={y|n}] 
[SYSKM={y|n}] [DELETE={y|n}] [INPUT_FILE=input-fname]

orapwd DESCRIBE FILE=filename

Command arguments are summarized in the following table.

Argument Description
FILE If the DESCRIBE argument is not included, then specify the name to assign to the new password file. You must supply a complete path. If you supply only a file name, the file is written to the current directory.
If the DESCRIBE argument is included, then specify the name of an existing password file.
PASSWORD Password for SYS. You are prompted for the password if it is not specified. The password is stored in the created password file.
ENTRIES (Optional) Maximum number of entries (user accounts) to permit in the file.
FORCE (Optional) If y, permits overwriting an existing password file.
ASM (Optional) If y, create an Oracle ASM password file in an Oracle ASM disk group.
If n, the default, create a password file in the operating system file system. When the DBUNIQUENAME argument is specified, the password file is a database password file. When the DBUNIQUENAME argument is not specified, the password file can be a database password file or an Oracle ASM password file.
DBUNIQUENAME Unique database name used to identify database password files residing in an ASM disk group only. This argument is required when the database password file is stored on an Oracle ASM disk group. This argument is ignored when an Oracle ASM password file is created by setting the ASM argument to y.
FORMAT (Optional) If 12, the default, the password file is created in Oracle Database 12c format. This format supports the SYSBACKUP, SYSDG, and SYSKM administrative privileges.
If legacy, the password file is in legacy format, which is the format before Oracle Database 12c. This argument cannot be set to legacy when the SYSBACKUP, SYSDG, or SYSKM argument is specified.
SYSBACKUP (Optional) If y, creates a SYSBACKUP entry in the password file. You are prompted for the password. The password is stored in the created password file.
SYSDG (Optional) If y, creates a SYSDG entry in the password file. You are prompted for the password. The password is stored in the created password file.
SYSKM (Optional) If y, creates a SYSKM entry in the password file. You are prompted for the password. The password is stored in the created password file.
DELETE (Optional) If y, delete the specified password file.
If n, the default, create the specified password file.
INPUT_FILE (Optional) Name of the input password file. ORAPWD migrates the entries in the input file to a new password file. This argument can convert a password file from legacy format to Oracle Database 12c format. ORAPWD cannot migrate an input password that is stored in an Oracle ASM disk group.
DESCRIBE Describes the properties of the specified password file, including the FORMAT value (12 or legacy) and the IGNORECASE value (y or n).

There are no spaces permitted around the equal-to (=) character.

Note: The IGNORECASE argument is deprecated in this release. Oracle strongly recommends that you set IGNORECASE to n or omit the IGNORECASE setting entirely. 

The following sections provide more information about some of the ORAPWD command line arguments.

FILE

This argument sets the name of the password file being created. This argument is mandatory.

If you specify a location on an Oracle ASM disk group, then the database password file is shared automatically among the nodes in the cluster. When you use an Oracle ASM disk group to store the password file, and you are not using Oracle Managed Files, you must specify the full path name for the file. The full path is not required if you are using Oracle Managed Files.

If you do not specify a location on an Oracle ASM disk group, then the file name required for the password file is operating system specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file.

Table 1-1 lists the required name and location for the password file on the UNIX, Linux, and Windows platforms. For other platforms, consult your platform-specific documentation.

Table 1-1 Required Password File Name and Location on UNIX, Linux, and Windows

Platform Required Name Location
UNIX and Linux orapwORACLE_SID ORACLE_HOME/dbs
Windows PWDORACLE_SID.ora ORACLE_HOME\database

For example, for a database instance with the SID orcldw, the password file must be named orapworcldw on Linux and PWDorcldw.ora on Windows.

In an Oracle Real Application Clusters environment on a platform that requires an environment variable to be set to the path of the password file, the environment variable for each instance must point to the same password file.

For a policy-managed Oracle RAC database or an Oracle RAC One Node database with ORACLE_SID of the form db_unique_name_n, where n is a number, the password file is searched for first using ORACLE_HOME/dbs/orapwsid_prefix or ORACLE_HOME\database\PWDsid_prefix.ora. The sid_prefix (the first 8 characters of the database name) is used to locate the password file.

Note: It is critically important to the security of your system that you protect your password file and the environment variables that identify the location of the password file. Any user with access to these could potentially compromise the security of the connection.

ENTRIES
This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

Entries can be reused as users are added to and removed from the password file. If you intend to add users to a password file by granting SYSDBA and SYSOPER privileges to them, then this argument is required.

Note: When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate more entries than you think you will ever need.

FORCE
This argument, if set to y, enables you to overwrite an existing password file. An error is returned if a password file of the same name already exists and this argument is omitted or set to n.

ASM
If this argument is set to y, then ORAPWD creates an Oracle ASM password file. The FILE argument must specify a location in the Oracle ASM disk group.

If this argument is set to n, the default, then ORAPWD creates a password file. The FILE argument can specify a location in the Oracle ASM disk group or in the operating system file system. When the DBUNIQUENAME argument is specified, the password file is a database password file. When the DBUNIQUENAME argument is not specified, the password file can be a database password file or an Oracle ASM password file.

DBUNIQUENAME
This argument sets the unique database name for a database password file being created on an Oracle ASM disk group. It identifies which database resource to update with the database password file location.

This argument is not required when a database password file is created on an operating system file system.

This argument is ignored when an Oracle ASM password file is created by setting the ASM argument to y.

FORMAT
If this argument is set to 12, the default, then ORAPWD creates a database password file in Oracle Database 12c format. Oracle Database 12c format is required for the password file to support SYSBACKUP, SYSDG, and SYSKM administrative privileges.

If this argument is set to legacy, then ORAPWD creates a database password file that is in the format before Oracle Database 12c. The password file supports SYSDBA and SYSOPER administrative privileges, but it does not support SYSBACKUP, SYSDG, and SYSKM administrative privileges.

SYSBACKUP
If this argument is set to y, then ORAPWD creates a SYSBACKUP entry in the password file. You are prompted for the password. The password is stored in the created password file.

If this argument is set to n, then ORAPWD does not create a SYSBACKUP entry in the password file. If a password file was created in Oracle Database 12c format, then you can add a SYSBACKUP entry to the password file.

SYSDG
If this argument is set to y, then ORAPWD creates a SYSDG entry in the password file. You are prompted for the password. The password is stored in the created password file.

If this argument is set to n, then ORAPWD does not create a SYSDG entry in the password file. If a password file was created in Oracle Database 12c format, then you can add a SYSDG entry to the password file.

SYSKM
If this argument is set to y, then ORAPWD creates a SYSKM entry in the password file. You are prompted for the password. The password is stored in the created password file.

If this argument is set to n, then ORAPWD does not create a SYSKM entry in the password file. If a password file was created in Oracle Database 12c format, then you can add a SYSKM entry to the password file.

DELETE
If this argument is set to y, then ORAPWD deletes the specified password file. When y is specified, FILE, ASM, or DBUNIQUENAME must be specified. When FILE is specified, the file must be located on an ASM disk group.

If this argument is set to n, the default, then ORAPWD creates the password file.

INPUT_FILE
This argument specifies the name of the input password file. ORAPWD migrates the entries in the input file to a new password file. This argument can convert a password file from legacy format to Oracle Database 12c format.

When an input file is specified, ORAPWD does not create any new entries. Therefore, ORAPWD ignores the following arguments:
  1. PASSWORD
  2. SYSBACKUP
  3. SYSDG
  4. SYSKM
When an input file is specified and the new password file replaces the input file, FORCE must be set to y.

  • Creating a Database Password File with ORAPWD

You can create a database password file with ORAPWD.

To create a database password file:

1. Run the ORAPWD command.

Example 1-19 Creating a Database Password File Located in an Oracle ASM Disk Group

The following command creates a database password file in Oracle Database 12c format named orapworcl that is located in an Oracle ASM disk group. The DBUNIQUENAME argument is required because the database password file is located in an Oracle ASM disk group. The password file allows up to 10 privileged users with different passwords.

orapwd FILE='+DATA/orcl/orapworcl' ENTRIES=10 DBUNIQUENAME='orcl' FORMAT=12

Example 1-20 Creating a Database Password File with a SYSBACKUP Entry

The following example is the similar to Example 1-19 except that it creates a SYSBACKUP entry in the database password file. The password file is in Oracle Database 12c format by default.

orapwd FILE='+DATA/orcl/orapworcl' ENTRIES=10 DBUNIQUENAME='orcl' SYSBACKUP=y

Example 1-21 Creating a Database Password File Located in a File System

The following command creates a database password file in Oracle Database 12c format named orapworcl that is located in the default location in an operating system file system. The password file allows up to 30 privileged users with different passwords.

orapwd FILE='/u01/oracle/dbs/orapworcl' ENTRIES=30 FORMAT=12

Example 1-22 Migrating a Legacy Database Password File to Oracle Database 12c Format

The following command migrates a database password file in legacy format to Oracle Database 12c format. The password file is named orapworcl, and it is located in an operating system file system. The new database password file replaces the existing database password file. Therefore, FORCE must be set to y.

orapwd FILE='/u01/oracle/dbs/orapworcl' FORMAT=12 
 INPUT_FILE='/u01/oracle/dbs/orapworcl' FORCE=y

Example 1-23 Describing a Password File

The following command describes the orapworcl password file.

orapwd DESCRIBE FILE='orapworcl'
Password file Description : format=12 ignorecase=N

  • Sharing and Disabling the Database Password File

You use the initialization parameter REMOTE_LOGIN_PASSWORDFILE to control whether a database password file is shared among multiple Oracle Database instances. You can also use this parameter to disable password file authentication.

To share a password file or disable password file authentication:

1. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter.

You can set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to one of the following values:

2. none: Setting this parameter to none causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.

3. exclusive: (The default) An exclusive password file can be used with only one database. Only an exclusive file can be modified. Using an exclusive password file enables you to add, modify, and delete users. It also enables you to change the password for SYS, SYSBACKUP, SYSDG, or SYSKM with the ALTER USER command.

When an exclusive password file is stored on an Oracle ASM disk group, it can be used by a single-instance database or multiple instances of an Oracle Real Application Clusters (Oracle RAC) database.

When an exclusive password file is stored on an operating system, it can be used with only one instance of one database.

4. shared: A shared password file can be used by multiple databases running on the same server, or multiple instances of an Oracle RAC database, even when it is stored on an operating system. A shared password file is read-only and cannot be modified. Therefore, you cannot add users to a shared password file. Any attempt to do so or to change the password of SYS or other users with the administrative privileges generates an error. All users needing administrative privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to exclusive. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to shared, and then share the file.

This option is useful if you are administering multiple databases with a single password file.

You cannot specify shared for an Oracle ASM password file.

If REMOTE_LOGIN_PASSWORDFILE is set to exclusive or shared and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to none.

  • Keeping Administrator Passwords Synchronized with the Data Dictionary

If you change the REMOTE_LOGIN_PASSWORDFILE initialization parameter from none to exclusive or shared, or if you re-create the password file with a different SYS password, then you must ensure that the passwords in the data dictionary and password file for the SYS user are the same.

To synchronize the SYS passwords, use the ALTER USER statement to change the SYS password. The ALTER USER statement updates and synchronizes both the dictionary and password file passwords.

To synchronize the passwords for non-SYS users who log in using the SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative privilege, you must revoke and then regrant the privilege to the user, as follows:

1. Find all users who have been granted the SYSDBA privilege.
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';

2. Revoke and then re-grant the SYSDBA privilege to these users.
REVOKE SYSDBA FROM non-SYS-user;
GRANT SYSDBA TO non-SYS-user;

3. Find all users who have been granted the SYSOPER privilege.
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSOPER='TRUE';

4. Revoke and regrant the SYSOPER privilege to these users.
REVOKE SYSOPER FROM non-SYS-user;
GRANT SYSOPER TO non-SYS-user;

5. Find all users who have been granted the SYSBACKUP privilege.
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSBACKUP ='TRUE';

6. Revoke and regrant the SYSBACKUP privilege to these users.
REVOKE SYSBACKUP FROM non-SYS-user;
GRANT SYSBACKUP TO non-SYS-user;

7. Find all users who have been granted the SYSDG privilege.
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDG='TRUE';

8. Revoke and regrant the SYSDG privilege to these users.
REVOKE SYSDG FROM non-SYS-user;
GRANT SYSDG TO non-SYS-user;

9. Find all users who have been granted the SYSKM privilege.
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSKM='TRUE';

10. Revoke and regrant the SYSKM privilege to these users.
REVOKE SYSKM FROM non-SYS-user;
GRANT SYSKM TO non-SYS-user;

  • Adding Users to a Database Password File

When you grant SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative privilege to a user, that user's name and privilege information are added to the database password file.

A user's name remains in the password file only as long as that user has at least one of these privileges. If you revoke all of these privileges, Oracle Database removes the user from the password file.

Note: The password file must be created with the FORMAT=12 argument to support SYSBACKUP, SYSDG, or SYSKM administrative privilege.

Creating a Password File and Adding New Users to It

Use the following procedure to create a password and add new users to it:

1. Follow the instructions for creating a password file as explained in "Creating a Database Password File with ORAPWD".
2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to exclusive. (This is the default.)
Oracle Database issues an error if you attempt to grant these privileges and the initialization parameter REMOTE_LOGIN_PASSWORDFILE is not set correctly.

Note: REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.

3. Connect with SYSDBA privileges as shown in the following example, and enter the SYS password when prompted:
CONNECT SYS AS SYSDBA

4. Start up the instance and create the database if necessary, or mount and open an existing database.
5. Create users as necessary. Grant SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative privilege to yourself and other users as appropriate. 

  • Granting and Revoking Administrative Privileges

Use the GRANT statement to grant administrative privileges. Use the REVOKE statement to revoke administrative privileges.

To grant the SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative privilege to a user:

1. Run the GRANT statement .

For example:

GRANT SYSDBA TO mydba;

To revoke the administrative privilege from a user:

2. Run the REVOKE statement.

For example:

REVOKE SYSDBA FROM mydba;

The WITH ADMIN OPTION is ignored if it is specified in the GRANT statement that grants an administrative privilege, and the following rules apply:
  1. A user currently connected as SYSDBA can grant any administrative privilege to another user and revoke any administrative privilege from another user.
  2. A user currently connected as SYSOPER cannot grant any administrative privilege to another user and cannot revoke any administrative privilege from another user.
  3. A user currently connected as SYSBACKUP can grant or revoke another user's SYSBACKUP administrative privilege.
  4. A user currently connected as SYSDG can grant or revoke another user's SYSDG administrative privilege.
  5. A user currently connected as SYSKM can grant or revoke another user's SYSKM administrative privilege.
  6. Administrative privileges cannot be granted to roles, because roles are available only after database startup. Do not confuse the database administrative privileges with operating system roles.

  • Viewing Database Password File Members

The V$PWFILE_USERS view contains information about users that have been granted administrative privileges.

To determine which users have been granted administrative privileges:

1. Query the V$PWFILE_USERS view.

The columns displayed by this view are as follows:

ColumnDescription
USERNAMEThis column contains the name of the user that is recognized by the password file.
SYSDBAIf the value of this column is TRUE, then the user can log on with the SYSDBA administrative privileges.
SYSOPERIf the value of this column is TRUE, then the user can log on with the SYSOPER administrative privileges.
SYSASMIf the value of this column is TRUE, then the user can log on with the SYSASM administrative privileges.
SYSBACKUPIf the value of this column is TRUE, then the user can log on with the SYSBACKUP administrative privileges.
SYSDGIf the value of this column is TRUE, then the user can log on with the SYSDG administrative privileges.
SYSKMIf the value of this column is TRUE, then the user can log on with the SYSKM administrative privileges.

  • Expanding the Number of Database Password File Users

If you receive an error when you try to grant system privileges to a user because the file is full, then you must create a larger database password file and grant the privileges to the users again.

Replacing a Password File


Use the following procedure to replace a database password file:
  1. Identify the users who have system privileges by querying the V$PWFILE_USERS view.
  2. Delete the existing database password file.
  3. Follow the instructions for creating a new database password file using the ORAPWD utility in "Creating a Database Password File with ORAPWD". Ensure that the ENTRIES parameter is set to a number larger than you think you will ever need.
  4. Follow the instructions in "Adding Users to a Database Password File".

  • Removing a Database Password File

You can remove a database password file if it is no longer needed.

If you determine that you no longer require a database password file to authenticate users, then to remove it:

1. Delete the database password file, and optionally reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to none.

After you remove this file, only those users who can be authenticated by the operating system can perform SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM database administration operations.

«« Previous
Next »»