Skip Headers
Oracle® Database Administrator's Guide
11g Release 2 (11.2)

Part Number E10595-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Creating and Maintaining a Password File

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

This section contains the following topics:

Creating a Password File with ORAPWD

The syntax of the ORAPWD command is as follows:

ORAPWD FILE=filename [ENTRIES=numusers] [FORCE={Y|N}] [IGNORECASE={Y|N}]

Command arguments are summarized in the following table.

Argument Description
FILE Name to assign to the password file. You must supply a complete path. If you supply only a file name, the file is written to the current directory.
ENTRIES (Optional) Maximum number of entries (user accounts) to permit in the file.
FORCE (Optional) If y, permits overwriting an existing password file.
IGNORECASE (Optional) If y, passwords are treated as case-insensitive.

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

The command prompts for the SYS password and stores the password in the created password file.

Example

The following command creates a password file named orapworcl that allows up to 30 privileged users with different passwords.

orapwd FILE=orapworcl ENTRIES=30

ORAPWD Command Line Argument Descriptions

The following sections describe the ORAPWD command line arguments.

FILE

This argument sets the name of the password file being created. You must specify the full path name for the file. The contents of this file are encrypted, and the file cannot be read directly. This argument is mandatory.

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 Required 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.

Caution:

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 specify REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this argument is required.

Caution:

When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate a number of entries that is larger 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.

IGNORECASE

If this argument is set to y, passwords are case-insensitive. That is, case is ignored when comparing the password that the user supplies during login with the password in the password file.

See Also:

Oracle Database Security Guide for more information about case-sensitivity in passwords.

Sharing and Disabling the Password File

You use the initialization parameter REMOTE_LOGIN_PASSWORDFILE to control whether or not a password file is shared among multiple Oracle Database instances. You can also use this parameter to disable password file authentication. The values recognized for REMOTE_LOGIN_PASSWORDFILE are:

  • 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.

  • EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of 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 SYS password with the ALTER USER command.

  • SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that 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 SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system 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 or a RAC database.

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.

Note:

You cannot change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so.

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 recreate 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 or SYSOPER 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;
    

Adding Users to a Password File

When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle Database issues an error if you attempt to grant these privileges.

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

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 Password File with ORAPWD".

  2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default.)

    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 or SYSOPER privileges to yourself and other users as appropriate. See "Granting and Revoking SYSDBA and SYSOPER Privileges", later in this section.

Granting and Revoking SYSDBA and SYSOPER Privileges

If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:

GRANT SYSDBA TO oe;

Use the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:

REVOKE SYSDBA FROM oe;

Because SYSDBA and SYSOPER are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user's SYSDBA or SYSOPER system privileges. These privileges cannot be granted to roles, because roles are available only after database startup. Do not confuse the SYSDBA and SYSOPER database privileges with operating system roles.

See Also:

Oracle Database Security Guide for more information on system privileges

Viewing Password File Members

Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER system privileges for a database. The columns displayed by this view are as follows:

Column Description
USERNAME This column contains the name of the user that is recognized by the password file.
SYSDBA If the value of this column is TRUE, then the user can log on with SYSDBA system privileges.
SYSOPER If the value of this column is TRUE, then the user can log on with SYSOPER system privileges.

Maintaining a Password File

This section describes how to:

  • Expand the number of password file users if the password file becomes full

  • Remove the password file

Expanding the Number of Password File Users

If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user, you must create a larger password file and regrant the privileges to the users.

Replacing a Password File

Use the following procedure to replace a password file:

  1. Identify the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.

  2. Delete the existing password file.

  3. Follow the instructions for creating a new password file using the ORAPWD utility in "Creating a 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 Password File".

Removing a Password File

If you determine that you no longer require a password file to authenticate users, you can delete the password file and then 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 or SYSOPER database administration operations.