As of Oracle9i connect internal is no longer supported. While orapwd is not a new feature of Oracle9i, it is the first release of oracle that we will be forced to use it.

Using the password file for authentication

1. Creating the password file with orapwd

   % orapwd file=${ORACLE_HOME}/dbs/orapwSID password=password entries=max_users
   
   
   

   Where:

   • filename is the name of the file that will hold the password information. The password file location will default to the current directory unless a path is provided.
   • password is the one for the SYS user.
   • entries parameter tells Oracle how many users you will be creating in the password file. You cannot increase entries at a later, so set the value high.

   Name and Location of the password file:

   At database startup time, Oracle will only look for the password file in the ORACLE_HOME/dbs directory. The naming convention that Oracle will search for is: orapwSID. The SID must match the ORACLE_SID environment variable. If orapwSID can not be found, Oracle will look for a file named orapw. If Oracle can not find a orapw file you will get the ORA-01990 at database startup time.

   An important security concern is that the password file be secured.

2. Set the REMOTE_LOGIN_PASSWORDFILE parameter

   In addition to creating the password file, you must also set the init.ora parameter REMOTE_LOGIN_PASSWORDFILE to one of:

   • NONE: Oracle ignores any password file. Privileged users must be authenticated by the operating system. This is the default value for this parameter.
   • EXCLUSIVE: An EXCLUSIVE password file can be used with only one database. 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. The only user recognized by a SHARED password file is SYS. All users needing SYSDBA or SYSOPER system privileges must connect using the same name, SYS, and password. You cannot change the password for SYS if the password fole is SHARED.
   
   

   SQL> show parameter password
   
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   remote_login_passwordfile            string      EXCLUSIVE
   

   
   

3. Adding SYSOPER/SYSDBA to a user

   SYSDBA gives the user DBA privileges. SYSOPER authorizes database support activities e.g. shutdown, startup, etc. Users registered in the orapwd file can connect by:

   SQL> grant sysdba to user;
   
   
   

4. Revoking SYSOPER/SYSDBA from a user

   SQL> revoke sysdba from user;
   
   
   

5. Who is registered in the password file ?

   The v$pwfile_users displays the system privileges that have been assigned.

   SQL> select * from v$pwfile_users;
   
   USERNAME                       SYSDB SYSOP
   ------------------------------ ----- -----
   SYS                            TRUE  TRUE
   USER                           TRUE  FALSE
   

   
   

Removing the 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 init.ora parameter to NONE. After you remove this file, only those users who can be authenticated by the operating system can perform database administration operations.

Do not remove or modify the password file if you have a database or instance mounted using REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE or SHARED. If you do, you will be unable to reconnect remotely using the password file. Even if you replace it, you cannot use the new password file, because the timestamps and checksums will be wrong.

Changing the password for the password file

Either of these commands will change the password for the SYS user:

% orapwd force=y file=orapwSID password=new_password

-- OR --

SQL> alter user sys identified by new_password