Using Password Files

Password files allow you to set passwords that are stored outside the database and that are used for authenticating administrators. These passwords are stored in an external file that is encrypted by Oracle. Password files can be even used if the database is down-so. You can use this passwords even if database is down .

To create a password file use the ORAPWD utility:

[oracle@orcl ~]$ orapwd file=./test_pwd entries=100 ignorecase=n
Enter password for SYS: 
[oracle@orcl ~]$ 

After creating the password file, you must set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to an appropriate value (  NONE, EXCLUSIVE and SHARED ).
NONE-causing Oracle to behave as though a password file does not exist. EXCLUSIVE means that the password file is being used only by your database and that you can modify it from within the database . SHARED-allows you to use a single password file for multiple databases, but none of them can update the password file. If you need to update the password file then you need to switch this parameter to EXCLUSIVE in one of the databases, change the password file and then change it back to be used as SHARED.

SQL> show parameter remote_login_passwordfile;

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

Now try connect to oracle as sysdba from other machine

C:UsersValeh>sqlplus sys@db11g as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 5 00:17:21 2015
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter password:

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

SQL>

OS Authentication

OS authentication is used to  authenticate user  at the OS level to connect  to the database. For OS authentication OS username must be mapped to the Oracle user name. The initialization parameter OS_AUTHENT_PREFIX controls that mapping. This value is concatenated with the OS username and used as the database user. Default value for OS_AUTHENT_PREFIX parameter ops$. If the default value is not changed, then database user of the OS user valeh will be ops$valeh.If this value is set to null then you will log onto the database as user valeh.

SQL> show parameter os_authent_prefix;

NAME				  TYPE	 VALUE
-------------------- ---------- -------------
os_authent_prefix     string	 ops$
SQL> 

To allow access from OS users, you must enable remote OS authentication, create a user and grant privileges :

--Fisrt create OS user for testing

[root@orcl ~]# useradd valeh
[root@orcl ~]# passwd valeh
Changing password for user valeh.
New password: 
Retype new password: 
passwd: all authentication tokens updated successfully.

SQL> show parameter remote_os_authent;
NAME 		      	TYPE 		VALUE
------------------ ----------- ----------------
remote_os_authent     boolean	FALSE

SQL> alter system set remote_os_authent=true scope=spfile;
SQL> shutdown
SQL> startup
 
SQL> show parameter remote_os_authent;
NAME			     TYPE	      VALUE
------------------  ---------- --------------
remote_os_authent    boolean     TRUE

SQL> create user ops$valeh identified externally;

User created.
SQL> grant create session to ops$valeh;

Grant succeeded.
 

Log in to the new created user, set oracle environment variables and run sqlplus, you don’t need to specify a username or a password to logon to Oracle:

[root@orcl ~]# su - valeh
[valeh@orcl ~]$ sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 2 19:56:37 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "OPS$VALEH"