Oracle8 Enterprise Edition Getting Started
Release 8.0.4 for Windows NT
A55928-01

Library
Product
Contents
Index

Prev Next

11
Authenticating Database Users with Windows NT

This chapter describes how to authenticate Oracle8 database users with Windows NT.

Specific topics discussed are:

Authentication Overview

The Oracle8 database can use information maintained by Windows NT to authenticate database users. The benefits of Windows NT authentication include:

The Windows NT Native Authentication Adapter (automatically installed with Net8 Server and Net8 Client) enables database user authentication through Windows NT. This enables client computers to make secure connections to an Oracle8 database on a Windows NT server. A secure connection is when a Windows NT client user name is retrieved on a Windows NT server through the Windows NT Native Authentication Adapter. The Windows NT server then permits the user name to perform the database actions on the server.

The Windows NT Native Authentication Adapter provides database users with the following privileges:

Each connection privilege is described in this chapter.

Attention:

Granting database roles through Windows NT is an advanced database administration task not appropriate for all database environments. You cannot use both Windows NT and the Oracle8 database to grant roles concurrently. Select a role granting process appropriate to your database environment.

 

Note:

For Windows NT authentication to work, the SQLNET.AUTHENTICATION_SERVICES parameter must be set as follows in your ORACLE_HOME\NET80\ADMIN\SQLNET.ORA file on both client and server:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

This is the default setting after Net8 Server and Net8 Client installation.  

Connecting Without a Password as a Nonprivileged Database User

This section describes how to authenticate nonprivileged database users (nondatabase administrators) using Windows NT so that a password is not required when accessing the database. When you use Windows NT to authenticate nonprivileged database users, your database relies solely on Windows NT to restrict access to database user names. In the steps below, the following Windows NT user names are authenticated:

User Name   This User...  

Local user FRANK  

Logs into their local Windows NT client computer to access an Oracle8 database. The database can be on a different computer. To access other databases and resources on other computers, the local user must provide a user name and password each time.  

Domain user FRANK on domain SALES  

Logs into a domain (SALES in the steps below) that includes many other Windows NT computers and resources, one of which contains an Oracle8 database. The domain user can access all the resources the domain provides with a single user name and password.  

The local and domain user name FRANK and the domain SALES are used in the steps below. Substitute the appropriate local and domain user name and domain name for your environment.

Follow the steps below to connect without a password as a nonprivileged database user:

Step 1: Perform Authentication Tasks on the Oracle8 Database Server

To perform authentication tasks on an Oracle8 database server:

  1. Add the OS_AUTHENT_PREFIX parameter to your INITSID.ORA file.

    The OS_AUTHENT_PREFIX value is prefixed to local or domain user names attempting to connect to the server with the user's operating system name and password. The prefixed user name is compared with the Oracle user names in the database when a connection request is attempted.

  1. Set OS_AUTHENT_PREFIX to an appropriate value. For example:

    Set OS_AUTHENT_PREFIX to...   Result  

    XYZ  

    XYZ is prefixed to the beginning of the Windows NT user name (for example, XYZFRANK for local user FRANK or XYZSALES\FRANK for domain user FRANK on domain SALES).

    Note: XYZ is only an example of an acceptable parameter value. Use a value appropriate to your environment.  

    ""  

    This is recommended, as it eliminates the need for any prefix to the Windows NT user names (for example, FRANK for local user FRANK or SALES\FRANK for domain user FRANK on domain SALES).

     

    Not included in INITSID.ORA file  

    The value defaults to OPS$ (for example, OPS$FRANK for local user FRANK or OPS$SALES\FRANK for domain user FRANK on domain SALES).  

    The parameter value XYZ is used in the steps below. Substitute XYZ with the value you set for OS_AUTHENT_PREFIX.

  2. Use User Manager to create a Windows NT local or domain user name for FRANK (if the appropriate name does not currently exist). See your Windows NT documentation or your network administrator if you do not know how to do this.

  3. Follow the substeps below to create a new registry parameter only if you are authenticating a domain name (FRANK on domain SALES). Otherwise, go to step 5.

    1. Start the registry editor from the MS-DOS command prompt: 

      C:\> REGEDT32
    2. Go to the registry subkey of the Oracle home directory that you are using. The location in which to add this parameter is determined by how many Oracle home directories are on your machine:

      If You Have...   Go to...  

      One home directory  

      HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE  

      Additional directories  

      HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ HOMEID

      where ID is incremented for each additional Oracle home directory on your computer.  

    3. Choose the Add Value option in the Edit menu.

      The Add Value dialog box appears:

    4. Enter OSAUTH_PREFIX_DOMAIN in the Value Name field.

    5. Choose REG_EXPAND_SZ from the Data Type drop-down list box.

    6. Click OK.

      The String Editor dialog box appears:

    7. Enter TRUE in the String field to enable authentication at the domain level.

      TRUE enables the server to differentiate between multiple FRANK user names, whether they be local user FRANK, domain user FRANK on SALES, or domain user FRANK on another domain in your network. Entering FALSE causes the domain to be ignored and local user FRANK to become the default value of the operating system user returned to the server.

    8. Click OK.

      The Registry Editor adds the parameter.

    9. Choose Exit from the registry menu.

      The registry exits.

  4. Ensure that SQLNET.AUTHENTICATION_SERVICES is set as follows in your ORACLE_HOME\NET80\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)

  5. Start Server Manager: 

    C:\> SVRMGR30

  6. Connect to the database with the SYSTEM database administrator (DBA) name: 

    SVRMGR> CONNECT SYSTEM/PASSWORD

    Unless you have changed it, the SYSTEM password is MANAGER by default.

  7. Create an operating system-authenticated user by entering the following:

    If Authenticating a...   Then Enter...  

    Local user name  

    		 
    SVRMGR> CREATE USER XYZFRANK IDENTIFIED EXTERNALLY;
     

    Domain user name  

    		 
    SVRMGR> CREATE USER "XYZSALES\FRANK" IDENTIFIED 
