Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Nov 17, 2009

Securing Client Connections: OS Authentication

By James Koopmann

In the last article, I quickly presented what a client-server connection was all about in regards to an Oracle database. Very simply a client is nothing more than a system or application that requires a connection to the database server to send or retrieve data. About the only requirement for an external system or application to connect to the database server is that it will need to have some form of client software loaded on it. This software could be Oracle’s client software, java classes, ODBC drivers, etc. Secondly, and just as easy, is the server side. The server component is the Oracle database.

The difficulty now becomes how can those client-server connections be properly monitored or hardened so that they do not allow unsecure connections. Operating System (OS) Authentication, while now deprecated in newer versions of Oracle but still available/allowed for backward compatibility, is often considered a tool of convenience but also regarded as a security hole. The use of OS authentication will allow users to be authenticated on the OS and then passed directly to the Oracle database without having to provide any database connection authentication. No username or password required as the OS takes care of everything. Setting up OS authentication is actually quite easy. For instance, to configure on a Linux system one need only:

1.  Check the parameter OS_AUTHENT_PREFIX.

SQL> show parameter OS_AUTHENT_PREFIX
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
os_authent_prefix                    string      

2.  If the OS_AUTHENT_PREFIX parameter is NULL, then set the parameter with the following command if using spfile:

SQL> alter system set OS_AUTHENT_PREFIX='$OPS' scope=spfile;
or assign this parameter in the init.ora file:
OS_AUTHENT_PREFIX='$OPS'

Note that this parameter requires a shutdown and startup (bounce) of the database. So if you plan to create users that will be authenticated after this is set, make sure you bounce the database. Also, you should never reset this parameter if there are currently users that have been created with another prefix as they will not be able to connect to the database.

3.  Check the parameter REMOTE_OS_AUTHENT:

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

4.  If the REMOTE_OS_AUTHENT parameter is FALSE (don’t allow OS authentication) set the parameter to TRUE with the following command if using spfile:

SQL> alter system set REMOTE_OS_AUTHENT=TRUE scope=spfile;

or assign this parameter in the init.ora file:

REMOTE_OS_AUTHENT=TRUE 

Note that this parameter requires a shutdown and startup (bounce) of the database. So if you plan on creating users that will be authenticated after this is set, make sure you bounce the database. Also, you should never reset this parameter if there are currently users that have been created with another prefix as they will not be able to connect to the database.

5.  Create an OS user:

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

6.  Create a database user:

SQL> CREATE USER ops$james IDENTIFIED EXTERNALLY;
User created.
SQL> GRANT CONNECT TO ops$james;
Grant succeeded.

7.  Test OS Authentication by connecting through SQL*Plus without entering a username or password on the SQL*Plus command line:

[oracle@ludwig ~]$ su - james
Password:
[james@ludwig ~]$ ORACLE_HOME=/opt/app/oracle/product/11.1.0/db_1
[james@ludwig ~]$ ORACLE_HOME_LISTNER=/opt/app/oracle/product/11.1.0/db_1/network/admin
[james@ludwig ~]$ ORACLE_SID=db11FS
[james@ludwig ~]$ PATH=$ORACLE_HOME/bin:$PATH
[james@ludwig ~]$
[james@ludwig ~]$ export PATH
[james@ludwig ~]$ export ORACLE_HOME
[james@ludwig ~]$ export ORACLE_HOME_LISTNER
[james@ludwig ~]$ export ORACLE_SID
[james@ludwig ~]$ sqlplus /
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Nov 11 19:29:43 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

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

It is easy to see why OS authentication is considered a security hole; if someone where to gain access to the server they can easily just issue the SQL*Plus command and get access to the database. Granted, an OS user must be defined on the OS but if they gained root access to a system they could easily start generating assumed database user names and try each one to connect to the database—a very easy task to script. On the flip-side, having the operating system authenticate a user enables some very key benefits. For instance, users have fewer passwords to maintain, which is always a good thing, streamlines the entry point for database connections and actually limits the ability of users to initiate attacks from inside the database.

Even though Oracle and many security experts agree that you should not be using OS authentication, the fact of the matter is that it is a legacy item that might still linger in many databases. For this reason, you just might want to check the DBA_USERS view to see if any passwords are handled externally.

SQL> SELECT username, password FROM dba_users; 

USERNAME             PASSWORD
-------------------- -------------------
SYS                  EBD902262A12F614
SYSTEM               50CD898E23F199FE
DBSNMP               B277673C0C180B23
SYSMAN               60F0FB2912F919CA
JAMES                EXTERNAL
OUTLN                4A3BA55E08595C81
MDSYS                72979A94BAD2AF80
ORDSYS               7EFA02EC7EA6B86F
CTXSYS               71E687F036AD56E5
SCOTT                F894844C34402B67

In addition, you might want to monitor the actual connections. You can do this by linking to the V$SESSION_CONNECT_INFO view for your monitoring queries.

SQL> SELECT sid, authentication_type, osuser 
       FROM  v$session_connect_info;

SID AUTHENTICATION_TYPE OSUSER
--- ------------------- -------
 83 DATABASE            oracle
 77 OS                  james

As the 2 Day Security Guide suggests, setting and keeping the default of FALSE for REMOTE_OS_AUTHENT is a definite way to create a more secure configuration that enforces proper server-based authentication of clients. Clearly one can restrict OS authentication by setting the REMOTE_OS_AUTHENT parameter to FALSE and be done with this security hole. However, understanding its use is, in my opinion, the beginning of understanding and locking down a database for use. Understanding the use of any option and then making a clear and informed decision is what is truly needed here, as there are definite pros and cons.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM