2
Administering External Users and Roles

External users and roles are in general defined by something external to Oracle9i database. In a Windows environment, they are defined by the operating system.

This chapter describes external user and external role creation and management using either Oracle Administration Assistant for Windows NT or by a combination of Oracle command line tools, Registry Editor, and Windows NT User Manager.

This chapter contains these topics:

• Using Oracle Administration Assistant for Windows NT
  
  
• Manually Administering External Users and Roles

Using Oracle Administration Assistant for Windows NT

Oracle Administration Assistant for Windows NT runs from Microsoft Management Console and enables you to configure the following Oracle database users and roles so that the Windows operating system can authenticate them, and they can access Oracle9i database without a password:

• Regular Windows NT domain users and global groups as external users
  
  
• Windows NT database administrators (with the SYSDBA privilege)
  
  
• Windows NT database operators (with the SYSOPER privilege)

In addition, Oracle Administration Assistant for Windows NT can create and grant local and external database roles to Windows NT domain users and global groups.

With Oracle Administration Assistant for Windows NT, none of the following need be done manually:

• Create NT local groups that match the database system identifier (SID) and role
  
  
• Assign NT domain users to these local groups
  
  
• Authenticate users in SQL*Plus with CREATE USER username IDENTIFIED EXTERNALLY

This section describes how to perform the following tasks with Oracle Administration Assistant for Windows NT:

• Adding a Computer and Saving Your Configuration
  
  
• Granting Administrator Privileges for All Databases on a Computer
  
  
• Granting Operator Privileges for All Databases on a Computer
  
  
• Connecting to a Database
  
  
• Viewing Database Authentication Parameter Settings
  
  
• Creating an External OS User
  
  
• Creating a Local Database Role
  
  
• Creating an External OS Role
  
  
• Granting Administrator Privileges for a Single Database
  
  
• Granting Operator Privileges for a Single Database

  Note: Oracle Administration Assistant for Windows NT runs from Microsoft Management Console, which is automatically included in Windows 2000. If you are using Windows NT 4.0, you must do one of the following: Install Microsoft Windows NT 4.0 Option Pack, which includes Microsoft Management Console Download Microsoft Management Console from the Microsoft Web site: http://www.microsoft.com

Managing a Remote Computer

If you want to use Oracle Administration Assistant for Windows NT to manage a remote computer, you must have administrator privileges for the remote computer. Oracle Administration Assistant for Windows NT always creates users in Oracle9i database with the domain name as the prefix. If you are managing Oracle7 release 7.x or later databases remotely, you must set registry parameter OSAUTH_PREFIX_DOMAIN to true on the remote computer. This parameter is located in

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID  

If a Windows 2000 computer is not identified with a Domain Name System (DNS) domain name, you will receive the following error message:

Calling query w32RegQueries1.7.0.17.0  RegGetValue
Key = HKEY_LOCAL_MACHINE
SubKey = SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Value = Domain
Query Exception: GetValueKeyNotFoundException
Query Exception Class: class oracle.sysman.oii.oiil.OiilQueryException
...

To assign a DNS name:

1. Choose Control Panel > System > Network Identification > More > Primary DNS.
   
   
2. Enter a domain name, such as US.ORACLE.COM.

Adding a Computer and Saving Your Configuration

When you use Oracle Administration Assistant for Windows NT for the first time, it adds the local computer to its navigation tree. You can then add other computers.

To add a computer to the Microsoft Management Console tree:

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows NT.

   Microsoft Management Console starts.

2. Double-click Oracle Managed Objects.

   The Computer icon appears.

3. Right-click Computers.
   
   
4. Choose New > Computer.

   The Add Computer dialog box appears.

   

   Text description of the illustration addcomp.gif

5. Specify the domain and computer name for the computer on which your Oracle database is installed.
   
   
6. Click OK.
   
   
7. Double-click Computers to display the computer you added.
   
   
8. Double-click the computer you added. Several nodes for authenticating database administrators and operators appear.

   The OS Database Administrators - Computer node creates an operating system-authenticated database administrator with SYSDBA privileges for every database instance on the computer. The OS Database Operators - Computer node creates an operating system-authenticated database operator with SYSOPER privileges for every database instance on the computer.

9. Save your configuration in a console file by choosing Save in the Console main menu. You can now authenticate database administrators and operators for all instances on the computer.

Granting Administrator Privileges for All Databases on a Computer

To grant database administrator (SYSDBA) privileges to database administrators (DBAs) for all databases on a computer:

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows NT.

   Oracle Administration Assistant for Windows NT starts.

2. Right-click OS Database Administrators - Computer.
   
   
3. Choose Add/Remove.

   The OS Database Administrators - Computer for hostname dialog appears.

   

   Text description of the illustration ntdba.gif

4. Select the domain of the user to which to grant SYSDBA privileges from the Domain list box.
   
   
5. Select the user.
   
   
6. Click Add.

   The user now appears in the OS Database Administrators - Computer window.

7. Click OK.

Granting Operator Privileges for All Databases on a Computer

To grant database operator (SYSOPER) privileges to DBAs for all databases on a computer:

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows NT.

   Oracle Administration Assistant for Windows NT starts.

2. Right-click OS Database Operators - Computer.
   
   
3. Choose Add/Remove.

   The OS Database Operators - Computer for hostname dialog appears.

   

   Text description of the illustration osdboper.gif

4. Select the domain of the user to which to grant SYSOPER privileges from the Domain list box.
   
   
5. Select the user.
   
   
6. Click Add.

   The user now appears in the OS Database Operators - Computer window.

7. Click OK.

Connecting to a Database

To enable Secure Sockets Layer (SSL) when connecting to an Oracle database, start the Oracle service and the listener service in the same user account as the wallet created in Oracle Wallet Manager. Do not use the default user account in the Windows NT Services dialog box. If the Oracle service and the listener service are started in the default user accounts, then SSL does not work, and the listener does not start. Support for SSL is an Oracle Advanced Security feature. Oracle Wallet Manager is also an Oracle Advanced Security feature.

To connect to a database:

1. Right-click the database instance you want to access in the Microsoft Management Console scope pane. In the example here, a connection is to be made to ORCL:

   

   Text description of the illustration cntdb.gif

2. Choose Connect Database.

   If you connect to the Oracle database, the following Windows NT nodes appear beneath the instance. If these nodes do not appear, double-click the instance.

• External OS Users
  
  
• Local Roles
  
  
• External OS Roles
  
  
• OS Database Administrators
  
  
• OS Database Operators

Troubleshooting Connection Problems

When connecting to a local computer, Oracle Administration Assistant for Windows NT first tries to connect to the database as a SYSDBA, using the Bequeath networking protocol. When connecting to a remote computer, Oracle Administration Assistant for Windows NT first tries to connect to the database using Windows native authentication as a SYSDBA, using the TCP/IP networking protocol (port 1521 or the deprecated 1526). If it is unsuccessful, one or more dialogs appear and prompt you to enter information to connect to the database.

The dialog shown here appears because the Windows NT domain user with which you are attempting to connect to the Oracle database is not recognized as an authenticated user with SYSDBA privileges. Enter an Oracle username and password to access the database. To avoid being prompted with this dialog again, configure your domain user to be a database administrator authenticated by the Windows NT operating system.

Text description of the illustration cntdb4.gif

The next dialog appears because you are not using the TCP/IP networking protocol to connect to a remote Oracle database or the Oracle database is not running. Using a protocol other than TCP/IP (Named Pipes for example) causes this dialog box to appear each time you attempt a remote connection.

Text description of the illustration cntdb2.gif

If you do not want this dialog to appear each time, then change to the TCP/IP protocol and make sure the Oracle Net Services listener for the database is listening on the default port 1521 (or the deprecated default port 1526). Otherwise, this dialog appears every time. Ensure also that the Oracle database is started.

1. Enter the net service name with which to connect to your Oracle database. You must enter a net service name regardless of the authentication method you select.
   
   
2. If you want to access the database with an Oracle username and password, select the Database Authenticated option. This username and password must exist in the Oracle database and have the SYSDBA privilege.
   
   
3. If you want to access the database with the Windows NT domain user with which you are currently logged in, select the OS Authenticated Connection as SYSDBA option. This domain user must already be recognized by Windows NT as an authenticated user with SYSDBA privileges. Otherwise, your logon fails.

   Note: Oracle Net Services provides a new Trace Assistant tool that helps diagnose connection problems by converting existing trace file text into a more readable format. See "Using the Trace Assistant to Examine Trace Files" in Oracle9i Net Services Administrator's Guide.

