Implementing Windows Authentication for Oracle

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:

1. Confirm the existence of or create the necessary Windows groups (e.g., ORA_DBA, ORA_SID_DBA, ORA_OPER, ORA_SID_OPER) needed for the required level of access to your Oracle database server machine.
2. Add Windows users to the appropriate groups.
3. Ensure that SQLNET.AUTHENTICATION_SERVICES uses Windows authentication (i.e., NTS) for both client and server authentication.

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 PENTON\WinUser. For Oracle to authorize this Windows user in the Oracle database, we must create a PENTON\WinUser 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 "PENTON\WINUSER" identified externally;
grant create session to "PENTON\WINUSER";

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.