Thursday 15 December 2016

OS Authentication in Oracle Database 11g

OS Authentication in Oracle Database 11g - Learn how to perform OS Authenticated Login in Oracle Database


How do you create a OS Authenticated User in Oracle Database 11g? Can I login via my OS User in Oracle Database 11g? Login into Oracle Database using SQLPLUS /

Yes, we can create a OS Authenticated User in Oracle Database or in simple words, I can make my current OS User to login into Oracle Database without even providing the credentials. For this, I'll login like 

Sqlplus /

There are basically 3 types of login scenarios that we follow in our local environment to connect to Oracle Database i.e.

1) Sqlplus / as sysdba
2) Sqlplus user@SID/Password or Sqlplus /@SID as sysdba or Sqlplus sys/***@SID as sysdba
3) Sqlplus /

The above mentioned 3 login scenarios are completely different from one another. To make you understand all of them in simple language, I would say

1) Direct OS Authenticated Oracle Login Using Environmental Settings (Generally the User is sys)
2) Remote Login (By providing proper username, db_name and credentials)
3) OS Authenticated Login (Host Login into Local Oracle Database)

Let me limit the approach of this particular post regarding "What is OS Authentication in Oracle Database?"

​As I've told you that the method of connecting to Oracle Database via OS Authentication is:

Sqlplus /

But if you'll try to login your database now using the same, you are going to see something like

C:\>sqlplus /
SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 14 17:50:42 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

Now let's get to know how we do it. There is a fixed parameter in Oracle Database called os_authent_prefix which is used to fulfill the purpose. If you'll query your Oracle Database with 

show parameter os_authent_prefix;

The output will display like:

SQL> show parameter os_authent_prefix;

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
os_authent_prefix                    string      OPS$

It is displaying a value of OPS$ which is default. Now all you need to do is create the OS user inside Oracle database with prefix OPS$ and you are set to go. Obviously you'll need to provide privileges just like we do for any other Oracle user. So I am creating one here and giving the privilege of Create Session to the same.

SQL> create user "OPS$DOMAIN_NAME\DEMON" identified externally;
User created.

​Here the DOMAIN_NAME represents the domain name from which your system is connected. And DEMON is my OS Username.

Note: This method is for creating OS Authenticated User in Windows. To perform the same in Linux, please read my post - Enabling OS Authentication in Linux/Unix

Also is you face trouble getting your OS related user and domain name information in Windows, you can read my post - How to find user and domain details using Command Prompt in Windows

​Once you are done creating your user, you can also provide appropriate privilege to the same like I have provided the CREATE SESSION privilege to my OS User.​

SQL> grant create session to "OPS$DOMAIN_NAME\DEMON";

Grant succeeded.

Also there is one more consideration that you'll need to follow in order to make this work i.e. you need to set the Sqlnet Authentication Services like shown below

SQLNET.AUTHENTICATION_SERVICES= (NTS)

In default Oracle Database Installation, it is by default the same so no need to change but still check your SQLNET.ORA file for the confirmation.

​After you are done following the above steps, close you command prompt, reopen it and enter try to perform OS Authenticated Login again into your Oracle Database, you'll see something like this


C:\>sqlplus /

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 14 17:52:58 2016

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

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

SQL>

I hope you'll like this post :)

No comments:

Post a Comment