Password File

Password file in Oracle:

Oracle password file stores passwords for users with administrative privileges.

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate the DBA. Obviously, DBA password cannot be stored in the database, because Oracle cannot access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinctmechanisms to authenticate the DBA:

(i) Using the password file or

(ii) Through the operating system (groups). Any OS user under dba group, can login as SYSDBA.

The default location for the password file is:

$ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix,

%ORACLE_HOME%\database\ORAPWD%ORACLE_SID%.ora on Windows.

REMOTE_LOGIN_PASSWORDFILE

The init parameter REMOTE_LOGIN_PASSWORDFILE specifies if a password file is used to authenticate the Oracle DBA or not. If it set either to SHARED or EXCLUSIVE, password file will be used.

REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without bouncing the database.

Following are the valid values for REMOTE_LOGIN_PASSWORDFILE:

NONE - Oracle ignores the password file if it exists i.e. no privileged connections are allowed over non secure connections. 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.

EXCLUSIVE (default) - Password file is exclusively used by only one (instance of the) database. Any user can be added to the password file. Only an EXCLUSIVE file can be modified. 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 - The password file is shared among databases. 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. However, the only user that can be added/authenticated is SYS.

A SHARED password file cannot be modified i.e. 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 or SYSASM (this is from Oracle 11g) privileges generates an error. All users needing SYSDBA or SYSOPER or SYSASM 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.

This option is useful if you are administering multiple databases or a RAC database.

If a password file is SHARED or EXCLUSIVE is also stored in the password file. After its creation, the state is SHARED. The state can be changed by setting REMOTE_LOGIN_PASSWORDFILE and starting the database i.e. the database overwrites the state in the password file when it is started up.

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.

Using ORAPWD

The syntax of the ORAPWD command is as follows:

$ORAPWD FILE=filename password=<password> [ENTRIES=numusers] [FORCE={Y|N}] [IGNORECASE={Y|N}] [NOSYSDBA={Y|N}]

EX:

$orapwd file=$ORACLE_HOME/dbs/orapworcl  password=<pasword> entries=30

ORAPWD Command Line Argument Descriptions:

FILE

This argument sets the name of the password file being created. You must specify the full path name for the file. If you supply only a file name, the file is written to the current directory. The contents of this file are encrypted, and the file cannot be read directly. This argument is mandatory.

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.

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.

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.

Creating a Password File and Adding New Users to It

1.Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.

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

$sqlplus / as sysdba

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

Sql>create user orauser identified by orauser;

Sql>grant sysdba to orauser;

To see the users who assigned to passwordfile or granted sysdba or sysoper privs.

Sql>SELECT USERNAME FROM V$PWFILE_USERS;

To revoke sysdba or remove from password file:

Sql>revoke sysdba from orauser;

Check users now:

---------------------------

Sql>SELECT USERNAME FROM V$PWFILE_USERS;