Friday, 16 December 2016

Enable OS Authentication in Oracle Database - Linux/Unix

Enable OS Authentication in Linux/Unix


How do we enable OS User Login Access in Oracle Database 11g in Linux? Can normal user like Oracle or Grid can login into Oracle Database 11g?

As I mentioned in one of my previous posts regarding - How do we enable OS Authentication in Oracle database 11g in Windows? - I showed you how do we manage to login via OS User into Oracle Database 11g

Now OS Authenticated Login in Linux is very similar to Windows except the fact that you don't need to mention the domain info while creating OS User in Oracle Database. So lets begin,


First of all switch to your desired user and try to login into Oracle Database after setting up all the Environment variables like shown below (In my case, the user is DEMON

$ su - demon
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ export PATH=$PATH:$ORACLE_HOME/bin
$ export ORACLE_SID=ORCL
$ sqlplus /
 
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 16 14:21:19 2016
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
ERROR:
ORA-01017: invalid username/password; logon denied

As expected, the ERROR CODE ORA-01017 appears saying invalid username/password because the user DEMON is not OS Authenticated for now. We have to create one in the Oracle Database first and provide proper 

privileges to the same first in order to make this happen.

So let's check the value of OS_AUTHENT_PREFIX at first,

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

As expected, the value is OPS$ (by default). Now let's create the user,

create user ops$DEMON identified externally;
 
grant create session to ops$DEMON;

Now the user OS Authenticated user DEMON is created with prefix OPS$ and we are ready to test the working of the same.

Note:- You can alter the value of OS_AUTHENT_PREFIX parameter and set it to null. To find out how we do that, read my post - How to set the value of OS_AUTHENT_PREFIX to NULL in Oracle Database 11g? - will be updated soon

$ su - demon
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ export PATH=$PATH:$ORACLE_HOME/bin
$ export ORACLE_SID=ORCL
$ sqlplus /
 
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 16 14:21:19 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>

Hence Successful. So this is how you enable OS Authentication for an OS user in Linux/Unix.

I hope you'll like the post.

No comments:

Post a comment