Viewing Database Authentication Parameter Settings

To view database authentication parameter settings:

1. Right-click the database.
   
   
2. Choose Properties.
   
   
3. The Properties dialog box appears displaying the following parameter values:
   
   
   • OS_AUTHENT_PREFIX
     
     
   • OS_ROLES

OS_AUTHENT_PREFIX is an init.ora file parameter that authenticates external users attempting to connect to the Oracle database with the user's Windows NT username and password. The value of this parameter is attached to the beginning of every user's Windows username.

By default, the parameter is set to none ("") during Oracle9i database creation. Therefore, a Windows domain username of frank is authenticated as username frank. If you set this parameter to xyz, then Windows NT domain user frank is authenticated as user xyzfrank.

OS_ROLES is an init.ora file parameter that, if set to true, enables the Windows NT operating system to manage authorization of an external role for a database user. By default, OS_ROLES is set to false. You must set OS_ROLES to true and restart your Oracle database 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.

If OS_ROLES is set to true, and you assign an external role to an NT global group, then it is granted only at the global group level, and not at the level of the individual user in this global group. This means that you cannot revoke or edit the external role assigned to an individual user in this global group through the Roles tab of the User Name Properties dialog box at a later time. Instead, you must use the Assign External OS Roles to an NT Global Group field in the dialog box to revoke the external role from this global group (and therefore all its individual users).

External roles assigned to an individual domain user or local roles (with OS_ROLES set to false) assigned to an individual domain user or NT global group are not affected by this issue. They can be edited or revoked.

If OS_ROLES is set to true, you cannot grant local roles in the database to any database user. You must grant roles through Windows NT. See "Creating a Local Database Role" and "Creating an External OS Role" for more information.

Creating an External OS User

The External OS Users node of Oracle Administration Assistant for Windows NT enables you to authenticate a Windows NT user to access the Oracle database as an external user without being prompted for a password. External users are typically regular database users (not database administrators) to which you assign standard database roles (such as CONNECT and RESOURCE), but do not want to assign SYSDBA (database administrator) or SYSOPER (database operator) privileges.

To create an external OS user:

1. Follow the steps in "Connecting to a Database" to connect to a database.
   
   
2. Right-click External OS Users. A contextual menu appears.

   

   Text description of the illustration mmc4.gif

3. Choose Create.

   Create External OS User Wizard starts, and the first of three wizard dialogs appears. The first dialog is for Windows Users and Groups.

   

   Text description of the illustration mmc5.gif

4. In the NT Domain Users and Groups box select the domain in which your Windows NT domain users and global groups are located.
   
   
5. Select the Windows NT domain users and global groups to which to grant access to the database.
   
   
6. Click Add. The selected users and groups now appear in the New External OS Users list box.
   
   
7. Click Next. The Profile and Tablespace dialog appears.

   

   Text description of the illustration mmc6stp2.gif

8. In the Assigned Profile list, select a profile for the new external users. A profile is a named set of resource limits. If resource limits are enabled, Oracle limits database usage and instance resources to whatever is defined in the user's profile. You can assign a profile to each user and a default profile to all users who do not have specific profiles.
   
   
9. In Tablespace Quota double-click the tablespace to assign a tablespace quota.
   
   
10. Click Next. The Roles dialog appears.

    

    Text description of the illustration step3.gif

11. In Available Roles select the database roles to grant to the new external users.
    
    
12. Click Grant.
    
    
13. Click Finish.
    
    
14. Right-click the external user for which you want to view information and select Properties.

    The assigned properties appear.

    Note: If you select an NT global group for authentication when using Oracle Administration Assistant for Windows NT, all users currently in the group are added to the Oracle database. If at a later time, you use a Windows NT tool to add or remove users in this Windows NT global group, these updates are not reflected in the Oracle database. The newly added or removed users must be explicitly added or removed in the Oracle database with Oracle Administration Assistant for Windows NT.

Creating a Local Database Role

The Local Roles node of Oracle Administration Assistant for Windows NT enables you to create a role and have it managed by the database. Once a local role is created, you can grant or revoke that role to a database user. To create a local database role:

1. Follow the steps in "Connecting to a Database" to connect to a database.
   
   
2. Right-click Local Roles for the database for which you want to create a local role.
   
   
3. Choose Create.

   Create Local Role Wizard starts, and the first of three wizard dialogs appears. The first dialog is for Name and Authentication.

   

   Text description of the illustration locrol1.gif

4. Enter a local role name to use.
   
   
5. In Authentication select None if you want a user to use this local role without being required to enter a password.

   Select Password if you want use of this role to be protected by a password. These roles can only be used by supplying an associated password with the SET ROLE command. See Oracle9i Database Administrator's Guide for additional information.

   Enter the password to use with this role.

   Confirm the password by entering it a second time.

6. Click Next. The System Privileges dialog appears.

   

   Text description of the illustration locrol2.gif

7. In Available System Privileges select the system privileges you want to assign to the local role.
   
   
8. Click Grant to grant the selected system privileges to the local role.

   The Granted System Privileges field displays the list of system privileges granted to the local role. To revoke a system privilege, make an appropriate selection, then choose Revoke.

9. If you want to grant Admin Option to this role, click the value in the Admin Option column to display a list box. This enables you to select Yes.
   
   
10. Click Next. The Roles dialog appears.

    

    Text description of the illustration locrol3.gif

11. In Available Roles select the roles you want to assign to the local role. Both local roles and external roles appear in this list.
    
    
12. Click Grant to grant the selected roles to the role.

    The Granted Roles field displays the list of roles granted to the role. Both local roles and external roles can appear in this list. To revoke roles, make appropriate selections, then choose Revoke.

13. Click Finish.

Creating an External OS Role

The External OS Roles node of Oracle Administration Assistant for Windows NT enables you to create an external role and have it managed by the Windows operating system. Once an external role is created, you can grant or revoke that role to a database user. To create an external role:

1. Follow the steps in "Connecting to a Database" to connect to a database.
   
   
2. Right-click External OS Roles for the database for which to create an external role.
   
   
3. Choose Create.

   Create External OS Role Wizard starts, and the first of three wizard dialogs appears. The first dialog is for Name. Authentication: External appears in this dialog to indicate that only external roles can be created.

   Note: Create External OS Role Wizard is available only if init.ora parameter OS_ROLES is set to true. If it is set to false, then you must first change it to true and then restart the Oracle database.

   

   Text description of the illustration exrol1.gif

4. Enter an external role name to use. An external role is a role that is managed by the Windows operating system.
   
   
5. Click Next.

   The System Privileges dialog appears.

   

   Text description of the illustration exrol2.gif

6. In Available System Privileges select the system privileges you want to assign to the external role.
   
   
7. Choose Grant to grant the selected system privileges to the external role.
   
   
8. The Granted System Privileges field displays the list of system privileges granted to the external role. To revoke a system privilege, make an appropriate selection, then choose Revoke.
   
   
9. If you want to grant Admin Option to this role, choose the value in the Admin Option column to display a list box. This enables you to select Yes.
   
   
10. Click Next.

    The Roles dialog appears.

    

    Text description of the illustration exrol3.gif

11. In Available Roles select the roles you want to assign to the external role. Both local roles and external roles appear in this list.
    
    
12. Click Grant to grant the selected roles to the external role.

    The Granted Roles field displays the list of roles granted to the external role.

13. Click Finish.

Granting Administrator Privileges for a Single Database

The OS Database Administrators node of Oracle Administration Assistant for Windows NT enables you to authorize a Windows NT user with SYSDBA privileges for a specific instance on a computer. To grant administrator (SYSDBA) privileges for a single database:

1. Follow the steps in "Connecting to a Database" to connect to a database.
   
   
2. Right-click the database to access (for example, orcl) in the Microsoft Management Console scope pane.
   
   
3. Choose Connect Database.

   Several icons, including OS Database Administrators and OS Database Operators, appear.

4. Right-click OS Database Administrators.
   
   
5. Choose Add/Remove.

   The OS Database Administrators for instance dialog appears. In the example shown here, the instance is MARK:

   

   Text description of the illustration dba_one.gif

