How to create an Oracle User to use both OS Authentication and Password Authentication

The initialization parameter REMOTE_OS_AUTHENT offers a trusted authentication model to the network, users can have OS accounts on machines other than the database server and gain access to database, convenience of single sign-on through remote OS authentication

REMOTE_OS_AUTHENT accepts BOOLEAN value, FALSE is a default value, which can be altered via ALTER SYSTEM command. This parameter is available since Oracle7, is deprecated from Oracle 11g (11.1.0.6), and being retained for backward compatibility only.

OS_AUTHENT_PREFIX specifies a prefix that Oracle uses to authenticate users attempting to connect to the server. Oracle concatenates the value of this parameter to the beginning of the user’s operating system account name and password. When a connection request is attempted, Oracle compares the prefixed username with Oracle usernames in the database. The default value of this parameter is OPS$ for backward compatibility with previous versions. However, you might prefer to set the prefix value to “” (a null string), thereby eliminating the addition of any prefix to operating system account names.

Below example illustrates how connect to oracle database using a single account with and without password – DO NOT try implement this feature in your production server.

Let us examine the default values:

SQL> show parameter os_authent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
remote_os_authent                    boolean     FALSE
SQL>

REMOTE_OS_AUTHENT can be set to TRUE using ALTER SYSTEM command, which will not take effect until the database is restarted.

SQL> alter system set remote_os_authent=TRUE scope=SPFILE;

System altered.

After the database restart the new value became effective:

SQL> show parameter os_authent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
remote_os_authent                    boolean     TRUE
SQL>

Now, let us create a schema (also know as database user) using traditional method, i.e. identified by password instead of identified externally.

SQL> create user ops$rperumal identified by mypassword;

User created.

SQL> grant connect to ops$rperumal;

Grant succeeded

Now, login as an OS user, it allows you to connect to database without the password (i.e. using OS authentication)

$ sqlplus /@pdb10

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 13 06:40:52 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

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

SQL> show user
USER is "OPS$RPERUMAL"

Additionally, the same account can connect to database by supplying the password (i.e traditional way connect username/password)

SQL> connect ops$rperumal/mypassword@pdb10
Connected.
SQL> show user
USER is "OPS$RPERUMAL"