DBA Tips Archive for Oracle

  


Oracle OS Authentication

by Jeff Hunter, Sr. Database Administrator

Contents

Introduction

The most common method for logging in to an Oracle database is by username and password. A database user is created by using the CREATE USER statement as follows:


CREATE USER username IDENTIFIED BY password;

This creates a database user, associated with a user schema, who can access the database and be authenticated by using a password with the CONNECT command as follows:


CONNECT username@database_service_name Enter Password: password

This guide provides an alternative to the username / password method by using OS Authentication. OS authentication allows Oracle to delegate control of user authentication for the database to the operating system. Although OS authentication has been widely used over the years, there are potential security risks to be aware of before implementing it in a production environment. In fact, when using Oracle Database 10g Release 2 or higher, a better and more secure approach uses a client-side Oracle Wallet which is a password store external to the database used to store Oracle login credentials.

OS authentication is a method which identifies users by the credentials supplied by the OS and then uses that information to allow authentication to the database without a password. These credentials can be the username and password supplied to the OS or digital certificates on the user's computer. A password is not required for a database connection because it is assumed the OS has already taken care of authenticating the user. One thing to keep in mind though is that database connections relying on OS authentication are only be as secure as the underlying OS.

This guide presents instructions on how to configure Oracle for OS authentication along with several examples on how to use it.

Local OS Authentication

Local OS authentication is performed using OS credentials on the same server where the database resides. Local OS authentication is enabled by default and is commonly used for administration purposes. Remote OS authentication, on the other hand, allows remote users to access the database using the OS credentials of their client machine or another server. Because of the considerable vulnerability and extreme security risk that remote OS authentication can impose, it is a highly discouraged method and rarely ever used in an experienced DBA shop. I will discuss remote OS authentication briefly in the next section.

Create Local OS User

As previously mentioned, local OS authentication is configured to work out of the box and can be implemented by creating a local OS user and an associated database user that is able to authenticate externally to the database. For example, to create login credentials for the user jhunter, start by creating an OS user on the local database server. In a UNIX/Linux environment, this is accomplished using the useradd and passwd utility.


# useradd jhunter # passwd jhunter Changing password for user jhunter. New password: ************ Retype new password: ************ passwd: all authentication tokens updated successfully.

OS Authentication Prefix

The next step is to create a database user that can identify externally to the database. This is done by adding the correct "OS authentication prefix" to the desired username and specifying the IDENTIFIED EXTERNALLY clause to the CREATE USER command. To determine the OS authentication prefix, check the value of the OS_AUTHENT_PREFIX initialization parameter. The default value is "OPS$".


SQL> show parameter os_authent_prefix NAME TYPE VALUE ------------------------------------ ----------- ------------------------- os_authent_prefix string ops$

Create Database User

Knowing the OS authentication prefix for the instance, we can now create the database user that will allow a local OS authenticated connection.


SQL> create user ops$jhunter identified externally; User created. SQL> grant create session to ops$jhunter; Grant succeeded.

Connect to the Oracle database as the new user using OS authentication. You may need to set a few environment variables for your session so that SQL*Plus will work (depending on whether or not the correct Oracle environment variables are set in the shell login script). Under UNIX/Linux you would expect to set the following.


# su - jhunter $ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 $ export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:/lib:/usr/lib:/usr/local/lib $ export PATH=${PATH}:${ORACLE_HOME}/bin $ export ORACLE_SID=testdb1 $ sqlplus / SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 11 21:26:18 2012 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, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "OPS$JHUNTER"

Mixing OS Authentication with Password Authentication

Some users may be unaware of a potential security risk with OS authentication. Consider the following example where the DBA creates a user able to authenticate with a password while the initialization parameter os_authent_prefix is set to its default value (OPS$).


SQL> create user ops$jhunter identified by mypassword; User created. SQL> grant create session to ops$jhunter; Grant succeeded.

A database user named OPS$JHUNTER will be created and because the OS authentication prefix is OPS$, the user is able to log in to the database using OS authentication. However, because a password was specified, this user can also log in with a password.


$ sqlplus 'ops$jhunter/mypassword' SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 11 21:43:57 2012 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, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "OPS$JHUNTER" SQL> exit $ sqlplus / SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 11 21:45:33 2012 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, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "OPS$JHUNTER"

This can add confusion to the method OPS$JHUNTER would use to log in to the database but more importantly, it could pose a potential security risk. Here's why. In almost all cases, the intent on creating an OS authenticated database user is to hide passwords in order to tighten security. If the database user now has the ability to supply a password, that leaves a hole in the security policy and defeats the purpose of OS authentication when it is being used to harden database security. Although we may trust OPS$JHUNTER to do the right thing, there is no way now to prevent him from logging in to the database and creating scripts that expose the password. A generally accepted practice is to create OS authenticated users without the IDENTIFIED BY clause (without a password). This way you know the account will not have the ability to expose the database password.