EXTERNALLY;
     

    where:   Is the...  

    XYZ  

    Value set for the OS_AUTHENT_PREFIX initialization parameter.  

    FRANK  

    Windows NT local user name.  

    SALES\FRANK  

    Domain name and Windows NT domain user name. The double quotes are required and the entire syntax must be in uppercase.  

  8. Grant the Windows NT local user FRANK or domain user FRANK appropriate database roles:

    If Authenticating a...   Then Enter...  

    Local user name  

    		 
    SVRMGR> GRANT RESOURCE TO XYZFRANK;
     

     

    		 
    SVRMGR> GRANT CONNECT TO XYZFRANK;
     

    Domain user name1  

    		 
    SVRMGR> GRANT RESOURCE TO "XYZSALES\FRANK";
     

     

    		 
    SVRMGR> GRANT CONNECT TO "XYZSALES\FRANK";
     
    1 Enter the syntax for domain users in uppercase and with double quotes around the domain user name.

  9. Connect to the database with the INTERNAL DBA name: 

    SVRMGR> CONNECT INTERNAL/PASSWORD

  10. Shut down the database: 

    SVRMGR> SHUTDOWN

  11. Restart the database: 

    SVRMGR> STARTUP

    This causes the change to the OS_AUTHENT_PREFIX parameter value to take effect.

Step 2: Perform Authentication Tasks on the Client Computer

To perform authentication tasks on the client computer:

  1. Create Windows NT local or domain user name FRANK with the same user name and password that exist on the Windows NT server (if the appropriate name does not currently exist).

  1. Ensure that SQLNET.AUTHENTICATION_SERVICES is set as follows in your ORACLE_HOME\NET80\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)

  2. Use Net8 Assistant or Oracle Net8 Easy Config to create a network connection from your client computer to the Windows NT server on which your Oracle8 database is installed. See Chapter 6, "Configuring the Network" in Net8 Getting Started for Windows NT and Windows 95 for instructions.

  3. Access the Oracle8 database from your client computer in either of two ways:

    • Log onto a client computer with local or domain user name FRANK.
    • Access a shared area on the Windows NT computer hosting the Oracle8 database with the local or domain user name (for example, using NET USE).

  4. Start Server Manager: 

    C:\> SVRMGR30

  5. Connect to your Windows NT server: 

    SVRMGR> CONNECT /@SERVICE_NAME

    where SERVICE_NAME is the Net8 service name for the Oracle8 database that you created in Step 3.

    The Oracle8 database searches the data dictionary for an automatic login user name corresponding to the Windows NT local or domain user name, verifies it, and allows you to connect as XYZFRANK or XYZSALES\FRANK.

  6. Verify that you have connected to the Oracle8 database as local or domain user FRANK by viewing the roles assigned in Step 9 of "Step 1: Perform Authentication Tasks on the Oracle8 Database Server". 

    SVRMGR> SELECT * FROM USER_ROLE_PRIVS;

    which outputs for local user FRANK: 

    USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
