DBA Tips Archive for Oracle
No Title[an error occurred while processing this directive]
by Michael New, MichaelNew@earthlink.net, Gradation LLC
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:
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:
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 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.
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.
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$".
Knowing the OS authentication prefix for the instance, we can now create the database user that will allow a local OS authenticated connection.
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.
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$).
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.
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.
It is also possible to disable OS authentication all together by setting the initialization parameter os_authent_prefix to null ('').
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.
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).
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.
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:
Without this entry in the sqlnet.ora file, any connection attempt using OS authentication will fail.
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$".
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.
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.
Connect to the Oracle database as the new user using OS authentication.
See [ID 60634.1] on the My Oracle Support web site for more information on how to setup OS authentication on the Windows platform.
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:
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.
The following is a working example on how to implement external role authorization.
Set the OS_ROLES initialization parameter to true and bounce the instance. The default setting for this parameter is false.
On the Windows platform only, set the parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora to NTS.
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.
Create a new OS user (or modify an existing one) and assign the OS developer role.
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.
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:
Add any database roles or privileges to the external role.
Log in to the OS as ahunter and verify the external role is set when connecting to the instance.
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-2019 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 firstname.lastname@example.org.
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: 32507