Password File Authentication in Oracle Database

-

Password File Authentication in Oracle Database

PUBLISHED by anand gupta
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.ORAparameter 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.ORAfile, 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 ofREMOTE_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 !!!

dont forget to comment

Comments

Post a Comment

Popular posts from this blog

Query for Tablespace usage with Autoextend

-
                                         Monitoring Tablespace Usage in Oracle I often see questions in Oracle newsgroups and forums about monitoring space in tablespaces, and many of those questions are regarding Enterprise Manager 12c and the alerts it generates reporting the percentage of used space. Of course how you monitor free space depends on whether or not the datafiles are autoextensible. Let's look at several ways to monitor free and used space, starting with methods used in older releases. In versions prior to Oracle 10 DBA_TABLESPACES, DBA_DATA_FILES and DBA_FREE_SPACE were used to generate such reports. One script, from MOS, uses these three views to compute the used space percentage: SELECT SUM(s.used_gb)/SUM(f.total_gb)*100 FROM dba_tablespaces t, ( SELECT tablespace_name, SUM(NVL(bytes,0))/(1024*1024*1024) total_gb FROM dba_data_files ...
Read more

How to configure multiple Oracle listeners

-
How to configure multiple Oracle listeners It happened to me few times during the stress tests that Oracle listener became a bottleneck and not be able to handle the required workload. It was resolved by creating  multiple  listeners which appeared to be a quick solution.  Here is a short step-by-step procedure to configure multiple Oracle listeners  on Solaris for standalone Oracle 9i and 10g environment. 1)  First of all, add additional NIC and cables. They can be on separate subnetwork or the same. In the later, make sure to set static route if needed.    2) Assume that we are going to configure two listeners,   LISTENER and LISTENER2 Modify listener.ora and tnsnames.ora as following: Here is a sample of   listener.ora  LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))     ) ...
Read more

sheel script - automatic tablespace addition in oracle 11g

-
      sheel script - automatic tablespace addition in oracle 11g #!/bin/bash a=`sqlplus -s / as sysdba <<END set pagesize 0 feedback off verify off heading off echo off; select tablespace_name from dba_segments group by tablespace_name having SUM(BYTES)/1024/1024>700; END` f=${#a} c=0 for file in $a ; do eval "var$c=$file"; c=$((c+1)); done echo $var0 array1=$(df -kh | grep '/u0*'|awk '{ print $6}') array2=$(df -kh | grep '/u0*'|awk '{ print $5}') size=${#array1} size1=${#array2} if ["$size" -eq 4] && ["$size1" -eq 3] && ["$array2" -gt 85%] && ["$f" -eq 1] then d=${array1[0]:0:4} sqlplus /nolog <<END connect /as sysdba alter tablespace $a add datafile '$d/$a01.dbf' size 10m autoextend on maxsize 28G; END` fi if ["$size" -eq 9] && ["$size1" -eq 3] && ["$array2 -gt...
Read more