Creating and Maintaining a Password File

Password file are necessary because oracle needs a way to authenticate you when you are connecting over a network to a database that has not been open. If you are connecting to an instance over a network then you have not logged into the database server, and oracle has no way to verify you.
As the password file are not the database file oracle can read it when when the database is closed. This gives the oracle a way to authenticate you.
You can create a password file using the password file creation utility, ORAPWD.

ORAPWD FILE=filename [ENTRIES=numusers]

   [FORCE={Y|N}] [IGNORECASE={Y|N}] [NOSYSDBA={Y|N}]

FILE: Name to assign to the 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.‎

IGNORECASE: ‎(Optional) If y, passwords are treated as case-insensitive.‎

NOSYSDBA: ‎(Optional) For Data Vault installations. See the Data Vault installation guide ‎for your platform for more information.‎

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

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

C:\> orapwd FILE=PWDsid.ora PASSWORD=password ENTRIES=max_users

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

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

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

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

       CONNECT SYS AS SYSDBA

3.      Start up the instance and create the database if necessary, or mount and open an existing database.

4.      Create users as necessary. Grant SYSDBA or SYSOPER privileges to yourself and other users as appropriate.

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:

GRANT SYSDBA TO shaan;

REVOKE SYSDBA FROM shaan;

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.

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.

Maintaining a Password File

·         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 re-grant the privileges to the users.

Replacing 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 "Using 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.

To make the password invisible from Command

C:\oracle\ora92\database> attrib +H PWDsid.ora

To make the password invisible

C:\oracle\ora92\database> attrib -H PWDsid.ora

To make the password invisible from Windows

1. Navigate to directory C:\oracle\ora92\database.
2. Right-click PWDsid.ora.
3. Choose Properties.
4. The PWDsid.ora Properties dialog box opens
5. In Attributes, check or clear the checkbox next to Hidden.
6. Click OK.

To view or hide an invisible password file:

1. Navigate to directory C:\oracle\ora92\database.
2. Choose Folder Options from the View main menu.
3. Choose the View tab.
4. To view an invisible password file, choose Show hidden files and folders.
5. To hide an invisible password file, choose Do not show hidden files and folders.
6. Click OK.