Password File Authentication - UNIX

After looking at all the possibilities of OS level authentication in the previous post , we will look the password file auth in this post. First we will look at few basic concepts and then go into mroe detail :-

remote_login_passwordfile :-

The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. There are three values this parameter can take and the values are :-

1. NONE: Oracle Database behaves as if the password file does not exist and hence doesnt use password file authentication.

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

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

SQL> show parameter pass

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

Password File :-

The default location for the password file is: $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows. We 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}]

Example :-

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=welcome1 entries=1 force=y;

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

PASSWORD : The password for the SYS user. If you use an ALTER USER statement to change the password for the SYS user after you connect to the database, then both the password stored in the data dictionary and the password stored in the password file are updated ( Only in case of EXCLUSIVE access). This parameter is mandatory.

Connecting to SQLPLUS :-

First of all , we will remove the possibility of connecting as "sqlplus / as sysdba" i.e. we will block the OS level authentication by using a non privileged user "timepass" :D .

[timepass@adc2110341 dbhome_1]$ id
uid=60000(timepass) gid=60000(timepass) groups=60000(timepass)

This makes sure that OS authentication won't be used while connecting as sysdba.

First Case :-
Connecting when remote_login_passwordfile=EXCLUSIVE and password file present :-

1. Check if remote_login_passwordfile is either set to EXCLUSIVE/SHARED

SQL> show parameter pass

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

2. Now check if password file is present for your database at the default location $ORACLE_HOME/dbs by the name orapw$ORACLE_SID .

bash-3.00$ pwd
/scratch/aime1/app/aime1/product/11.2.0/dbhome_1/dbs
bash-3.00$ ls orapw*
orapworcl

In our case , it is present . We will look later on how to create/modify the same.

3. We can find the contents of the Password file by two methods :-

1. By Linux utility "strings"

bash-3.00$ strings -a orapworcl 
]\[Z
ORACLE Remote Password file
INTERNAL
765379FA336BE7F0
43CA255A7916ECFE
O!KfH
g[HLm

2. By the view v$pwfile_users

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

Hence , by default password file contains only user SYS in it with the password which we specify while creating it .

Now you can see INTERNAL as the user if displayed by Password file contents but not when you use the v$view . That happens because of the way v$PWFILE_USERS is defined . Lets look at it in more detail.

Question :- Why V$PWFILE_USERS doesn't contain INTERNAL ?
Solution :- We have a view V$FIXED_VIEW_DEFINITION and this view contains the definitions of all the fixed views (views beginning with V$/GV$)

SQL> desc v$fixed_view_definition
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
VIEW_NAME                                          VARCHAR2(30)
VIEW_DEFINITION                                    VARCHAR2(4000)

Now we will select the definition of "V$PWFILE_USERS" from this view :-

SQL> select VIEW_DEFINITION from v$fixed_view_definition where upper(VIEW_NAME) ='V$PWFILE_USERS';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select  USERNAME , SYSDBA , SYSOPER, SYSASM from GV$PWFILE_USERS where inst_id =
USERENV('Instance')

AS we can see , this view is based on another fixed view named "GV$PWFILE_USERS" . Lets check its definition as well .

SQL> select VIEW_DEFINITION from v$fixed_view_definition where upper(VIEW_NAME) ='GV$PWFILE_USERS';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id,username,decode(sysdba,1,'TRUE','FALSE'),  decode(sysoper,1,'TRUE
','FALSE'), decode(sysasm,1,'TRUE','FALSE')  from x$kzsrt where valid=1  and use
rname != 'INTERNAL'

Now if you notice the definition of this view , it is dervied from x$kzsrt which roughly stands for :-
x$kzsrt – read as “[K]ernel [Z]Security layer [R]emote Password File [T]able entries

And the select query is made in such a way that User INTERNAL is not selected. And hence , it doesn't appear in the V$PWFILE_USERS view.

The other important point to note is that User INTERNAL was de-supported in 9i ( when they de-supported SVRMGRL) , even though user still exists in Oracle Database 11g Release 2 .
So trying to connect as internal fails .

bash-3.00$ sqlplus internal as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 12 23:18:32 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter password: 
ERROR:
ORA-09275: CONNECT INTERNAL is not a valid DBA connection

4. Now trying to connect as sysdba using password file :-

[timepass@adc2110341 bin]$ sqlplus sys/welcome1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 12 23:25:31 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Trying to connect from remote host :-

bash-3.00$ sqlplus sys/welcome1@adc_ORCL as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 12 23:26:22 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

This works fine .

Second Case :-
Connecting when remote_login_passwordfile=EXCLUSIVE and password file NOT present :-

1. Check if remote_login_passwordfile is either set to EXCLUSIVE/SHARED

SQL> show parameter pass

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

2. Now we will delete/rename the password file :-

bash-3.00$ mv orapworcl orapworcl.move

And if try to connect now as sysdba ,

[timepass@adc2110341 dbs]$ sqlplus sys/welcome1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 12 23:33:03 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

Third Case :-
Connecting when remote_login_passwordfile=NONE and password file present/NOT present :-

1. Modify the parameter remote_login_passwordfile and set it to NONE and bounce the database.

SQL> alter system set remote_login_passwordfile=NONE scope=spfile;

System altered.

Now check the value of the parameter :-

SQL> show parameter pass

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      NONE

2. Now try to connect as SYSDBA :-

[timepass@adc2110341 dbs]$ sqlplus sys/welcome1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 12 23:37:28 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

As we can see , second and third case are equivalent i.e. if remote_login_passwordfile is set to NONE or if it is set to EXCLUSIVE but the password file is not present , password file authentication is disabled and the only way to connect as sysdba is OS Authentication.

The other point to ntoe is we can't grant a user SYSDBA privilege if password file auth is disabled .

SQL> grant sysdba to dba1;
grant sysdba to dba1
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled

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

Lets try it :-

1. Create user dba1

SQL> create user dba1 identified by dba1;
User created.

SQL> grant connect to dba1;
Grant succeeded.

2. Check the pass file contents :-

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

3. Now grant sysdba to dba1 and check the contents again .

SQL> grant sysdba to dba1;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
DBA1                           TRUE  FALSE FALSE

4. Now try to connect as dba1 as sysdba

SQL> conn dba1/dba1 as sysdba
Connected.

Expanding the Password File :-

If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user

SQL> grant sysdba to d;
grant sysdba to d
*
ERROR at line 1:
ORA-01996: GRANT failed: password file
'/scratch/aime1/app/aime1/product/11.2.0/dbhome_1/dbs/orapworcl' is full

You need to create a larger password file and regrant the privileges to the users.