6. In NT Domain Users and Groups select the domain of the user to which to grant SYSDBA privileges from the Domain: list box.
   
   
7. Select the user.

   The user now appears in OS Database Administrators.

8. Click OK.

Granting Operator Privileges for a Single Database

The OS Database Operators node of Oracle Administration Assistant for Windows NT enables you to authorize a Windows NT user with SYSOPER privileges for a specific instance on a computer. To grant operator (SYSOPER) privileges for a single database:

1. Follow the steps in "Connecting to a Database" to connect to a database.
   
   
2. Right-click the database to access (for example, orcl) in the Microsoft Management Console scope pane.
   
   
3. Choose Connect Database.

   Several icons, including OS Database Administrators and OS Database Operators, appear.

4. Right-click OS Database Operators.
   
   
5. Choose Add/Remove.

   The OS Database Operators for instance dialog appears. In the example shown here, the instance is MARK:

   

   Text description of the illustration oper_one.gif

6. In NT Domain Users and Groups select the domain of the user to which to grant SYSOPER privileges from the Domain: list box.
   
   
7. Select the user.
   
   
8. Click Add.

   The user now appears in OS Database Operators.

9. Click OK.

Manually Administering External Users and Roles

Instead of using Oracle Administration Assistant for Windows NT, you can manually configure administrators, operators, users, and roles to be authenticated by the operating system. Manual configuration involves using Oracle command line tools, editing the registry, and creating local groups in Windows NT User Manager. All of the following can be manually configured to access the Oracle database without a password:

• External OS users
  
  
• Windows NT database administrators (with SYSDBA privilege)
  
  
• Windows NT database operators (with SYSOPER privilege)

In addition, you can manually create and grant local and external database roles to Windows NT domain users and global groups.

This section describes:

• Manually Creating an External OS User
  
  
• Manually Granting Administrator and Operator Privileges for Databases
  
  
• Manually Creating an External Role
  
  
• Manually Migrating Users

  Note: Use extreme care when manually configuring administrators, operators, users, and roles to be authenticated by the operating system. If possible, use Oracle Administration Assistant for Windows NT to perform configuration procedures.

Manually Creating an External OS User

This section describes how to authenticate external OS users (not database administrators) using Windows NT, so that a password is not required when accessing the database. When you use Windows NT to authenticate external OS users, your database relies solely on Windows NT to restrict access to database usernames.

In the following procedure, two Windows NT usernames are authenticated:

• Local user frank
  
  
• Domain user frank on domain sales

Local user frank logs into its local Windows NT client computer to access an Oracle9i database, which can be on a different computer. To access other databases and resources on other computers, the local user must provide a username and password each time.

Domain user frank on domain sales logs into a sales domain that includes many other Windows NT computers and resources, one of which contains an Oracle9i database. The domain user can access all the resources the domain provides with a single username and password.

The procedure is divided into two sets of tasks performed on different computers:

• External User Authentication Tasks on the Oracle9i Database Server
  
  
• External User Authentication Tasks on the Client Computer

External User Authentication Tasks on the Oracle9i Database Server

1. Add parameter OS_AUTHENT_PREFIX to your init.ora file.

   The OS_AUTHENT_PREFIX value is prefixed to local or domain usernames attempting to connect to the server with the user's operating system name and password. The prefixed username is compared with Oracle usernames in the database when a connection request is attempted. Using parameter OS_AUTHENT_PREFIX with Windows native authentication methods is the recommended method for performing secure, trusted client connections to your server.

2. Set a value for OS_AUTHENT_PREFIX. Your choices are:
   
   
   • Any character string

     If you specify xyz, as in this procedure's example, then xyz is prefixed to the beginning of the Windows NT username (for example, xyzfrank for local user frank or xyzsales\frank for domain user frank on domain sales). String values are case insensitive.

   • "" (two double quotes with no space between)

     This option is recommended, because it eliminates the need for any prefix to Windows NT usernames (for example, frank for local user frank or sales\frank for domain user frank on domain sales).

   • No value specified

     If you do not specify a value for OS_AUTHENT_PREFIX, it defaults to OPS$ (for example, OPS$frank for local user frank or OPS$sales\frank for domain user frank on domain sales).