XYZFRANK                       CONNECT                        NO  YES NO 
XYZFRANK                       RESOURCE                       NO  YES NO 
2 rows selected.

    or, for domain user FRANK: 

    USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
XYZSALES\FRANK                 CONNECT                        NO  YES NO 
XYZSALES\FRANK                 RESOURCE                       NO  YES NO 
2 rows selected.

    As the Oracle8 user name is the whole name XYZFRANK or XYZSALES\FRANK, all objects created by XYZFRANK or XYZSALES\FRANK (that is tables, views, indexes, etc.) are prefixed by this name. For another user to reference the table SHARK owned by XYZFRANK, for example, the user must enter: 

    SVRMGR> SELECT * FROM XYZFRANK.SHARK

    Attention:

    Automatic authorization is supported for all Net8 protocols.

     

Connecting as SYSOPER and SYSDBA Without a Password

This section describes how to enable Windows NT to grant the SYSOPER and SYSDBA privileges to DBAs. This enables DBAs to issue the following commands from a client computer and connect to the Oracle8 database without entering a password:

To enable this feature, the Windows NT local or domain user name must belong to one of the following four Windows NT local groups:

Local Group   This Local Group Includes All...  

ORA_OPER  

SYSOPER database privileges; applicable for all system identifiers (SIDs).  

ORA_DBA  

SYSDBA database privileges; applicable for all SIDs.  

ORA_SID_DBA  

SYSDBA database privileges; applicable only for the SID specified in the name.  

ORA_SID_OPER  

SYSOPER database privileges; applicable only for the SID specified in the name.  

The SYSOPER and SYSDBA privileges are mapped to the following Windows NT local groups:

This Privilege...   Maps to the Local Group...  

SYSOPER  

ORA_SID_OPER, ORA_OPER  

SYSDBA  

ORA_SID_DBA, ORA_DBA, ORA_SID_OPER, ORA_OPER  

Follow the steps below to connect as SYSOPER or SYSDBA without a password:

Step 1: Perform Authentication Tasks on the Oracle8 Database Server

To perform authentication tasks on the Oracle8 database server:

  1. Open User Manager on the Windows NT server where your Oracle8 database is installed.

  1. Select New Local Group from the User Menu.

    The New Local Group dialog box appears.

  2. Enter the appropriate Windows NT local group name in the Group Name field. For this example, the SID entered is ORCL.

  3. Click Add.

    The Add Users and Groups dialog box appears:

  4. Select an appropriate Windows NT user from the Names field and click Add.

  5. Click OK.

    Your selection is added to the Members field of the New Local Group dialog box:

  6. Click OK.

  7. Exit User Manager.

  8. Ensure that SQLNET.AUTHENTICATION_SERVICES is set as follows in your ORACLE_HOME\NET80\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)

Step 2: Perform Authentication Tasks on the Client Computer

To perform authentication tasks on the client computer:

  1. Create a Windows NT local or domain user name with the same user name and password that exist on the Windows NT server (if the appropriate user name does not currently exist).

  1. Ensure that SQLNET.AUTHENTICATION_SERVICES is set as follows in your ORACLE_HOME\NET80\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)

  2. Use Net8 Assistant or Oracle Net8 Easy Config to create a network connection from your client computer to the Windows NT server on which your Oracle8 database is installed. See Chapter 6, "Configuring the Network" in Net8 Getting Started for Windows NT and Windows 95 for instructions.

  3. Access the Oracle8 database in either of two ways:

    • Log onto a client computer with the local or domain user name.
    • Access a shared area on the Windows NT computer hosting the Oracle8 database with the local or domain user name (for example, using NET USE).

  4. Start Server Manager: 

    C:\> SVRMGR30

  5. Connect to the Oracle8 database: 

    SVRMGR> SET INSTANCE SERVICE_NAME

    where SERVICE_NAME is the Net8 service name for the Oracle8 database that you created in Step 3.

  6. Connect as SYSOPER or SYSDBA based on the local group you specified in step 3 of "Step 1: Perform Authentication Tasks on the Oracle8 Database Server":

    If The Local Group Is...   Then Enter...  

    ORA_DBA or ORA_SID_DBA  

    SVRMGR> CONNECT / AS SYSOPER  

     

    or  

     

    SVRMGR> CONNECT / AS SYSDBA  

    ORA_OPER or ORA_SID_OPER  

    SVRMGR> CONNECT / AS SYSOPER  

    You are connected to the Windows NT server. If you connect with SYSDBA, you are given DBA privileges.