Disable OS Authentication

It is also possible to disable OS authentication all together by setting the initialization parameter os_authent_prefix to null ('').


SQL> alter system set os_authent_prefix='' scope=spfile; System altered. SQL> startup force ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2228904 bytes Variable Size 1275071832 bytes Database Buffers 369098752 bytes Redo Buffers 7118848 bytes Database mounted. Database opened. SQL> !id uid=1505(jhunter) gid=1505(jhunter) groups=1505(jhunter),502(dba),503(oper) SQL> connect / ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL>

Remote OS Authentication

In the previous section we discussed local OS authentication where OS user accounts are created on the database server allowing connections to a local database on that machine without the need for a password. Remote OS authentication (as the name implies) allows remote users to access the database using the OS credentials of their client machine or another server. If the thought of this practice makes the hairs on the back of your neck to stand up, then you are in the majority of security minded and experienced DBAs that would never consider its implementation — especially in a production environment.

Suffice it to say that remote OS authentication is not enabled by default. In fact, the use of remote OS authentication was deprecated in Oracle Database 11g Release 1 (11.1), and is retained for backward compatibility only. See [ID 456001.1] on the My Oracle Support web site.

Consider for example a database named erpdb running on server erpprod and a client logged in to their PC as user SCOTT. Now consider a database user that was created to log in to the database using OS authentication (identified externally) named OPS$SCOTT. With remote OS authentication enabled, user SCOTT would be able to connect to the production database from his PC (remotely) without a password.


sqlplus /@erpdb

Notice the inherent security hole that remote OS authentication imposes. How easy would it be for a user with knowledge that OPS$SCOTT existed in the production database to create a SCOTT user on his or her client machine? Unless other precautions were put in place (network security), nothing would stop this user from creating an OS user on an unsecured machine and log in to the databases without use of a password.

Although remote OS authentication is strongly discouraged and rarely enabled, there are certain conditions where methods could be put in place to reduce the risk of rogue logins. For example, if the database server can be put on a network completely isolated from normal client access and the clients able to access the isolated network are secured well enough to be trusted (DBAs, sysadmins, etc.). Under the assumption that the network is 100% secure and that clients with access to the secured portion of the network can be trusted, it may be permissible to use remote OS authentication.

Unless you can show a real need that warrants the use of remote OS authentication and can accept the inherit risks involved with using it, this feature should remain disabled by ensuring the initialization parameter remote_os_authent is set to false (its default value).


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

Windows Domain Authorization

Oracle OS authentication on the Windows platform is not much different than with UNIX/Linux. OS users in Windows can be created as local users (Start > All Programs > Administrative Tools > Computer Management) or as domain users created in Active Directory. Although it is possible to have a local OS user account on the Windows database server, most organizations require all users and computers (including database servers) to be members of a domain. As a domain member, when authenticating to the database server, users must join the domain and supply their domain username and password; not the local user.

Add sqlnet.ora Entry

A default Oracle installation on the Windows platform will create the sqlnet.ora file in the %ORACLE_HOME%\network\admin folder. Although there may be several entries contained in this file, the following entry must exist in order to connect using OS authentication:


SQLNET.AUTHENTICATION_SERVICES = (NTS)

Without this entry in the sqlnet.ora file, any connection attempt using OS authentication will fail.

OS Authentication Prefix

Just as we needed to do with UNIX/Linux, we need to identify the "OS authentication prefix" for the instance. This can be determined by checking the value of the OS_AUTHENT_PREFIX initialization parameter. The default value is "OPS$".


SQL> show parameter os_authent_prefix NAME TYPE VALUE ------------------------------------ ----------- ------------------------- os_authent_prefix string OPS$

Create Database User

Once we have determined the OS authentication prefix, we need to identify if the user is being authenticated as a domain user or the local machine. The Windows username will be of the form USERDOMAIN\USERNAME or COMPUTERNAME\USERNAME. In this particular example, I am authenticated to the database server as a domain user.


W:\>set u USERDNSDOMAIN=IDEVELOPMENT.INFO USERDOMAIN=IDEVELOPMENT USERNAME=jhunter USERPROFILE=C:\Users\jhunter W:\>echo %USERDOMAIN%\%USERNAME% IDEVELOPMENT\jhunter

When creating the database user, keep in mind that the domain or machine name will form part of the username presented to Oracle. When I authenticate to the database server as a domain account, the OS user will be presented to Oracle as OPS$IDEVELOPMENT\JHUNTER, not as OPS$JHUNTER. Note how the OS authentication prefix and domain name will need to be part of the user's database name. If I were authenticated locally to machine and not through a domain, the OS user would be presented as COMPUTERNAME\JHUNTER.

Knowing that the value of the OS authentication prefix (the os_authent_prefix parameter) is "OPS$" and that my Windows username is "IDEVELOPMENT\JHUNTER", a database user named "OPS$IDEVELOPMENT\JHUNTER" will need to be created. Note that the database username must be uppercase and enclosed by double quotes.