3. Create a Windows NT local or domain username for frank with User Manager (if the appropriate name does not currently exist). See your Windows NT documentation for detailed instructions.
   
   
4. Do this step only if you are not authenticating a domain name with a user (for example, just frank instead of frank on domain sales). Otherwise, go to step 5.
   
   
   1. Start Registry Editor from the command prompt:
      
      

      C:\> regedt32
      

   2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID, where ID is the Oracle home directory you want to edit.
      
      
   3. Choose Edit > Add Value.

      The Add Value dialog box appears:

      

      Text description of the illustration addval.gif

   4. Enter OSAUTH_PREFIX_DOMAIN in the Value Name field.
      
      
   5. Choose REG_EXPAND_SZ from the Data Type list box.
      
      
   6. Click OK.

      The String Editor dialog box appears:

      

      Text description of the illustration addstr.gif

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

      There may be multiple frank usernames on your network, including local user frank, domain user frank on sales, and possibly several domain users frank on other domains. Entering true enables the server to differentiate among them. 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.

      Registry Editor adds the parameter.

   9. Choose Exit from the registry menu.

      String Editor exits.

5. Ensure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
   
   
6. Start SQL*Plus:
   
   

   C:\> sqlplus
   

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

   SQL> CONNECT 
   Enter user-name: SYSTEM/password
   
   

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

8. Create a local external user by entering:
   
   

   SQL> CREATE USER xyzfrank IDENTIFIED EXTERNALLY;
   
   

   where xyz is the value you chose for initialization parameter OS_AUTHENT_PREFIX, and frank is the Windows NT local username.

9. Grant a local external user database roles by entering:
   
   

   SQL> GRANT RESOURCE TO xyzfrank;
   SQL> GRANT CONNECT TO xyzfrank;
   

10. Create a domain external user by entering:
    
    

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

    where XYZ is the value you chose for initialization parameter OS_AUTHENT_PREFIX, and SALES\FRANK is the domain name and Windows NT domain username. Double quotes are required and the entire syntax must be in uppercase.

11. Grant a domain external user database roles by entering:
    
    

    SQL> GRANT RESOURCE TO "XYZSALES\FRANK";
    SQL> GRANT CONNECT TO "XYZSALES\FRANK";
    

    Double quotes are required and the entire syntax must be in uppercase.

12. Connect to the database with the SYSDBA name:
    
    

    SQL> CONNECT / AS SYSDBA
    

13. Shut down the database:
    
    

    SQL> SHUTDOWN
    

14. Restart the database:
    
    

    SQL> STARTUP
    

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

External User Authentication Tasks on the Client Computer

1. Create Windows NT local or domain username frank with the same username and password that exist on the Windows NT server (if the appropriate name does not currently exist).
   
   
2. Ensure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
   
   
3. Use Oracle Net Configuration Assistant to configure a network connection from your client computer to the Windows NT server on which your Oracle9i database is installed. See Oracle9i Net Services Administrator's Guide for instructions.
   
   
4. Start SQL*Plus:
   
   

   C:\> sqlplus / NOLOG
   
   

5. Connect to your Windows NT server:
   
   

   SQL> CONNECT /@connect_identifier
   
   

   where connect_identifier is the net service name for Oracle9i database.

   Oracle9i database searches the data dictionary for an automatic login username corresponding to the Windows NT local or domain username, verifies it, and enables connection as xyzfrank or xyzsales\frank.

6. Verify that you have connected to Oracle9i database as local or domain user frank by viewing the roles assigned in steps 9 or 11 of "External User Authentication Tasks on the Oracle9i Database Server".
   
   

   SQL> 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 Oracle9i username is the whole name xyzfrank or xyzsales\frank, all objects created by xyzfrank or xyzsales\frank (that is, tables, views, indexes, and so on) are prefixed by this name. For another user to reference the table shark owned by xyzfrank, for example, the user must enter:

   SQL> SELECT * FROM xyzfrank.shark 
   

   Note: Automatic authorization is supported for all Oracle Net protocols.

Manually Granting Administrator and Operator Privileges for Databases

This section describes how to enable Windows NT to grant the database administrator (SYSDBA) and database operator (SYSOPER) privileges to database administrators. With this privilege, database administrators can issue the following commands from a client computer and connect to Oracle9i database without entering a password:

CONNECT / AS SYSOPER
CONNECT / AS SYSDBA

To enable this feature, the Windows NT local or domain username of the database administrator must belong to one of the Windows NT local groups listed in Table 2-1.

Table 2-1 Windows NT Local Groups with SYSDBA and SYSOPER Privileges

Local Group	Privileges
ORA_OPER	SYSOPER privileges for all databases on a computer
ORA_DBAFoot 1	SYSDBA privileges for all databases on a computer
ORA_SID_OPER	SYSOPER privileges for a single database (identified by SID)
ORA_SID_DBA	SYSDBA privileges for a single database (identified by SID)

1 ORA_DBA is automatically created during installation. See section "Operating System Authentication Enabled at Installation" for information.

The manual procedure for enabling database administrators to connect as SYSOPER or SYSDBA without a password is divided into two sets of tasks performed on different computers:

• SYSDBA/SYSOPER Authentication Tasks on the Oracle9i Database Server
  
  
• SYSDBA/SYSOPER Authentication Tasks on the Client Computer

SYSDBA/SYSOPER Authentication Tasks on the Oracle9i Database Server

1. Open User Manager on the Windows NT server where your Oracle9i database is installed.
   
   
2. Choose New Local Group from the User Menu.

   The New Local Group dialog appears:

   

   Text description of the illustration osauth1.gif

3. Enter the name you have chosen for the new Windows NT local group in the Group Name field. For this example, the SID entered is ORCL.
   
   
4. Click Add. The Add Users and Groups dialog appears:

   

   Text description of the illustration osauth3.gif

5. Select one or more Windows NT users from the Names field and choose Add.
   
   
6. Click OK.

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

   

   Text description of the illustration osauth2.gif

7. Click OK.
   
   
8. Exit User Manager.
   
   
9. Ensure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
   
   
10. Start Registry Editor from the command prompt:
    
    

    C:\>regedt32
    
    

11. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID

    where ID is the Oracle home that you want to edit.

12. Set parameter OSAUTH_PREFIX_DOMAIN to true.

SYSDBA/SYSOPER Authentication Tasks on the Client Computer

1. Create a Windows NT local or domain username with the same username and password that exist on the Windows NT server (if the appropriate username does not currently exist).
   
   
2. Ensure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
   
   
3. Use Oracle Net Configuration Assistant to configure a network connection from your client computer to the Windows NT server on which your Oracle9i database is installed. See Oracle9i Net Services Administrator's Guide for instructions.
   
   
4. Start SQL*Plus:
   
   

   C:\> sqlplus
   
   

5. Connect to Oracle9i database:
   
   

   SQL> SET INSTANCE net_service_name
   
   

   where net_service_name is the Oracle Net net service name for Oracle9i database.

6. If you specified ORA_DBA or ORA_SID_DBA in step 3 of "SYSDBA/SYSOPER Authentication Tasks on the Oracle9i Database Server", then enter either of the following:
   
   

   SQL> CONNECT / AS SYSOPER
   SQL> CONNECT / AS SYSDBA
   
   

   If you specified ORA_OPER or ORA_SID_OPER in step 3, then enter:

   SQL> CONNECT / AS SYSOPER
   
   

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

Manually Creating an External Role

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

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

Consider the following example. With external roles enabled, you log on to a Windows NT domain with domain username sales\frank (sales is the domain name and frank is the domain username). You then connect to an Oracle9i database as Oracle database user scott. In this case, you receive the roles granted to sales\frank but not the roles granted to scott.

The procedure for manually creating an external role is divided into two sets of authorization tasks performed on different computers:

• External Role Authorization Tasks on the Oracle9i Database Server
  
  
• External Role Authorization Tasks on the Client Computer

External Role Authorization Tasks on the Oracle9i Database Server

1. Add initialization parameter OS_ROLES to the init.ora file.
   
   
2. Set OS_ROLES to true.

   The default setting for this parameter is false.

3. Ensure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
   
   
4. Start SQL*Plus:
   
   

   C:\> sqlplus / NOLOG
   
   

5. Connect to your Windows NT server:
   
   

   SQL> CONNECT / AS SYSDBA
   
   

