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.
