10
Authenticating Database Users with Windows NT

This chapter describes how to authenticate Oracle8i database users with Windows NT. Specific topics discussed are:

• Authentication Overview
• Automatically Enabling Operating System Authentication During Installation

Authentication Using the Oracle Administration Assistant for Windows NT

• Adding a Computer
• Granting Database Administrator and Database Operator Privileges using Oracle Administration Assistant for Windows NT
• Connecting to a Database
• Create a Nonprivileged Database User using Oracle Administration Assistant for Windows NT
• Create a Local Database Role
• Create an External OS Role

Manual Authentication Using Windows NT

• Connecting Without a Password as a Nonprivileged Database User
• Connecting as SYSDBA and SYSOPER Without a Password
• Connecting as INTERNAL Without a Password
• Granting Database Roles through Windows NT

Authentication Overview

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

• Enabling users to connect to an Oracle8i database without supplying a user name or password
• Centralizing Oracle8i database user authorization information in Windows NT, which frees Oracle8i from storing or managing user passwords
• Allowing Oracle8i and Windows NT user names to be the same

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

In Oracle 8.1.5, by default, external users created in the database must be prefixed with the domain name. For example, for an NT user DOMAIN1\NTUSER1, the Oracle user created in the database must be DOMAIN1\NTUSER1. If you wish 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: For Windows NT authentication to work, the SQLNET.AUTHENTICATION_SERVICES parameter must be set by as follows in your ORACLE_BASE\ORACLE_HOME \NETWORK\ADMIN\SQLNET.ORA file on both client and server: SQLNET.AUTHENTICATION_SERVICES = (NTS)

Automatically Enabling Operating System Authentication During Installation

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

• Automatically created when Oracle8i Enterprise Edition is installed.
• A special Windows NT local group whose members automatically receive the SYSDBA privilege.

Enables you to:

• Connect to any local Oracle8i databases without a password by issuing commands such as the following:
• CONNECT INTERNAL
• CONNECT / AS SYSDBA
• Connect to Oracle8i databases without a password by issuing a command such as the following:
• CONNECT /@NET_SERVICE_NAME AS SYSDBA

where NET_SERVICE_NAME is the network service name of the Oracle8i database to which to connect.

• Perform database administration procedures such as starting and shutting down local databases
• Add additional Windows NT users to ORA_DBA, enabling them to have the SYSDBA privilege provided you have Administrator privileges.

Oracle Administration Assistant for Windows NT

Oracle Administration Assistant for Windows NT is a graphical user interface (GUI) tool that enables you to easily configure Oracle database administrators, operators, users, and roles to be authenticated by the Windows NT operating system. Oracle Administration Assistant for Windows NT enables you to:

• Configure regular Windows NT domain users and global groups to access the Oracle database without a password.
• Configure Windows NT database administrators (with the SYSDBA privilege) to access the Oracle database without a password.
• Configure Windows NT database operators (with the SYSOPER privilege) to access the Oracle database without a password.
• Create and grant local and external OS database roles to Windows NT domain users and global groups.

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

• Creating NT local groups that match the database system identifier (SID) and role.
• Assigning NT domain users to these local groups.
• Authenticating users in Server Manager or SQL*Plus line mode with the CREATE USER USERNAME IDENTIFIED EXTERNALLY syntax.

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 the database with the domain name as the prefix. Therefore, if you are managing Oracle 7.x or Oracle 8.0.x databases remotely, you must set the registry value OSAUTH_PREFIX_DOMAIN in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID to TRUE in the remote system.

Adding a Computer

When you use Oracle Administration Assistant for Windows NT for the first time, it will automatically add the local computer in the navigation tree. You may then add another computer by following steps:

1. Choose Start > Programs > Oracle - HOME_NAME > Enterprise Management > Database Administration Applications > Oracle Administration Assistant for Windows NT.
2. Right Click Computer.
3. Choose New > Computer
4. Specify the domain and hostname of the computer on which your Oracle database is installed, and click OK.
5. Double-click Computers to display the computer you added.
6. Double-click the computer you added. Several Oracle Administration Assistant for Windows NT nodes appear.
7. In the appropriate pull down menu select the domain location and name of the computer on which the Oracle database is installed.

8. Click OK. You can now access the Oracle database to perform authentication tasks.

Save your configuration in a console file by clicking Save on the Console pulldown menu.

Granting Database Administrator and Database Operator Privileges using Oracle Administration Assistant for Windows NT

You can grant the SYSOPER and SYSDBA privileges to DBAs using the Oracle Administration Assistant for Windows NT snap-in for the Microsoft Management Console.

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