6. Create a new database role. You can give this new role whatever name you want. In this example the role is named DBSALES3:
   
   

   SQL> CREATE ROLE DBSALES3 IDENTIFIED EXTERNALLY;
   
   

7. Grant to DBSALES3 whatever Oracle roles are appropriate to your database environment:
   
   

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

8. Connect to the database as SYSDBA:
   
   

   SQL> CONNECT / AS SYSDBA
   
   

9. Shut down the database:
   
   

   SQL> SHUTDOWN
   
   

10. Restart the database:
    
    

    SQL> STARTUP
    
    

11. Open Windows NT User Manager.
    
    
12. Choose New Local Group from the User menu.

    The New Local Group dialog appears:

    

    Text description of the illustration osauth4.gif

13. 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 identifies the database instance
      
      
    • rolename identifies the database role granted
      
      
    • D indicates that this database role is to be a default role of the database user
      
      
    • A indicates that this database role includes ADMIN OPTION

    Characters D and A are optional. If specified, they must be preceded by an underscore.

    For this example, ORA_orcl_dbsales3_D is entered.

14. Click Add.

    The Add Users and Groups dialog appears:

    

    Text description of the illustration osauth3.gif

15. Select the Windows NT local or domain username you want to add and choose Add.
    
    
16. Click OK.

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

    

    Text description of the illustration osauth5.gif

    You can create multiple database roles and grant them to several possible Windows NT groups with differing options, as shown in the following table. Users connecting to the ORCL instance and authenticated by Windows NT as members of all four of these Windows NT local groups will have the privileges associated with dbsales3 and dbsales4 by default (because of option _D). If these users first connect as members of dbsales3 or dbsales4 and use the SET ROLE command, then they can also gain access to database roles dbsales1 and dbsales2. But if these users try to connect with dbsales1 or dbsales2 without first connecting with a default role, they are unable to connect. Finally, these users can grant dbsales2 and dbsales4 to other roles (because of option _A).

    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 Oracle9i database converts the group name to a role name, it changes the name to uppercase.

17. Click OK.
    
    
18. Exit User Manager.

External Role Authorization Tasks on the Client Computer

1. Create a Windows NT local or domain username with the same username and password that exist on the Windows NT server (if the appropriate username does not currently exist).
   
   
2. Ensure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
   
   
3. Use Oracle Net Configuration Assistant to configure a network connection from your client computer to your Oracle9i database. See Oracle9i Net Services Administrator's Guide for instructions.
   
   
4. Start SQL*Plus:
   
   

   C:\> sqlplus / NOLOG
   
   

5. Connect to the correct instance:
   
   

   SQL> SET INSTANCE connect_identifier
   
   

   where connect_identifier is the net service name for the Oracle9i database connection that you created in Step 3.

6. Connect to Oracle9i database:
   
   

   SQL> CONNECT scott/tiger AS SYSDBA
   
   

   You are connected to the Windows NT server over net service with Oracle username scott/tiger. Roles applied to Oracle username scott consist of all roles defined for the Windows NT username that were previously mapped to the database roles (in this case, ORA_DBSALES3_D). All roles available under an authenticated connection are determined by the Windows NT username and the Oracle-specific Windows NT local groups to which the user belongs (for example, ORA_SID_DBSALES1 or ORA_SID_DBSALES4_DA).

   Note: OSDBA and OSOPER are generic names for two special operating system groups that control database administrator logins when using operating system authentication. On Windows NT, OSDBA and OSOPER are mapped to local groups in User Manager. Windows NT-specific names for OSDBA and OSOPER are described in "Manually Granting Administrator and Operator Privileges for Databases". See Oracle9i Database Administrator's Guide for more information on OSDBA and OSOPER.

Manually Migrating Users

You can migrate local or external users to enterprise users with User Migration Utility. Migrating from a database user model to an enterprise user model provides solutions to administrative, security, and usability challenges in an enterprise environment. In an enterprise user model, all user information is moved to an LDAP directory service, which provides the following benefits:

• Centralized storage and management of user information
  
  
• Centralized user authentication
  
  
• Enhanced security

User Migration Utility is a command-line tool. Its syntax is of the form:

C:\ umu parameters

To get a list of User Migration Utility parameters, enter:

C:\ umu help=yes