Connecting as INTERNAL Without a Password

This section describes how to connect as INTERNAL without a password. To do this, you must create one of the following new local Windows NT user groups and add a Windows NT operating system local or domain user to that group:

Local Group   This Local Group Includes All...  

ORA_DBA  

SYSDBA database privileges. This group is applicable for all SIDs.  

ORA_SID_DBA  

SYSDBA database privileges. This group is applicable only for the SID specified in the name.  

This enables you to log into a local computer or a Windows NT domain. In the domain, your Oracle8 database is just one of many resources to which you have access. Once you access this domain, you are automatically validated as an authorized DBA who can access the Oracle8 database without a password.

Note:

The Oracle8 database does not support setting the registry DBA_AUTHORIZATION parameter to the value of BYPASS. Server Manager uses the value of DBA_AUTHORIZATION as a password for INTERNAL to retry a failed login attempt when no password is provided.

 

Follow the steps below to connect as INTERNAL without a password:

Step 1: Perform Authentication Tasks on the Oracle8 Database Server

To perform authentication tasks on the Oracle8 database server:

  1. Create a Windows NT user name (local or domain) if one does not already exist.

  1. Set the INITSID.ORA file REMOTE_LOGIN_PASSWORDFILE parameter to NONE to enable operating system authenticated logins for the INTERNAL user name.

  2. Ensure that SQLNET.AUTHENTICATION_SERVICES is set as follows in your ORACLE_HOME\NET80\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)

  3. Open User Manager.

  4. Go to New Local Group from the User Menu.

    The New Local Group dialog box appears.

  5. Enter the ORA_SID_DBA or ORA_DBA Windows NT local group name in the Group Name field. For this example, the SID entered is ORCL:

  6. Click Add.

    The Add Users and Groups dialog box appears:

  7. Select an appropriate Windows NT local or domain user from the Names field and click Add.

  8. Click OK.

    Your selection is added to the Members field of the New Local Group dialog box:

  9. Click OK.

  10. Exit User Manager.

  11. Connect to the database with the INTERNAL DBA name: 

    SVRMGR> CONNECT INTERNAL/PASSWORD

  12. Shut down the database: 

    SVRMGR> SHUTDOWN

  13. Restart the database: 

    SVRMGR> STARTUP

Step 2: Perform Authentication Tasks on the Client Computer

To perform authentication tasks on the client computer:

  1. Create a Windows NT local or domain user name with the same user name and password that exist on the Windows NT server (if the appropriate user name does not currently exist).

  1. Ensure that SQLNET.AUTHENTICATION_SERVICES is set as follows in your ORACLE_HOME\NET80\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)

  2. Use Net8 Assistant or Oracle Net8 Easy Config to create a network connection from your client computer to your Oracle8 database. See Chapter 6, "Configuring the Network" in Net8 Getting Started for Windows NT and Windows 95 for instructions.

  3. Access the Oracle8 database in either of two ways:

    • Log onto a client computer with a local or domain user name.
    • Access a shared area on the Windows NT computer hosting the Oracle8 database with the local or domain user name (for example, using NET USE).

  4. Start Server Manager: 

    C:\> SVRMGR30

  5. Connect to the Oracle8 database: 

    SVRMGR> SET INSTANCE SERVICE_NAME

    where SERVICE_NAME is the Net8 service name for the Oracle8 database that you created in Step 3.

  6. Connect to your Windows NT server: 

    SVRMGR> CONNECT INTERNAL

    You are connected to the Windows NT server.

Granting Database Roles through Windows NT

This section describes how to grant Oracle8 database roles to users directly through Windows NT. When you use Windows NT to authenticate users, Windows NT local groups can grant these users database roles. Through User Manager, you can create, grant, or revoke database roles to users.

Note:

All privileges for these roles are active when the user connects. When using operating system roles, all roles are granted and managed through the operating system. You cannot use both operating system roles and Oracle roles at the same time.  

Follow the steps below to grant database roles with Windows NT:

Step 1: Perform Authentication Tasks on the Oracle8 Database Server

To perform authentication tasks on the Oracle8 database server:

  1. Add the OS_ROLES initialization parameter to the INITSID.ORA file.

  1. Set OS_ROLES to TRUE.

    The default setting for this parameter is FALSE.

  2. Ensure that SQLNET.AUTHENTICATION_SERVICES is set as follows in your ORACLE_HOME\NET80\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)

  3. Create a new database role: 

    SVRMGR> CREATE ROLE DBSALES3 IDENTIFIED EXTERNALLY;

    where DBSALES3 is the name of the role for these steps. Substitute a role name appropriate to your database environment.

  4. Grant Oracle roles to DBSALES3 that are appropriate to your database environment: 

    SVRMGR> GRANT DBA TO DBSALES3 WITH ADMIN OPTION;