SQL> create user "OPS$IDEVELOPMENT\JHUNTER" identified externally; User created. SQL> grant create session to "OPS$IDEVELOPMENT\JHUNTER"; Grant succeeded.

Connect to the Oracle database as the new user using OS authentication.


W:\>whoami idevelopment\jhunter W:\>sqlplus / SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 16 21:26:56 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "OPS$IDEVELOPMENT\JHUNTER"

See [ID 60634.1] on the My Oracle Support web site for more information on how to setup OS authentication on the Windows platform.

External Role Authorization

Similar to the way Oracle is able to rely on the OS for user authentication to the database, it is also possible to delegate authorization in Oracle through the use of external role authorization. This is accomplished by setting the os_roles initialization parameter to true and creating external roles. When external role authorization is enabled for an instance, the OS completely manages the role grants for all database usernames through the use of external roles. Based on the OS group the user belongs to, the user's role inside the database will be set accordingly. A user can then subsequently enable as many roles identified by the OS as specified by the initialization parameter max_enabled_roles.

By default, os_roles is set to false. You must set os_roles to true and restart the Oracle instance before you can create external roles. If os_roles is set to false, the Oracle database manages granting and revoking of roles for database users.

External roles are defined at the operating system. A role should have the prefix ora_ followed by the ORACLE_SID and the name of the role. The role can also have the suffix d (if it is a default role) and/or a (if the user has the ADMIN OPTION to the role). For example, a database role in the testdb1 database named developer (default role and with the ADMIN OPTION) under Linux would have an entry in /etc/group similar to:


ora_testdb1_developer_da:x:1506:ahunter,hhunter

Revoking a role granted by the OS is ignored by Oracle, as are any local roles previously granted by Oracle. Also note that in order to prevent possible breaches of security, a default role cannot be authenticated at the operating system level when Shared Server (a.k.a. Multi-Threaded Server) is configured. The only method to enable external role authorization in shared server mode is to set remote_os_roles to true which as you can image, opens up an entirely new set of possibilities to breach security. (See Remote OS Authentication for more information on the inherit security risks involved when allowing remote security policies.)

If os_roles is set to true, you cannot grant local roles in the database to any database user. Once you enable external role authorization, you must grant roles through the OS using external roles. You cannot use both external role authorization and the Oracle database to grant roles concurrently.

External Role Authorization Example

The following is a working example on how to implement external role authorization.

  1. Set the OS_ROLES initialization parameter to true and bounce the instance. The default setting for this parameter is false.


    SQL> alter system set os_roles=true scope=spfile; System altered. SQL> startup force; ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2228904 bytes Variable Size 1275071832 bytes Database Buffers 369098752 bytes Redo Buffers 7118848 bytes Database mounted. Database opened.

  2. On the Windows platform only, set the parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora to NTS.


    SQLNET.AUTHENTICATION_SERVICES= (NTS)

  3. Create an operating system role that will be used as an external role to the database. In this example, a default database role named developer will be created for the testdb1 instance.


    # groupadd ora_testdb1_developer_d

  4. Create a new OS user (or modify an existing one) and assign the OS developer role.


    # useradd -G ora_testdb1_developer_d ahunter # echo " > export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 > export LD_LIBRARY_PATH=\${ORACLE_HOME}/lib:/lib:/usr/lib:/usr/local/lib > export PATH=\${PATH}:\${ORACLE_HOME}/bin > export ORACLE_SID=testdb1" >> ~ahunter/.bash_profile

  5. Connect to the database as a DBA user and create a new database user and external role. For the purpose of this example, the role is named developer.


    SQL> create user ahunter identified by somepassword; User created. SQL> create role developer identified externally; Role created.

  6. Remember that the instance is enable with external role authorization and cannot be used to grant roles. All roles must be granted through the OS. Attempting to manually grant the new role to ahunter will result in the following error:


    SQL> grant developer to ahunter; grant developer to ahunter * ERROR at line 1: ORA-01956: invalid command when OS_ROLES are being used

  7. Add any database roles or privileges to the external role.


    SQL> grant create session to developer; Grant succeeded. SQL> grant create table to developer; Grant succeeded. SQL> grant create procedure to developer; Grant succeeded. SQL> alter user ahunter quota unlimited on users; User altered.

  8. Log in to the OS as ahunter and verify the external role is set when connecting to the instance.


    # su - ahunter $ id uid=1506(ahunter) gid=1507(ahunter) groups=1507(ahunter),1506(ora_testdb1_developer_d) $ sqlplus ahunter/somepassword SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 16 09:43:02 2012 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, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> select * from session_roles; ROLE ------------------------------ DEVELOPER

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 04-Sep-2012 00:24:08 EDT
Page Count: 26654