Oracle Administration Assistant for Windows NT starts.

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

4. Highlight the name of a user or from the NT Domain Users and Groups box to grant SYSDBA privileges.
5. Click the Add button. The user will now appear in the OS Database Operators window.
6. Click OK.

To grant the SYSDBA privileges using the Oracle Administration Assistant for Windows NT snap-in in the Microsoft Management Console:

1. Right click the OS Database Administrators.
2. Choose Add/Remove.

3. Highlight the name of a user or from the NT Domain Users and Groups box to grant SYSDBA privileges.
4. Click the Add button. The user will now appear in the OS Database Administrators window.
5. Click OK.

Connecting 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. Several NT nodes appear beneath the instance, indicating that you are connected to the Oracle database. If these nodes do not appear, double-click the instance.
3. To connect a local computer the Oracle Administration Assistant will first try to connect as a SYSDBA to the database using Bequeath. To connect a remote computer Oracle Administration Assistant will try to connect using NT native authentication as a SYSDBA to the database using TCP/IP (port 1521 and 1526). If it is unsuccessful, the following dialog box will be displayed for you to enter information to connect to the database.

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 privilege. Therefore, you must enter an Oracle user name and password to access the database. To avoid being prompted with this dialog box again, configure your domain user to be a database administrator authenticated by the Windows NT operating system.

The dialog box shown above appeared because you are not using the TCP/IP networking protocol to connect to a remote Oracle database. Using SPX or Named Pipes causes this dialog box to appear each time you attempt a remote connection. To avoid having to respond to this dialog box, change to the TCP/IP networking protocol and use default port 1521 or 1526.

The dialog box shown above may also appear because the Oracle database is not running. Start the Oracle database.
 

Note:  For connections to local Oracle databases, the Bequeath networking protocol is automatically used. Bequeath is automatically installed with Net8 Server.

Net Service Name

Enter the net service name with which to connect to your Oracle database. The net service name is an alias for a specific database instance that you create with the graphical user interface tool Net8 Assistant or Net8 Easy Config. You must enter a net service name regardless of the authentication method you select below.

Database Authenticated

Select this option if you want to access the database with an Oracle user name and password. This user name and password must exist in the Oracle database. This user name must also have the SYSDBA privilege.

• Enter an Oracle database user name.
• Enter the password for the Oracle database user name.

OS Authenticated Connection as SYSDBA

Select this option if you want to access the database with the Windows NT domain user with which you are currently logged in. This Windows NT domain user must already be recognized by Windows NT as an authenticated user with SYSDBA privileges. Otherwise, your logon attempt will fail.

Database Properties

To view database properties right click the database, choose Properties.

Create a Nonprivileged Database User using Oracle Administration Assistant for Windows NT

You may create a Nonprivileged Database User using the Oracle Administration Assistant for Windows NT snap-in in the Microsoft Management Console.

Start the Microsoft Management Console.

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

Oracle Administration Assistant for Windows NT starts.

2. Right Click External OS Users.
3. Choose Create.

The Create External OS User Wizard is launched.

4. Choose the domain from the Domain pull down menu then from the NT Domain Users and Groups box select the name of the user or group you want to grant access to the database.
5. Click the Add button. The user will now appear in the New NT Authenticated 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.

6. Click Next.

The Step2 page allows you to choose which profile and tablespace information to assign to the user or group.

7. Select a profile for the new External OS 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.
8. In the Tablespace Quota window double-click the tablespace to assign a tablespace quota. It assigns profile and tablespace information to them, and grants database roles.
9. Click Next.

10. In the Step 3 page highlight the database role to the selected NT users and groups.
11. Click the Grant button.

To view the properties of an external OS user click on External OS Users in the Microsoft Management Console scope pane. Right click the external OS user for which you wish to view the properties and select Properties.

OS_ROLES

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 OS roles for database users. If OS_ROLES is set to TRUE only external OS roles will be displayed in the field Granted External OS Roles. By default, OS_ROLES is set to FALSE and only local roles will be displayed in the field Granted Roles. You must set OS_ROLES to TRUE and restart your Oracle database before you can create external OS 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 OS 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 OS 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 in the dialog box to revoke the external OS role from this global group (and therefore all its individual users).

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

Create a Local Database Role

You can create local database role using the Oracle Administration Assistant for Windows NT snap-in for the Microsoft Management Console.

1. In the Microsoft Management Console scope pane right click Local Roles for the Database for which you wish to create a local role.
2. Click Create.

3. Enter a Local Role name to use. A Local Role is a role that is managed by the Oracle database.
4. Select None if you want a user to be able to use this local role without being required to enter a password.
5. 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 Oracle8 Administrator's Guide for additional information.
6. Enter the password to use with this role.
7. Confirm the password by entering it a second time.
8. Click Next.

Available System Privileges

9. Select appropriate system privileges to assign to the local role.
10. Click Grant to grant the selected system privileges to the local role.

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

Click the value in the Admin Option column to display a drop-down list box. This box enables you to select Yes or No. Select Yes if you want to grant the Admin Option to this role.

11. Click Next.

Available Roles

Select appropriate roles to assign to the local role. Both local roles and External OS roles appear in this list.

12. Click Grant to grant the selected roles to the role.
13. Click Revoke to revoke the selected roles from the role.
14. The Granted Roles Displays the list of roles granted to the role. Both local roles and external OS roles can appear in this list. To revoke roles, make appropriate selections, then click Revoke.
15. Click Back to go to the previous step in this wizard.
16. Click Finish.

Create an External OS Role

You can create an External OS role using the Oracle Administration Assistant for Windows NT snap-in for the Microsoft Management Console.

1. In the Microsoft Management Console scope pane right click External OS Roles for the database you wish to create an external role.
2. 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 OS roles can be created.

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

Available System Privileges

5. Select appropriate system privileges to assign to the External OS role.
6. Click Grant to grant the selected system privileges to the External OS role.
7. Click Revoke to revoke the selected system privileges from the External OS role.
8. The Granted System Privileges box displays the list of system privileges granted to the external OS role. To revoke a system privilege, make an appropriate selection, then click Revoke.

Admin Option

Click the value in the Admin Option column to display a drop-down list box. This box enables you to select Yes or No. Select Yes if you want to grant the Admin Option to this role.

9. Click Next.

10. Select appropriate roles to assign to the External OS role.
11. Click Grant to grant the selected roles to the External OS role. Both Local roles and external OS roles appear in this list.

Click Revoke to revoke the selected roles from the External OS role.

The Granted Roles box displays the list of roles granted to the External OS role. Both Local roles and External OS roles can appear in this list.

12. Click Finish.

Connecting Without a Password as a Nonprivileged Database User

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

User Name	This User...
Local user FRANK	Logs into their local Windows NT client computer to access an 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
• Step 2: Perform Authentication Tasks on the Client Computer

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 NT 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. Ensure that you have the following line in your ORACLE_BASE\ORACLE_HOME\NETWORK\ADMIN\SQLNET.ORA file:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

5. Start SQL*Plus:

C:\> SQLPLUS

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

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

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

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

SQL> CONNECT INTERNAL

10. Shut down the database:

SQL> SHUTDOWN

11. 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 or Net8 Easy Config 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 8 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.

Connecting as SYSDBA and SYSOPER Without a Password

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

• CONNECT / AS SYSOPER
• CONNECT / AS SYSDBA

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 system identifiers (SIDs).
ORA_DBA 1	SYSDBA database privileges; applicable for all SIDs.
ORA_SID_DBA	SYSDBA database privileges; applicable only for the SID specified in the name.
ORA_SID_OPER	SYSOPER database privileges; applicable only for the SID specified in the name.

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
• Step 2: Perform Authentication Tasks on the Client Computer

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 or Net8 Easy Config 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. To do this, you must create one of the following new local Windows NT user groups and add a Windows NT operating system local or domain user to that group:

Local Group	This Local Group Includes All...
ORA_DBA 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
• Step 2: Perform Authentication Tasks on the Client Computer

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.
11. Connect to the database with the INTERNAL DBA name:

SQL> CONNECT INTERNAL

12. Shut down the database:

SQL> SHUTDOWN

13. Restart the database:

SQL> STARTUP

Step 2: Perform Authentication Tasks on the Client Computer

To perform authentication tasks on the client computer:

1. Create a Windows NT local or domain user name with the same user name and password that exist on the Windows NT server (if the appropriate user name does not currently exist).
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 or Net8 Easy Config 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.

Granting Database Roles through Windows NT

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

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

If You...	Then...
Enable operating system roles.	You only receive the roles granted to DTMSDOM\FRANK, and not the roles granted to SCOTT.
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.
Connect to the Oracle8i database as Oracle database user SCOTT.

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

• Step 1: Perform Authentication Tasks on the Oracle8i Database Server
• Step 2: Perform Authentication Tasks on the Client Computer

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 or Net8 Easy Config 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).

 
 

---