SVRMGR> GRANT RESOURCE TO DBSALES3 WITH ADMIN OPTION;
SVRMGR> GRANT CONNECT TO DBSALES3 WITH ADMIN OPTION;

  5. Connect to the database with the INTERNAL DBA name: 

    SVRMGR> CONNECT INTERNAL/PASSWORD

  6. Shut down the database: 

    SVRMGR> SHUTDOWN

  7. Restart the database: 

    SVRMGR> STARTUP

  8. Open the Windows NT User Manager.

  9. Choose New Local Group from the User menu.

    The New Local Group dialog box appears:

  10. Enter the Windows NT local group name corresponding to the database role in the Group Name field with the following syntax: 

    ORA_SID_ROLENAME [_D] [_A]

    where:

    SID  

    Indicates the database instance.  

    ROLENAME  

    Identifies the database role granted to users of a database session.  

    D  

    Optional character indicating that this database role is to be the default role of the database user. If specified, this character must be preceded by an underscore.  

    A  

    Optional character indicating that this database role includes the ADMIN OPTION. This enables the user to grant the role to other roles only. If specified, this character must be preceded by an underscore.  

    For this example, ORA_ORCL_DBSALES3_D is entered.

  11. Click Add.

    The Add Users and Groups dialog box appears:

  12. Select the appropriate Windows NT local or domain user name and click Add.

  13. Click OK.

    Your selection is added to the Members field of the New Local Group dialog box:

    You can convert additional database roles to several possible Windows NT groups, as shown in the following table. Then, users connecting to the ORCL instance in this example and authenticated by Windows NT as members of these Windows NT local groups have the privileges associated with DBSALES3 and DBSALES4 by default (because of the _D option). DBSALES1 and DBSALES2 are available for use by the user if they first connect as members of DBSALES3 or DBSALES4 and use the SET ROLE command. If a user tries to connect with DBSALES1 or DBSALES2_A without first connecting with a default role, they are unable to connect. Additionally, users can grant DBSALES2 and DBSALES4 to other roles.

    Database Roles   Windows NT Groups  

    DBSALES1  

    ORA_ORCL_DBSALES1  

    DBSALES2  

    ORA_ORCL_DBSALES2_A  

    DBSALES3  

    ORA_ORCL_DBSALES3_D  

    DBSALES4  

    ORA_ORCL_DBSALES4_DA  

    Note:

    When the Oracle8 database converts the group name to a role name, it changes the name to uppercase.  

  14. Click OK.

  15. Exit User Manager.

Step 2: Perform Authentication Tasks on the Client Computer

To perform authentication tasks on the client computer:

  1. Create a Windows NT local or domain user name with the same user name and password that exist on the Windows NT server (if the appropriate user name does not currently exist).

  1. Ensure that SQLNET.AUTHENTICATION_SERVICES is set as follows in your ORACLE_HOME\NET80\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)

  2. Use Net8 Assistant or Oracle Net8 Easy Config to create a network connection from your client computer to your Oracle8 database. See Chapter 6, "Configuring the Network" in Net8 Getting Started for Windows NT and Windows 95 for instructions.

  3. Access the Oracle8 database in either of two ways:

    • Log onto a client computer with a local or domain user name.
    • Access a shared area on the Windows NT computer hosting the Oracle8 database with the local or domain user name (for example, using NET USE).

  4. Start Server Manager: 

    C:\> SVRMGR30

  5. Connect to the correct instance: 

    SVRMGR> SET INSTANCE SERVICE_NAME

    where SERVICE_NAME is the Net8 service name for the Oracle8 database that you created in Step 3.

  6. Connect to the Oracle8 database: 

    SVRMGR> CONNECT SCOTT/TIGER

    You are connected to the Windows NT server over Net8 with the Oracle user name SCOTT/TIGER. The roles applied to the Oracle user name SCOTT consist of all roles defined for the Windows NT user name that were mapped to the database roles above (in this case, ORA_DBSALES3_D). All roles available under an authenticated connection are determined by the Windows NT user ID and the Oracle-specific Windows NT local groups to which the user belongs (for example, ORA_SID_DBSALES1 or ORA_SID_DBSALES4_DA).


Prev
Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index