IN ORACLE MILIEU …

Introduction

As a DBA we must have used sqlplus “/as sysdba” to connect to database, atleast hundred times a day. Never bothered about the password to provide !!!

This is because we were using OS level authentication. We can change the configuration and make Oracle to ask for the password. Well, “/as sysdba” works fine if we are connecting to the host where the database is actually installed.

For example I have installed a database as oracle01 user (which belongs to DBA group) on one of my host called “host1″. I telnet to host1 as oracle01 user and provide the password to login. Once I successfully login to the host, there ends the authentication part. Not for administering the database all I have to do is to use our famous command to connect to database – “sqlplus /as sysdba”.

The reason above thing work is because I was using Operating System level authentication. If I try to connect to same database as sysdba from some other host, I wont be able to connect. Because the authentication is done based on host login password. Since I haven’t logged into host, authentication will fail and connect as sysdba will fail. So for OS authentication its mandatory that you are always logged into the host where the oracle is installed (oracle database resides).

Authentication Type

There are 2 types of authentication:

1. OS (Operating System) Authentication
2. Password File Authentication

And yes the above one that i explained is OS level authentication. Lets see what is password file authentication.

Password File Authentication

In case of password file authentication, we create a password file for our database. ORAPWD is the utility for creating a password file. This utility is provided by oracle and comes when you install database. This binary is present in ORACLE_HOME/bin directory. Below is the usage for the same.

ORAPWD FILE=(file_name) password=(password) ENTRIES=(Entries)

Where file_name is the name and location of the password file. Usually we create password with name as ora(SID).pwd in ORACLE_HOME/dbs directory. So value for file_name becomes $ORACLE_HOME/dbs/ora(sid).pwd

password - is the password you want to set for password file. Remember that this will become the password for sys user as well. Meaning that when you are connecting as sys user, you need to provide this password. (oracle will prompt for password in case of password file authentication).

Entries - This is the number of entries that password file can have. Be careful while providing this value as once you set this value, you cannot change it. You have to delete password file and recreate it, but its risky.

Example:

$orapwd FILE=/u01/oracle/product/9.2.0/dbs/oraorcl.pwd PASSWORD=welcome1 ENTRIES=10

This will create a password file oraorcl.pwd in /u01/oracle/product/9.2.0/dbs directory.

After creating password file, how your database will know that you have created password file and you are supposed to use the same. This is done by INIT.ORA parameter REMOTE_LOGIN_PASSWORDFILE. This
parameter can have 3 values (none - OS level authentication, shared/exclusive – password file authentication). So for using password file, you need to set the value of this parameter to either shared or exclusive.

What is the difference between SHARED and EXCLUSIVE?

If we set the value of REMOTE_LOGIN_PASSWORDFILE to SHARED in INIT.ORA file, then following is true.

• This file can be used for more then one database (shared file)
• One SYS user will be recognized by database. Meaning that you can login to database using SYS and no other user holding sysdba responsibility. However you can connect to database using SYSTEM or any other user but not the once holding sysdba responsibility.

If we set the value of REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE in INIT.ORA file, then following is true.

• This file will be specific to one database only. Other database cannot use this file.
• Any user enter having sysdba responsibility, which is present in password file can be connected to database as sysdba from remote server.

So when using password file authentication remember to set the value of REMOTE_LOGIN_PASSWORDFILE to SHARED or EXCLUSIVE in INIT.ORA file. Also when using OS level authentication set the value if this parameter to NONE.

Hope this helps !!!