Implementing Windows Authentication for Oracle

As a matter of practice, the database server typically stores the passwords needed to access an Oracle database. Although this system is convenient for the DBA, relying on passwords kept on the database server has several disadvantages. For example, if you forget a password and need to reset it, the DBA must intervene. Also, synchronization of Windows passwords and Oracle database passwords is strictly a manual process. In contrast, Microsoft SQL Server's integrated security feature lets you use Windows usernames and passwords to secure database access. With this approach, when users need to have their passwords reset, the SQL Server DBA can delegate this task to Help desk personnel.

Many people don't realize that you can configure Oracle database servers to use OS authentication (aka external authentication in Oracle), which is similar to SQL Server's integrated authentication. Before you can use Windows authentication with Oracle, you need to have a thorough understanding of the security implications of doing so. Because the implementation details for authorizing Oracle users logged on to the Oracle server are quite different from the details for authorizing Oracle users logged on to remote clients, I look at both scenarios in this article.

Database Server Windows Group Authentication
When you install Oracle on a Windows server, the system creates an ORA_DBA Windows group and automatically adds to that group the Windows account used to install Oracle. The DBA can then add to the group other Windows users who need full Oracle DBA privileges. But be careful—Windows local and domain users within the ORA_DBA group don't have to supply an Oracle username and password. As the Description property for the ORA_DBA group says, Members can connect to the Oracle database as a DBA without a password.

For Oracle to accept users in the ORA_DBA group as authenticated users, you need to properly configure the sqlnet.ora file, which Figure 1 shows. For Oracle9i and Oracle8i, this file is in the %ORACLE_HOME%etworkadmin folder, where %ORACLE_HOME% represents the path used to install the Oracle server software. The sqlnet.ora file lets you configure how connections to the Oracle server will be made.

The NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file specifies the methods that Oracle clients use to resolve the database connection string name alias. For example, when I type at the command line

sqlplus /@test9

the SQL*Plus utility attempts to resolve the test9 alias by using the NAMES.DIRECTORY_PATH entries in the sqlnet.ora file. (For a description of the SQL*Plus tool and information about obtaining the tool, see the "Manipulate Oracle with SQL*Plus" sidebar.) In the sample sqlnet.ora file that Figure 1 shows, the client first attempts Oracle name resolution by using a tnsnames.ora text file, which can reside either locally or on a shared network resource. If the tnsnames.ora file doesn't contain the name, the client will attempt to resolve the name by using an Oracle Names server (Oracle now recommends using Lightweight Directory Access Protocol—LDAP—instead of Oracle Names servers). Finally, the client tries to resolve the name by using a host-name resolution method such as DNS or Network Information Service (NIS).

The SQLNET.AUTHENTICATION_SERVICES parameter in the sqlnet.ora file specifies which authentication service Oracle should use when a user attempts to connect to the Oracle server. By default, Oracle9i and Oracle8i enable Windows authentication by means of the following setting:

SQLNET.AUTHENTICATION_SERVICES=(NTS)

Windows NT always uses NT LAN Manager (NTLM) authentication. Windows Server 2003, Windows XP, and Windows 2000 all use Kerberos authentication when the Oracle client machine is in a Windows 2003 or Win2K domain; otherwise, they use NTLM authentication. The default setting of enforcing Windows authentication isn't compatible with applications that use standard Oracle authentication. And many third-party vendors have applications that use standard Oracle usernames and passwords to connect to Oracle. To support both Oracle and Windows authentication, you can change the authentication service parameter in the server's sqlnet.ora file as follows:

SQLNET.AUTHENTICATION_SERVICES= (NONE,NTS)

Any changes that you make to the authentication methods can result in connection failures. To detect any such failures, whenever you change the authentication service parameter, use SQL*Plus first to perform basic connectivity testing, then test your Oracle client applications.

Because the ORA_DBA group is a Windows group, the Oracle database server uses it only when SQLNET.AUTHENTICATION_SERVICES is using Windows authentication. For example, if Windows authentication is enabled and I go to the command line and type

set oracle_sid=test9sqlplus "/ as sysdba"

I can create a SYSDBA privileged connection without supplying an Oracle username and password.

The ORACLE_SID value shown in our example in the first command line (i.e., test9) identifies the database connection string alias for sqlplus.exe to use to connect to an Oracle database instance. The second command line specifies the authentication credentials. Double quotes are required for SQL*Plus to interpret the entire connect string, including the spaces, as one command-line parameter. The syntax " as sysdba" specifies that the client would like to connect to the Oracle database as the currently logged on Windows user with SYSDBA privileges. Upon entering both commands on my Oracle client machine, the system returned the results that Figure 2 shows. If an Oracle username and password are supplied to SQL*Plus when connecting as SYSDBA, SQL*Plus ignores them. This action isn't a security breach because the Oracle server has authenticated the Windows credentials and not the Oracle credentials.

Membership in the ORA_DBA group gives a user SYSDBA privileges to all Oracle instances on the server because the ORA_DBA Windows group maps to the Oracle SYSDBA role. The SYSDBA role is equivalent to SQL Server's systems administrator (sa) role. If you want to get more granular, you can create separate groups of the general format ORA_SID_DBA, where SID is the uppercase Oracle SID, to grant SYSDBA privileges on a per-instance basis instead of on a per-server basis. For example, in the previous session, the SID is test9, which means you can create a group named ORA_TEST9_DBA. Then, any Windows users that you add to the ORA_TEST9_DBA group but not to the ORA_DBA group will have SYSDBA privileges only for the Oracle TEST9 database instance.

