Securing Client Connections: OS Authentication

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:

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

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

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

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

REMOTE_OS_AUTHENT=TRUE 

[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.

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

[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