Oracle8i Administrator's Guide
Release 2 (8.1.6) for Windows NT
A73008-01

Library
Product
Contents
Index

Prev Next

8
Authenticating Database Users with Windows

This chapter describes how to authenticate Oracle8i database users with Windows.

Specific topics discussed are:

Windows Native Authentication Overview

The Oracle8i database can use Windows user login credentials to authenticate database users. The benefits include:

Windows native authentication methods (automatically installed with Net8 Server and Net8 Client) enable database user authentication through Windows NT or Windows 2000. This enables client computers to make secure connections to an Oracle8i database on a Windows NT or Windows 2000 server. The server then permits the user to perform the database actions on the server.

Note:

This chapter describes using Windows native authentication methods with Windows NT 4.0 and Windows 2000. For information on the Secure Socket Layer (SSL) protocol and Oracle Internet Directory, see the Oracle Advanced Security Administrator's Guide and Oracle Internet Directory Administrator's Guide. 

Windows Authentication Protocols

Windows native authentication methods work with Windows authentication protocols to enable access to your Oracle8i database. Kerberos is the default authentication protocol for Windows 2000. With Windows NT 4.0, NT LAN Manager (NTLM) is the default protocol.

Client computers do not need to specify an authentication protocol when attempting a connection to an Oracle8i database. Instead, the Oracle8i database determines the protocol to use, completely transparent to the user. The only Oracle requirement for the client is to ensure that SQLNET.AUTHENTICATION_SERVICES is set to NTS in the ORACLE_BASE\ORACLE_HOME\NETWORK\ADMIN\SQLNET.ORA file on both the client and database server (this is the default setting for both after installation). For Oracle7 Server and Oracle8 8.0 releases, you must manually set this value using Net8 Assistant.

Your Oracle8i database network likely includes client computers and database servers running different Windows operating system and Oracle software releases in different domains. For example, you may be running an Oracle 8.0.5 client installed on Windows 95 that connects to an Oracle 8.1.6 database installed on a Windows NT 4.0 computer that runs in a Windows 2000 domain. This combination of different releases means that the authentication protocol being used can vary.

This table lists the Oracle software and Windows operating system releases required to enable Kerberos to be the default authentication protocol used:

For The...  This Windows Software is Required...  This Oracle Software is Required... 

Client Computer 
  • Windows 2000

 
  • Oracle8i Client release 8.1.6

 

Database Computer 
  • Windows 2000

 
  • Oracle8i database release 8.1.6

 

Domain 
  • Windows 2000

 
  • None

 

For all other combinations of Windows operating system and Oracle software releases used in your network, the authentication protocol used is NTLM.

See Also:

Your Windows documentation for more information on each authentication protocol 

User Authentication and Role Authorization Methods

This section describes how user login credentials are authenticated and database roles are authorized in Windows NT 4.0 or Windows 2000 domains. User authentication and role authorization are defined as follows:

Feature  Description  More Information 

user authentication 

The process by which the database uses the user's Windows login credentials to authenticate the user. 

Oracle8i Administrator's Guide  

role authorization 

The process of granting an assigned set of roles to authenticated users. 

Oracle8i Administrator's Guide  

In releases prior to 8.1.6, Oracle supported user authentication and role authorization in Windows NT 4.0 domains. This table provides descriptions of these basic features:

Feature  Description  More Information 

authentication of external users 

Users are authenticated by the database using the user's Windows login credentials that enable them to access the Oracle database without being prompted for additional login credentials.  

"External Users and Roles"

"Administering External Users and Roles" 

authorization of external roles 

Roles are authorized using Windows NT local groups. Once an external role is created, you can grant or revoke that role to a database user. You must set the INIT.ORA parameter OS_ROLES to TRUE to authorize external roles. 

With release 8.1.6, enhancements have been made to support global user authentication and global role authorization with Windows native authentication in Windows 2000 domains using Active Directory. You map multiple enterprise users in a directory server to this global user. These enhancements are available to you only if you:

Enterprise user authentication is enabled by setting the OSAUTH_X509_NAME registry parameter to TRUE on the computer on which the Oracle8i database is running in a Windows 2000 domain. If this parameter is set to FALSE (the default setting) in a Windows 2000 domain, then the Oracle8i database authenticates the user as an external user (described in "External Users and Roles"). Setting this parameter to TRUE in a Windows NT 4.0 domain does not enable you to use enterprise users.

See "Enterprise User Authentication" for more information on using the OSAUTH_X509_NAME registry parameter.

Which Authentication and Authorization Methods Should I Use?

The user authentication and role authorization methods to use depend upon your Oracle8i database environment:

Use...  When... 

Enterprise users and roles 

You have many users connecting to multiple databases.

Enterprise users enable you to limit the number of individual database users that you need to create and manage. If you do not create enterprise users, you must create each user individually as a global user in each database to which they require access. Enterprise users require the use of a directory server.

Use enterprise roles in environments where enterprise users assigned to these roles are located in many geographic regions and must access multiple databases. Enterprise roles enable you to limit the number of individual database roles that you need to create and manage. If you do not create enterprise roles, you must create each role individually in each database. Enterprise roles require the use of a directory server. 

External users and roles 

You have a smaller number of users accessing a limited number of databases. External users must be created individually in each database, and do not require the use of a directory server.

External roles must also be created individually in each database, and do not require the use of a directory server. External roles are authorized using group membership of the users in the local groups on the system. 

External Users and Roles

The following sections describe external user authentication and external role authorization methods:

External User Authentication

This table describes external user authentication features:

Feature   Description 

User authentication 

Authentication of external users is supported. External users are local users or domain users. External users must be created individually in each Oracle8i database that they need to access. External users can access the Oracle8i database without providing a user name or password. Use external users in environments where users do not need to access multiple databases. 

User authentication process 

The Oracle8i database receives the client user credentials from the Windows authentication protocol and queries the data dictionary to determine if this is a valid user. If the user name is found, the Oracle8i database authenticates the user as an external user (with authorized external roles) and permits access. 

User administration tools 

Use one of the following tools to authenticate external user names:

 

Starting with Oracle release 8.1.5, external users created in the database were automatically prefixed with the domain name. For example, for a Windows NT user DOMAIN1\NTUSER1, the Oracle user created in the database is DOMAIN1\NTUSER1. If you want to create the Oracle user in the database without prefixing with the domain name, you first need to set the registry value OSAUTH_PREFIX_DOMAIN in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID to FALSE.

Note that it is less secure if you do not prefix the domain name. Therefore, if you migrate a database with external users to Oracle8i for Windows NT, you have two options:

External Role Authorization

This table describes external role authorization features:

Feature   Description 

Role authorization 

Authorization of external roles is supported. External roles are roles authorized on a single Oracle8i database.

Use external roles in environments where users assigned to these roles do not need to access multiple databases. 

User permissions 

The permissions (external roles) authorized to a user are those assigned to the local group of which they are a member. 

User groups 

Users can belong to Windows NT local and domain groups. These groups can be assigned to external roles. 

Enterprise Users and Roles

The following sections describe enterprise user authentication and enterprise role authorization methods:

Enterprise User Authentication

This table describes enterprise user authentication features:

Feature   Description 

User authentication 

Enterprise users are users created in a directory server (for example, Active Directory) who require access to multiple databases in an enterprise. To access these databases, enterprise users need to be defined in each database as a global user.

For example, assume there is an enterprise user (cn=joe,cn=users,dc=acme,dc=com) who needs access to two databases: Sales and Marketing. This enterprise user must be defined in both databases as a global user.

Most users do not need their own accounts in the database; they typically need to access only application schemas in a database. This is especially critical in an Internet environment, where a number of users access the same application and there is no need to create global users in the database for all enterprise users.

In 8.1.6, you can create just one global user in the database and map multiple enterprise users in a directory server to this one global user with Oracle Enterprise Security Manager. See the Oracle Advanced Security Administrator's Guide for more information. 

User authentication process 

Enterprise user authentication is enabled, if you:

  • Set the OSAUTH_X509_NAME registry parameter to TRUE (See "Oracle8i Integration with a Directory Server" for instructions.)

  • Operate your Oracle8i database in a Windows 2000 domain

  • Use Oracle Enterprise Security Manager to map a global user to enterprise users

 

User administration tools 

Oracle Enterprise Security Manager (an integrated application included in Oracle Enterprise Manager) enables you to create enterprise users, roles, and domains. See the Oracle Advanced Security Administrator's Guide for more information on using this tool. 

Authentication protocol 

Kerberos (if the Windows and Oracle releases match those listed in the table in "Windows Authentication Protocols"; otherwise, NTLM is used). 

Enterprise Role Authorization

This table describes enterprise role authentication features:

Note:

Enterprise roles are authorized by the directory server, and not by setting the OS_ROLES initialization file parameter to TRUE (the method for enabling external role authorization). 

Feature   Description 

Role authorization 

Authorization of enterprise roles is supported with Oracle8i release 8.1.6. An enterprise role is a single role created in a directory server with Oracle Enterprise Security Manager. Use Oracle Enterprise Security Manager to assign multiple global roles and Windows 2000 global and universal users and groups located on multiple databases to this enterprise role. A global role is a role that must be created individually in each Oracle8i database.

For example, an enterprise user can be granted the enterprise role "HR", which contains the global role "HR user" in the human resources database, and the global role "employee" in the corporate information database. If a user changes jobs, his enterprise role assignment is only changed in one place, altering his privileges in multiple databases throughout the enterprise. Also, an administrator can add capabilities to enterprise roles (granted to multiple users) without having to update the authorizations of each individual user.

Use enterprise roles in environments where users assigned to these roles are located in many geographic regions and must access multiple databases.

See the Oracle Advanced Security Administrator's Guide for more information on creating and storing enterprise roles in a directory server with Oracle Enterprise Security Manager. 

User permissions 

The permissions authorized to a user are those assigned to the database of which they are a member. 

User groups 

Users can belong to Windows 2000 global and universal groups. These groups can be assigned to enterprise roles. 

Oracle8i Integration with a Directory Server

Perform the following tasks to integrate Oracle components with Active Directory. This enables you to take advantage of the new user authentication and role authorization described in "Enterprise Users and Roles". Note that these enhancements are only available if you are running in a Windows 2000 domain.

Step 1: Install and Configure Components

Read Chapter 4, "Using Oracle8i Directory Server Features with Active Directory" and the Oracle8i Installation Guide for Windows NT for information on pre-installation, installation, and configuration issues.

Step 2: Set the OSAUTH_X509_NAME Registry Parameter

Set the OSAUTH_X509_NAME registry parameter to enable client users to access the Oracle8i database as X.509-compliant enterprise users. This parameter is required only if you want to use enterprise users and roles.

Set This Parameter On...  Description 

An Oracle8i database computer running in a Windows 2000 domain 

When set to TRUE, enables a client user name to be identified as an X.509-compliant enterprise user name when connecting to an Oracle8i database through Active Directory. User authentication is permitted through the Windows authentication protocol and Active Directory.

If set to FALSE (the default setting), the client user is identified as an external user and database authorization is permitted through the Windows authentication protocol and Oracle8i database data dictionary. No access to Active Directory is available. 

To set the OSAUTH_X509_NAME registry parameter:

  1. Go to the computer on which the Oracle8i database is installed.

  2. Choose Start > Run.

  3. Enter REGEDT32 in the Open field, and click OK.

    The Registry Editor window appears.

  4. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID.

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

  5. Double-click OSAUTH_X509_NAME.

    A String Editor dialog box appears.

  6. Set the value to TRUE in the String field.

  7. Click OK.

  8. Choose Exit from the Registry menu.

    The registry editor exits.

Step 3: Start and Use Oracle Enterprise Security Manager

Use Oracle Enterprise Security Manager to create and manage enterprise users, roles, and domains, and assign enterprise users and groups to enterprise roles.

Oracle Enterprise Security Manager is included as an integrated application with Oracle Enterprise Manager. See the Oracle Advanced Security Administrator's Guide for information on using this tool. The procedures below describe Windows-unique features for using Oracle Enterprise Security Manager in a Windows 2000 domain.

To use Oracle Enterprise Security Manager:

  1. Choose Start > Programs > Oracle - HOME NAME > Extended Database Administration > Enterprise Security Manager.

  2. Log in when prompted.

  3. Use the online help and instructions in Oracle Advanced Security Administrator's Guide to use this tool.

  4. Review the following issues for using Active Directory.

Automatically Enabling Operating System Authentication During Installation

When you install your Oracle8i database, your Windows user name is automatically added to a Windows NT local group called ORA_DBA. The ORA_DBA local group is:

This enables you to:

Administering External Users and Roles

There are two methods for administering external users and roles:

Using Oracle Administration Assistant for Windows NT

