Creating and Maintaining a Password File - Oracle 10g

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:

  • Using ORAPWD
  • Setting REMOTE_LOGIN_ PASSWORDFILE
  • Adding Users to a Password File
  • Maintaining a Password File

Using ORAPWD
When you invoke this password file creation utility without supplying any parameters, you receive a message indicating the proper use of the command as shown in the following sample output:

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

The following command creates a password file named acct.pwd that allows up to 30 privileged users with different passwords. In this example, the file is initially created with the password secret for users connecting as SYS.

orapwd FILE=acct.pwd PASSWORD=secret ENTRIES=30

The parameters in the ORAPWD utility are described in the sections that follow.

FILE
This parameter 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 parameter is mandatory.

The types of filenames allowed for the password file are 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. See your operating system documentation for the names and locations allowed on your platform.

If you are running multiple instances of Oracle Database using Oracle Real Application Clusters, the environment variable for each instance should point to the same password file.

PASSWORD
This parameter sets the password for user SYS. If you issue the ALTER USER statement to change the password for SYS after connecting to the database, both the password stored in the data dictionary and the password stored in the password file are updated. This parameter is mandatory.

ENTRIES
This parameter 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 reuse as users are added to and removed from the password file. If you intend to specify REMOTE_LOGON_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this parameter is required.

Setting REMOTE_LOGIN_ PASSWORDFILE
In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are:

  • NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, n privileged connections are allowed over nonsecure connections. NONE is the default value for this parameter.
  • EXCLUSIVE: An EXCLUSIVE password file can be used with only one database. Only an EXCLUSIVE file can contain the names of users other than SYS. Using an EXCLUSIVE password file lets you grant SYSDBA and SYSOPER system privileges to individual users and have them connect as themselves.
  • SHARED: A SHARED password file can be used by multiple databases running on the same server. However, the only user recognized by a SHARED password file is SYS. You cannot add users to a SHARED password file. All users needing SYSDBA or SYSOPER system privileges must connect using the same name, SYS, and password. This option is useful if you have a single DBA administering multiple databases.

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) Oracle Database issue 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 "Using ORAPWD"
  2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.
  3. Connect with SYSDBA privileges as shown in the following example: CONNECT SYS/password 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.

Granting the SYSDBA or SYSOPER privilege to a user causes that user's username to be added to the password file. This enables the user to connect to the database as SYSDBA or SYSOPER by specifying username and password (instead of using SYS). The use of a password file does not prevent operating system authenticated users from connecting if they meet the criteria for operating system authentication.

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.

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:

Viewing Password File Members

Column Description

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
  • Avoid changing the state of 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. Shut down the database.
  3. Delete the existing password file.
  4. Follow the instructions for creating a new password file using the ORAPWD utility in "Using ORAPWD" Ensure that the ENTRIES parameter is set to a number larger than you think you will ever need.

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 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 database administration operations.

Changing the Password File State
The password file state is stored in the password file. When you first create a password file, its default state is SHARED. You can change the state of the password file by setting the initialization parameter REMOTE_LOGIN_PASSWORDFILE. When you start up an instance, Oracle Database retrieves the value of this parameter from the parameter file used by the instance. When you mount the database, the database compares the value of this parameter to the value stored in the password file. If the values do not match, the database overwrites the value stored in the file.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics