Thursday, March 3, 2011

Remote_login_passwordfile Parameter and it's use

Remote_login_passwordfile   specifies whether Oracle checks for a password file . When we grant "sysdba" or  "sysoper" privileges to a user, that  user's name  and  privilege  information  are  added to the password file. A user's name remains in the password file only as long as that user has at least one of these two privileges. If we revoke both of these privileges, Oracle Database removes the user from the password file .

We have two options for Super User "SYS" password authentication .
1.) Operating System authentication
2.) Password file authentication

Password file authentication : When we use password file authentication then we have to create password file for  "SYS"  user or  the  user  having  "sysdba"  privileges . We can create the password file as below : 
C:\>orapwd  file=$ORACLE_HOME\database\orapwd.ora  password=sys_password  entries=n force=y

Note:

=> Entries= n  specifies the "maximum number of distinct DBAs and OPERs (opt)" can login into database .
=> Force=y    specifies if the password file exists then it overwrites the existing password file .

 There are three value for remote_login_passwordfile parameters : 

1.) EXCLUSIVE:  This is default value. The password file can be used by only one database and the password file can contain names other than sys.We can use password file for authentication and connect to remote machine with sys user.Only an EXCLUSIVE file can contain the names of users other than SYS. Using an "exclusive" password file lets us allow to grant sysdba and sysoper system privileges to individual users and have them connect as themselves.


2.) NONE: If this parameter set "NONE" means passwordfile doesn't exists. That is, no privileged connections are allowed over non-secure connections. If remote_login_password is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting  remote_login_password  to NONE.


3.) SHARED: A SHARED password file can be used by multiple databases running on the same server. However, only user recognized by a SHARED password file is sys .We cannot add users to a SHARED password file. All users needing  sysdba  or sysoper system privileges must connect using the same name, SYS, and password. This option is useful if we have a single DBA administering multiple databases.A shared password file cannot be modified. This means that we 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, we can change remote_login_passwordfile  to shared, and then share the file.This option is useful if we are administering multiple databases or a RAC database. 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 .

If we ever receive the file full error i.e, ORA-1996  while granting  "sysdba"  or  "sysoper" system privileges to a user, we must create a larger password file and regrant the privileges to the users. The below steps will replace the current passwordfile with new larger password file .

1.)  Identify the users who have sysdba or  sysoper  privileges by querying the  v$pwfile_users views .
2.)  Delete the existing password file.
3.)  Creating a new password file using the ORAPWD utility and ensure that the ENTRIES parameter is set to a number larger then the previous password file .
4.) Connect with "sys" and grant the priveleges to user as quired from v$pwfile_users view .


Enjoy     :-) 


9 comments:

Anonymous said...

Good work buddy..

NEERAJ VISHEN said...

thnks buddy...

Adnan Rafi said...

can you please show us a demo on following :

Entries= n specifies the "maximum number of distinct DBAs and OPERs (opt)" can login into database .

NEERAJ VISHEN said...

Hi adnan..

Below is demo of your above question

1.) Delete your passwordfile

2.) Create a new password file and specify the no. of users i.e, entries as
C:\>orapwd file=%ORACLE_HOME%\database\pwd%ORACLE_SID%.ora password=sys entries=4

3.) Conn with database use the above password and grant sysdba role to other users as

c:\> sqlplus sys/sys@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 31 09:39:10 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> grant sysdba to hr;
Grant succeeded.

SQL> grant sysdba to scott;
Grant succeeded.

SQL> grant sysdba to sysman;
Grant succeeded.

SQL> grant sysdba to system;
Grant succeeded.

SQL> grant sysdba to pm ;
grant sysdba to pm
*
ERROR at line 1:
ORA-01996: GRANT failed: password file '' is full

Since no. of entries=4 so only 4 user can be granted sysdba or sysoper role . You can check the no. of user granted sysdba and sysoper role by using the v$pwfile_users as

SQL> select * from V$PWFILE_USERS;

USERNAME SYSDBA SYSOPER
--------- ------ ------
SYS TRUE TRUE
HR TRUE FALSE
SCOTT TRUE FALSE
SYSMAN TRUE FALSE
SYST TRUE FALSE

Hope this help u

Enjoy :-)

Anonymous said...

Thanks Neeraj well explained !!

Can you elaborate little more "NONE" i.e. oracle docs says privileged users must be authenticated by the operating system when setting Remote_Login_PasswordFile=none,if n't Oracle ignores any password file.

Thiyagusham said...

Hi ;

I tried to restrict sysdba privilege to no of users via orapwd utility.but i can't restrict. even i set no of entries=2 my db allows
upto 4 users.

[oracle@testorcl ~]$ cd $ORACLE_HOME
[oracle@testorcl db_1]$ cd dbs/
[oracle@testorcl dbs]$ rm -rf ora*
[oracle@testorcl dbs]$ orapwd file=orapwtestdb password=welcome entries=2

oracle@testorcl dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 10 16:40:05 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.

QL> grant sysdba to scott;
Grant succeeded.

SQL> grant sysdba to hr;
Grant succeeded.

SQL> grant sysdba to sam;
Grant succeeded.

SQL> grant sysdba to sony;
Grant succeeded.

SQL> grant sysdba to sona;
grant sysdba to sona
*
ERROR at line 1:
ORA-01996: GRANT failed: password file '' is full
Please note : in orapwd file entries=2 only but my db allowed
upto four users.

MANVENDRA MANOHAR BIKRAM said...

Hi Big Brother,
Even i have also done practical on this parameter. Thiyagusham is right.
If we set entries=2 then you can grant SYSDBA or SYSOPER to max 4 users excluding SYS.

NEERAJ VISHEN said...

@Thiyagusham and MANVENDRA MANOHAR BIKRAM


Entries=n specifies the "max no distict distinct sysdba or sysoper login into database " .

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.

If u define entries=5 , then it will except 8 passwd except sys as it is multiple of blocksize .(Assuming Blocksize 512 byte)


Hope this help you , if any doubt, let me know .

RAMESH NAIDU said...

If the file name orapw.Ora the other instance can access that password file in the case of `SHARED`