Oracle Administration Assistant for Windows NT is a GUI tool that runs from the Microsoft Management Console. Oracle Administration Assistant for Windows NT enables you to configure the following Oracle database users and roles to be authenticated by the Windows operating system:

Oracle Administration Assistant for Windows NT eliminates the need to manually:

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

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 in the 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 > Database Administration > Oracle Administration Assistant for Windows NT.

    The 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.


  5. Specify the domain and hostname of 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:

    This Node...  Enables You To... 

    OS Database Administrators - Computer 

    Create an operating system-authenticated database administrator (with SYSDBA privileges) for all database instances on the computer. 

    OS Database Operators - Computer 

    Create an operating system-authenticated database operator (with SYSOPER privileges) for all database instances on the computer. 

  9. Save your configuration in a console file by clicking Save in the Console main menu.

    You can now authenticate database administrators and operators for all instances on the computer.

  10. See "Granting Administrator and Operator Privileges for All Databases on a Computer".

Granting Administrator and Operator Privileges for All Databases on a Computer

You can grant database administrator (SYSDBA) and database operator (SYSOPER) privileges to DBAs for all databases on a computer.

To grant privileges for all databases on a computer:

  1. Choose Start > Programs > Oracle - HOME_NAME > Database Administration > Oracle Administration Assistant for Windows NT.

    Oracle Administration Assistant for Windows NT starts.

  2. Make an appropriate selection:

    If You Want to Grant...  Then... 

    Database administrator (SYSDBA) privileges 
    1. Right click OS Database Administrators - Computer.

    2. See section "Granting Administrator Privileges for All Databases on a Computer"

     

    Database operator (SYSOPER) privileges 
    1. Right click OS Database Operators - Computer.

    2. See section "Granting Operator Privileges For All Databases on a Computer"

     

Granting Administrator Privileges for All Databases on a Computer

To grant administrator (SYSDBA) privileges for all databases on a computer:

  1. Choose Add/Remove.

    The OS Database Administrators - Computer for hostname dialog box appears:


  2. Select the domain of the user to which to grant SYSDBA privileges from the Domain drop-down list box.

  3. Select the user.

  4. Click Add.

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

  5. Click OK.

Granting Operator Privileges For All Databases on a Computer

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

  1. Choose Add/Remove.

    The OS Database Operators - Computer for hostname dialog box appears:


  2. Select the domain of the user to which to grant SYSOPER privileges from the Domain drop-down list box.

  3. Select the user.

  4. Click Add.

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

  5. Click OK.

Connecting to a Database

Once you connect to a database, you can perform additional authentication tasks:

To connect to a database:

  1. Right-click the database instance to access (for example, ORCL) in the Microsoft Management Console scope pane:


  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.

    This Node...  Enables You To...  For More Information... 

    External OS Users 

    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 (non-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.  

    See "Creating a Nonprivileged Database User (External User)" 

    Local Roles 

    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.  

    See "Creating a Local Database Role" 

    External OS Roles 

    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. 

    See "Creating an External Role" 

    OS Database Administrators 

    Authenticate a Windows NT user with SYSDBA privileges for a specific instance on a computer. 

    See "Granting Administrator and Operator Privileges for a Single Database" 

    OS Database Operators 

    Authenticate a Windows NT user with SYSOPER privileges for a specific instance on a computer. 

    See "Granting Administrator and Operator Privileges for a Single Database" 

Troubleshooting Connection Problems

Viewing Database Authentication Parameter Settings

To view database authentication parameter settings:

  1. Right click the database.

  2. Choose Properties.

  3. View the following database properties:


    Parameter  Description 

    OS_AUTHENT_PREFIX 

    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 user name and password. The value of this parameter is attached to the beginning of every user's Windows user name. By default, the parameter is set to none ("") during Oracle8i database creation. Create Oracle users in the database without the prefix OPS$, which was needed for Oracle7 and Oracle8 8.0.x.

    Therefore, a Windows domain user name of FRANK is authenticated as user name FRANK. You can set this parameter to an appropriate value. For example, if you set this parameter to XYZ, the Windows NT domain user FRANK is authenticated as user XYZFRANK. 

    OS_ROLES 

    OS_ROLES is an INIT.ORA file parameter that, if set to TRUE, enables the Windows NT operating system to manage the authorization of external roles for database users. 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 the granting and revoking of roles for database users. See section "Understanding the OS_ROLES Parameter" for more information. 

Understanding the OS_ROLES Parameter

OS_ROLES is a parameter in the INIT.ORA file that, if set to TRUE, enables the Windows NT operating system to manage the authorization of external roles for database users. 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 the 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, 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 Domain\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, and can be edited or revoked.

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

Creating a Nonprivileged Database User (External User)

You can create a nonprivileged database user (external user).

To create a nonprivileged database user:

  1. Follow the steps in "Connecting to a Database" to connect to a database.

  2. Right click External OS Users.

  3. Choose Create.


    The Create External OS User Wizard starts:


  4. 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 user now appears in the New External OS Users window.

    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 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.  

  7. Click Next.


  8. 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. Double-click the tablespace to assign a tablespace quota in the Tablespace Quota window. This assigns profile and tablespace information to the users, and grants database roles.

  10. Click Next.


  11. Select the database roles to grant to the new external users.

  12. Click the Grant button.

  13. Click Finish.

  14. Right click the external user for which you want to view information and select Properties.

    The assigned properties appear.

Creating a Local Database Role

You can create a local database role.

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. Click Create.

    The Create Local Role wizard appears:


  4. Enter a local role name to use. A local role is a role that is managed by the Oracle database.

  5. Select None if you want a user to use this local role without being required to enter a password.

  6. Select Password if you want the 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 the Oracle8i Administrator's Guide for additional information.

  7. Enter the password to use with this role.

  8. Confirm the password by entering it a second time.

  9. Click Next.


  10. Select appropriate system privileges to assign to the local role.

  11. 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 click Revoke.

  12. If you want to grant the Admin Option to this role, click the value in the Admin Option column to display a drop-down list box. This enables you to select Yes.

  13. Click Next.

  14. Select appropriate roles to assign to the local role. Both local roles and external roles appear in this list.


  15. 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 click Revoke.

  16. Click Finish.

Creating an External Role

You can create external roles.

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. Click Create.

    Note:

    This wizard is only available if you set the INIT.ORA parameter OS_ROLES to TRUE and restart the Oracle database.

    "Authentication: External" appears on this page to indicate that only external roles can be created.  


  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.


  6. Select appropriate system privileges to assign to the external role.

  7. Click 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 click Revoke.

  9. If you want to grant the Admin Option to this role, click the value in the Admin Option column to display a drop-down list box. This enables you to select Yes.

  10. Click Next.


  11. Select appropriate roles to assign to the external role.

  12. Click Grant to grant the selected roles to the external role. Both local roles and external roles appear in this list.

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

  13. Click Finish.

Granting Administrator and Operator Privileges for a Single Database

You can grant database administrator (SYSDBA) and database operator (SYSOPER) privileges to DBAs for a single database on a computer.

To grant 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. Make an appropriate selection:

    If You Want to Grant...  Then... 

    Database administrator (SYSDBA) privileges 
    1. Right click OS Database Administrators.

    2. See section "Granting Administrator Privileges for a Single Database"

     

    Database operator (SYSOPER) privileges 
    1. Right click OS Database Operators.

    2. See section "Granting Operator Privileges for A Single Database"

     

Granting Administrator Privileges for a Single Database

To grant administrator (SYSDBA) privileges for a single database:

  1. Choose Add/Remove.

    The OS Database Administrators for instance dialog box (MARK in this example) appears:


  2. Select the domain of the user to which to grant SYSDBA privileges from the NT Domain Users and Groups drop-down list box.

  3. Select the user. The user now appears in the OS Database Administrators window.

  4. Click OK.

Granting Operator Privileges for A Single Database

To grant operator (SYSOPER) privileges for a single database:

  1. Choose Add/Remove.

    The OS Database Operators for instance dialog box (MARK in this example) appears:


  2. Select the domain of the user to which to grant SYSOPER privileges from the NT Domain Users and Groups drop-down list box.

  3. Select the user.

  4. Click Add.

    The user now appears in the OS Database Operators window.

  5. Click OK.

Manually Administering External Users and Roles

Manual configuration involves using Oracle command line tools, editing the registry with REGEDT32, and creating local groups in Windows NT User Manager. This enables you to:

This section describes:

Creating a Nonprivileged Database User (External 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 Oracle8i 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 Oracle8i 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 Oracle8i Database Server

To perform authentication tasks on an Oracle8i database server:

  1. Add the OS_AUTHENT_PREFIX parameter to your INIT.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. Using the OS_AUTHENT_PREFIX parameter with Windows native authentication methods is the recommended method for performing secure, trusted client connections to your server.

  2. Set OS_AUTHENT_PREFIX to an appropriate value. Values are case insensitive. 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 INIT.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.

  3. 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.

  4. Follow the substeps below to create a new registry parameter 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 the registry editor from the MS-DOS 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 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 are 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.

  5. Ensure that you have the following line in your ORACLE_BASE\ORACLE_HOME\NETWORK\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (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 an operating system-authenticated user by entering the following:

    If Authenticating a...  Then Enter... 

    Local user name 

    SQL> CREATE USER XYZFRANK IDENTIFIED EXTERNALLY; 

    Domain user name 

    SQL> 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. 

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

    If Authenticating a...  Then Enter... 

    Local user name 

    SQL> GRANT RESOURCE TO XYZFRANK; 

     

    SQL> GRANT CONNECT TO XYZFRANK; 

    Domain user name1 

    SQL> GRANT RESOURCE TO "XYZSALES\FRANK"; 

     

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

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

    SQL> CONNECT INTERNAL
  11. Shut down the database: 

    SQL> SHUTDOWN
  12. Restart the database: 

    SQL> STARTUP

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

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).

  2. Ensure that you have the following line in your ORACLE_BASE\ ORACLE_HOME\NETWORK\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)
  3. Use Net8 Assistant to configure a network connection from your client computer to the Windows NT server on which your Oracle8i database is installed. See the Net8 Administrator's Guide for instructions.

  4. Start SQL*Plus: 

    C:\> SQLPLUS
  5. Connect to your Windows NT server: 

    SQL> CONNECT /@NET_SERVICE_NAME

    where NET_SERVICE_NAME is the Net8 network service name for the Oracle8i database that you created in Step 3.

    The Oracle8i 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 Oracle8i database as local or domain user FRANK by viewing the roles assigned in Step 9 of "Step 1: Perform Authentication Tasks on the Oracle8i 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 Oracle8i user name 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

    Attention:

    Automatic authorization is supported for all Net8 protocols.  

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 DBAs. This enables DBAs to issue the following commands from a client computer and connect to the Oracle8i database without entering a password:

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

Local Group  This Local Group Includes All... 

ORA_OPER 

SYSOPER database privileges; applicable for all databases on a computer. 

ORA_DBA 1 

SYSDBA database privileges; applicable for all databases on a computer. 

ORA_SID_DBA 

SYSDBA database privileges; applicable only for a single database on a computer (identified by the SID). 

ORA_SID_OPER 

SYSOPER database privileges; applicable only for a single database on a computer (identified by the SID). 
1 ORA_DBA is automatically created during installation. See section "Automatically Enabling Operating System Authentication During Installation" for information.

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 Oracle8i Database Server

To perform authentication tasks on the Oracle8i database server:

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

  2. Choose New Local Group from the User Menu.

    The New Local Group dialog box appears.

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


  4. Click Add.

    The Add Users and Groups dialog box appears:


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

  6. Click OK.

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


  7. Click OK.

  8. Exit User Manager.

  9. Ensure that you have the following line in your ORACLE_BASE \ORACLE_HOME\NETWORK\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)
  10. In the registry in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID set the parameter OSAUTH_PREFIX_DOMAIN to TRUE.

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).

  2. Ensure that you have the following line in your ORACLE_BASE\ORACLE_HOME\NETWORK\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)
  3. Use Net8 Assistant to configure a network connection from your client computer to the Windows NT server on which your Oracle8i database is installed. See Net8 Administrator's Guide for instructions.

  4. Start SQL*Plus: 

    C:\> SQLPLUS
  5. Connect to the Oracle8i database: 

    SQL> SET INSTANCE NET_SERVICE_NAME

    where NET_SERVICE_NAME is the Net8 network service name for the Oracle8i 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 Oracle8i Database Server":

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

    ORA_DBA or ORA_SID_DBA 

    SQL> CONNECT / AS SYSOPER 

     

    or 

     

    SQL> CONNECT / AS SYSDBA 

    ORA_OPER or ORA_SID_OPER 

    SQL> 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. If you installed your Oracle8i database, your Windows NT user name was automatically added to a Windows NT local group called ORA_DBA. This enables you to automatically connect as INTERNAL without a password. However, if you or the user to which to assign this feature did not install your Oracle8i database, then you must follow the instructions in this section.

To connect as INTERNAL without a password, 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 1 

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. 
1 ORA_DBA is automatically created during installation. See section "Automatically Enabling Operating System Authentication During Installation" for information.

This enables you to log into a local computer or a Windows NT domain. In the domain, your Oracle8i 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 Oracle8i database without a password.

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

Step 1: Perform Authentication Tasks on the Oracle8i Database Server

To perform authentication tasks on the Oracle8i database server:

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

  2. Ensure that you have the following line in your ORACLE_BASE\ORACLE_HOME\NETWORK\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.

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).

  2. Ensure that you have the following line in your ORACLE_BASE\ORACLE_HOME\NETWORK\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)
  3. Use Net8 Assistant to configure a network connection from your client computer to your Oracle8i database. See Net8 Administrator's Guide for instructions.

  4. Start SQL*Plus: 

    C:\> SQLPLUS
  5. Connect to the Oracle8i database: 

    SQL> SET INSTANCE NET_SERVICE_NAME

    where NET_SERVICE_NAME is the Net8 network service name for the Oracle8i database that you created in Step 3.

  6. Connect to your Windows NT server: 

    SQL> CONNECT INTERNAL

    You are connected to the Windows NT server.

Creating an External Role

This section describes how to grant Oracle8i 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. For example:

If You...  Then... 
  1. Enable external roles.

 

You only receive the roles granted to DTMSDOM\FRANK, and not the roles granted to SCOTT. 
  1. Log onto a Windows NT domain with your domain user name; for example, SALES\FRANK, where SALES is the domain name and FRANK is the domain user name.

 
  1. Connect to the Oracle8i database as Oracle database user SCOTT.

 

 

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

Step 1: Perform Authentication Tasks on the Oracle8i Database Server

To perform authentication tasks on the Oracle8i database server:

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

  2. Set OS_ROLES to TRUE.

    The default setting for this parameter is FALSE.

  3. Ensure that you have the following line in your ORACLE_BASE \ORACLE_HOME\NETWORK\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)
  4. Start SQL*Plus: 

    C:\> SQLPLUS
  5. Connect to your Windows NT server: 

    SQL> CONNECT INTERNAL
  6. Create a new database role: 

    SQL> 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.

  7. Grant Oracle roles to DBSALES3 that 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 with the INTERNAL DBA name: 

    SQL> CONNECT INTERNAL
  9. Shut down the database: 

    SQL> SHUTDOWN
  10. Restart the database: 

    SQL> STARTUP
  11. Open the Windows NT User Manager.

  12. Choose New Local Group from the User menu.

    The New Local Group dialog box appears:


  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 

    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.

  14. Click Add.

    The Add Users and Groups dialog box appears:


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

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

  17. Click OK.

  18. 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).

  2. Ensure that you have the following line in your ORACLE_BASE \ORACLE_HOME\NETWORK\ADMIN\SQLNET.ORA file: 

    SQLNET.AUTHENTICATION_SERVICES = (NTS)
  3. Use Net8 Assistant to configure a network connection from your client computer to your Oracle8i database. See Net8 Administrator's Guide for instructions.

  4. Start SQL*Plus: 

    C:\> SQLPLUS
  5. Connect to the correct instance: 

    SQL> SET INSTANCE NET_SERVICE_NAME
where NET_SERVICE_NAME is the Net8 service name for the Oracle8i database 
that you created in Step 3.
  6. Connect to the Oracle8i database: 

    SQL> 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 name and the Oracle-specific Windows NT local groups to which the user belongs (for example, ORA_SID_DBSALES1 or ORA_SID_DBSALES4_DA).

Administering Enterprise Users and Roles

Use Oracle Enterprise Security Manager to create and manage enterprise users, roles, and domains. Oracle Enterprise Security Manager is included as an integrated application of the Oracle Enterprise Manager Console. See the Oracle Advanced Security Administrator's Guide for more information on using Oracle Enterprise Security Manager.

Note:

You can administer external users and roles in Windows 2000 domains, but you cannot use Oracle Enterprise Security Manager to perform this administration. See "Administering External Users and Roles" for more information on tools available for administering external users and roles. 

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

Library
Product
Contents
Index