Similarly, you can use membership in the ORA_OPER and ORA_SID_OPER groups, which map to the Oracle SYSOPER role, to grant SYSOPER privileges to specific Windows users. (SYSOPER has a restricted subset of the SYSDBA privileges, similar to SQL Server's db_backupoperator role.)

In summary, to use Windows authentication with privileged authorization (i.e., SYSDBA, SYSOPER) to enable access to Oracle, perform the following steps:

Oracle provides a GUI, which Figure 3 shows, for adding users to the ORA_DBA and ORA_OPER groups. If the groups don't exist, you can use the GUI to create them. To access the GUI, from the Start button, navigate to All Programs, Oracle - OraHome92, Configuration and Migration Tools, Oracle Administration Assistant for Windows NT. To add a user to the ORA_OPER Windows group, right-click the OS Database Operators - Computer node and select Add/Remove from the context menu. When the OS Database Operators dialog box appears, select the domain, select the user, click Add, then click OK. The system creates the ORA_OPER group if it didn't previously exist and adds the selected user to the group.

One cautionary note when using Windows authentication: If at any point in the future you need to recreate the Oracle password file (in the %ORACLE_HOME%database folder), refer to the Oracle documentation and check the value of REMOTE_LOGIN_PASSWORD in the init.ora file. As the Oracle9i Database Administrator's Guide explains, the REMOTE_LOGIN_PASSWORD value affects how Oracle authentication works, which in turn can impact applications that use Oracle authentication.

Server Windows Authentication Without a Group
What if the DBA is logged on to the database server but chooses to connect to Oracle with less than full SYSDBA privileges? This prudent approach of using least privilege can minimize the damage that might be caused if the DBA makes a mistake. For our example, let's assume that a Windows user named WinUser in the PENTON domain logged on to the Windows server hosting Oracle. Notice that with a default installation, the same Windows user who connected as SYSDBA can't connect with lesser privileges. For example, if I type

sqlplus /

the system will return the results that Figure 4 shows. The reason for the failure is that the client is no longer attempting to connect to the Oracle database through membership in the ORA_DBA Windows group. Consequently, the Windows user isn't automatically mapped to an Oracle role through Windows group membership and, therefore, the user isn't authorized in Oracle. Because we're not using group membership to authenticate the user, the actual Windows user, WinUser, is being passed to Oracle and needs Oracle authorization. Oracle will authorize a Windows user only when that user matches an Oracle user. In our example, the user's Fully Qualified Domain Name (FQDN) is PENTONWinUser. For Oracle to authorize this Windows user in the Oracle database, we must create a PENTONWinUser Oracle user. When a Windows user matches an Oracle user, the privileges granted to the Windows user are the same as the privileges granted to the Oracle user. The syntax for creating the Oracle user requires that the FQDN be in all uppercase and inside double quotes, as the example below shows. Using SQL*Plus or another favorite client tool, we can connect to the Oracle database with SYSDBA privileges and execute the following commands:

create user "PENTONWINUSER" identified externally;grant create session to "PENTONWINUSER";

An Oracle parameter exists that affects how Oracle matches a Windows username to an Oracle username when you're not using Windows group membership. Early versions of Oracle used a prefix of OPS$, which you would append to the beginning of the Oracle username used in external authentication. Because Oracle usernames are limited to 30 characters, using an OPS$ prefix effectively limited the username to the remaining 26 characters. To avoid using the OPS$ prefix, the Oracle database parameter file, the init.ora file (in the %ORACLE_HOME%database folder), should have the following setting (default Oracle9i and Oracle8i installations are configured to include this setting):

os_authent_prefix = ""

The parameter is provided for backward compatibility. Oracle doesn't recommend adding a prefix, thus the default empty setting is as shown. For a change to the OS_AUTHENT_PREFIX setting to take effect, you must shut down and restart the Oracle database instance.

After the Windows user has a matching Oracle username, Windows can authenticate that user and the user can establish a connection to the Oracle database. A similar technique is used to authenticate remote clients.

Remote Client Windows Authentication
Windows clients that use Windows authentication to access a remote Oracle server on the network aren't actually authenticated by the OS on that server. Instead, the client OS authenticates these users. To enable remote authentication, add the following entry to the init.ora file for the database instance, then shut down and restart the database:

REMOTE_OS_AUTHENT=TRUE

Oracle doesn't recommend using remote authentication because it doesn't provide protection against spoofing of user credentials. For example, imagine you have a valid Windows user in the PENTON domain named WinUser, you create an Oracle user on the server by using the following syntax, and you enable remote authentication:

create user "PENTONWINUSER" identified externally;grant create session to "PENTONWINUSER";

Now, consider what might happen if a rogue machine named PENTON connects to your network. The attacker could create a local Windows user named WinUser on the rogue machine, which would authenticate as PENTONWinUser. This user could then be passed to the Oracle server on the network as PENTONWinUser. The Oracle server wouldn't be able to distinguish between the domain name of PENTON and the rogue machine name of PENTON, so the server would accept the remote authentication from the rogue machine. The Oracle server just sees that PENTONWinUser is the user, so it authorizes the user with all the privileges of PENTONWinUser. If unsecured client machines can gain access to your network, then remote Windows authentication opens your database environment to unauthorized access.

A Familiar Model
Windows authentication on the Oracle database server is easily implemented and provides convenient access to authorized users logged on to the database server. Because Windows handles the password management, Windows authentication follows a model that's familiar to network and systems administrators. However, keep in mind that this type of authentication isn't suitable for remote users because of the security risks involved.