How to Use Operating System Authentication for Different User in Oracle

We normally use Linux Oracle user to authenticate for SYSDBA privilege and we don’t need to pass the username/password for it.

But how can we do it for a different we have, how can the different user can connect to Database using OS Authentication.

[oratest@GI01 ~]$ . oraenv
ORACLE_SID = [oratest] ? testdb
ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID oratest.
You can set ORACLE_BASE manually if it is required.
[oratest@GI01 ~]$
[oratest@GI01 ~]$
[oratest@GI01 ~]$
[oratest@GI01 ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 25 01:15:53 2014

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

Enter user-name: sys/sys as sysdba
ERROR:
ORA-01031: insufficient privileges

Enter user-name: ^C
[oratest@GI01 ~]$

Find out whether oratest is a part of dba group or not:

[oratest@GI01 ~]$ id -a
uid=1102(oratest) gid=1102(oratest) groups=1102(oratest) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oratest@GI01 ~]$

So oratest is not a part of dba group as of now.

Make this user as a part of dba group:

[root@GI01 ~]# usermod -G dba oratest
[root@GI01 ~]# su – oratest
[oratest@GI01 ~]$
[oratest@GI01 ~]$ id -a
uid=1102(oratest) gid=1102(oratest) groups=1102(oratest),1200(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oratest@GI01 ~]$

Now try to connect after configuring the basic Environment:

[oratest@GI01 ~]$ export ORACLE_SID=testdb
[oratest@GI01 ~]$
[oratest@GI01 ~]$ cat /etc/oratab | tail -2
dbtest:/u01/app/oracle/product/11.2.0.3/db_1:N # line added by Agent
testdb:/u01/app/oracle/product/11.2.0.3/db_1:N # line added by Agent
[oratest@GI01 ~]$
[oratest@GI01 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
[oratest@GI01 ~]$

[oratest@GI01 ~]$ export PATH=$ORACLE_HOME/bin

[oratest@GI01 ~]$
[oratest@GI01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 25 01:18:50 2014

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

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

SQL>

If this still does not work, make sure you unset TWO_TASK value if already configured. TWO_TASK stops user to connect as sysdba using OS Authentication.

Its done…

Hope it helps..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s