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%\network\admin 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=test9
sqlplus "/ 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.

   Previous  [1]  2  3  Next