10 Administering External Users and Roles on Windows

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

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

Oracle Administration Assistant for Windows

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

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

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

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

Create local groups that match the database system identifier and role
Assign domain users to these local groups

Authenticate users in SQL*Plus with

SQL> CREATE USER username IDENTIFIED EXTERNALLY

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

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

Managing a Remote Computer

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

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOME_NAME

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

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

To assign a DNS name:

From the Start menu, select Settings, then select Control Panel, then select System, then Computer Name, and then click Change.
Enter the new computer name and a domain name, such as, US.ORACLE.COM.
Click More if you want to change the primary DNS suffix.

Adding a Computer and Saving Your Configuration

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

To add a computer to the Microsoft Management Console tree:

From the Start, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools and then select Administration Assistant for Windows.

Microsoft Management Console starts.
Double-click Oracle Managed Objects.

The Computer icon appears.
Right-click Computers.
Select New and then select Computer.

The Add Computer dialog appears.

Description of the illustration addcomp.gif
Specify the domain and computer name for the computer on which Oracle Database is installed.
Click OK.
Double-click Computers to display the computer you added.
Double-click the computer you added. Several nodes for authenticating database administrators and operators appear.

The OS Database Administrators - Computer node creates an operating system-authenticated database administrator with SYSDBA privileges for every database instance on the computer. The OS Database Operators - Computer node creates an operating system-authenticated database operator with SYSOPER privileges for every database instance on the computer.
Save your configuration in a console file by choosing Save in the Console main menu. You can now authenticate database administrators and operators for all instances on the computer.

Granting Administrator Privileges for All Databases on a Computer

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

Note:

If you use a domain account for database installation, then the domain user must be granted local administrative privileges. It is not sufficient if the domain user has inherited membership privileges from another group. You must ensure that, the user performing the installation must be in the same domain, if not it results in an NTS authentication failure.

From the Start, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools, and then select Administration Assistant for Windows.

Oracle Administration Assistant for Windows starts.
Right-click OS Database Administrators - Computer.
Click Add/Remove.

The OS Database Administrators - Computer for hostname dialog appears.

Description of the illustration ntdba.gif
Select the domain of the user to which to grant SYSDBA privileges from the Domain list.
Select the user.
Click Add.

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

Granting Operator Privileges for All Databases on a Computer

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

From the Start, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools and then select Administration Assistant for Windows.

Oracle Administration Assistant for Windows starts.
Right-click OS Database Operators - Computer.
Click Add/Remove.

The OS Database Operators - Computer for hostname dialog appears.

Description of the illustration osdboper.gif
Select the domain of the user to which to grant SYSOPER privileges from the Domain list.
Select the user.
Click Add.

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

Connecting to a Database

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

Troubleshooting Connection Problems

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

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

Description of the illustration cntdb4.gif

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

Description of the illustration cntdb2.gif

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

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

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

Viewing Database Authentication Parameter Settings

To view database authentication parameter settings:

Right-click the database.
Choose Properties.
The Properties dialog appears displaying the following parameter values:
- OS_AUTHENT_PREFIX
- OS_ROLES

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

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

OS_ROLES is an init.ora file parameter that, if set to true, enables the Windows operating system to manage authorization of an external role for a database user. By default, OS_ROLES is set to false. You must set OS_ROLES to true and restart Oracle Database before you can create external roles. If OS_ROLES is set to false, Oracle Database manages granting and revoking of roles for database users.

If OS_ROLES is set to true, and you assign an external role to a Windows global group, then it is granted only at the Windows global group level, and not at the level of the individual user in this global group. This means that you cannot revoke or edit the external role assigned to an individual user in this global group through the Roles tab of the User Name Properties dialog at a later time. Instead, you must use the field in the Assign External OS Roles to a Global Group dialog 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 Windows global group are not affected by this issue. They can be edited or revoked.

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

Creating an External Operating System User

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

To create an external operating system user:

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

Description of the illustration mmc4.gif
Choose Create.

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

Description of the illustration mmc5.gif
In Domain Users and Groups select the domain in which your Windows domain users and global groups are located.
Select the Windows domain users and global groups to which to grant access to the database.
Click Add. The selected users and groups now appear in the New External OS Users list.
Click Next. The Profile and Tablespace dialog appears.

Description of the illustration mmc6stp2.gif
In the Assigned Profile list, select a profile for the new external users. A profile is a named set of resource limits. If resource limits are enabled, Oracle Database 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.
In Tablespace Quota double-click the tablespace to assign a tablespace quota.
Click Next. The Roles dialog appears.

Description of the illustration step3.gif
In Available Roles select the database roles to grant to the new external users.
Click Grant.
Click Finish.
Right-click the external user for which you want to view information and select Properties.

The assigned properties appear.

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

Creating a Local Database Role

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

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

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

Description of the illustration locrol1.gif
Enter a local role name to use.
In Authentication select None if you want a user to use this local role without being required to enter a password.

Select Password if you want use of this role to be protected by a password. These roles can only be used by supplying an associated password with the SET ROLE command. See Oracle Database 2 Day DBA for additional information.

Enter the password to use with this role.

Confirm the password by entering it a second time.
Click Next. The System Privileges dialog appears.

Description of the illustration locrol2.gif
In Available System Privileges select the system privileges you want to assign to the local role.
Click Grant to grant the selected system privileges to the local role.

The Granted System Privileges field displays the list of system privileges granted to the local role. To revoke a system privilege, make an appropriate selection, then choose Revoke.
If you want to grant Admin Option to this role, click the value in the Admin Option column to display a list. This enables you to select Yes.
Click Next. The Roles dialog appears.

Description of the illustration locrol3.gif
In Available Roles select the roles you want to assign to the local role. Both local roles and external roles appear in this list.
Click Grant to grant the selected roles to the role.

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

Creating an External Operating System Role

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

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

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

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

Description of the illustration exrol1.gif
Enter an external role name to use. An external role is a role that is managed by the Windows operating system.
Click Next.

The System Privileges dialog appears.

Description of the illustration exrol2.gif
In Available System Privileges select the system privileges you want to assign to the external role.
Choose Grant to grant the selected system privileges to the external role.
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.
If you want to grant Admin Option to this role, choose the value in the Admin Option column to display a list. This enables you to select Yes.
Click Next.

The Roles dialog appears.

Description of the illustration exrol3.gif
In Available Roles select the roles you want to assign to the external role. Both local roles and external roles appear in this list.
Click Grant to grant the selected roles to the external role.

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

Granting Administrator Privileges for a Single Database

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

Follow the steps in "Connecting to a Database" to connect to a database.
Right-click OS Database Administrators.
Choose Add/Remove.

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

Description of the illustration dba_one.gif
In Domain Users and Groups select the domain of the user to which to grant SYSDBA privileges from the Domain list.
Select the user.

The user now appears in OS Database Administrators.
Click OK.

Granting Operator Privileges for a Single Database

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

Follow the steps in "Connecting to a Database" to connect to a database.
Right-click OS Database Operators.
Choose Add/Remove.

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

Description of the illustration oper_one.gif
In Domain Users and Groups select the domain of the user to which to grant SYSOPER privileges from the Domain list.
Select the user.
Click Add.

The user now appears in OS Database Operators.
Click OK.

Manually Administering External Users and Roles

Instead of using Oracle Administration Assistant for Windows, you can manually configure administrators, operators, users, and roles to be authenticated by the operating system. Manual configuration involves using Oracle Database command line tools, editing the registry, and creating local groups in Active Directory Users and Computers.

All of the following can be manually configured to access Oracle Database without a password:

External operating system users
Windows database administrators (with SYSDBA privilege)
Windows database operators (with SYSOPER privilege)

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

This section describes:

Manually Creating an External Operating System User
Manually Granting Administrator and Operator Privileges for Databases
Manually Creating an External Role
Manually Migrating Users

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

Manually Creating an External Operating System User

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

In the following procedure, two Windows usernames are authenticated:

Local user frank
Domain user frank on domain sales

Local user frank logs into its local Windows client computer to access an Oracle Database server, which 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 sales domain that includes many other Windows computers and resources, one of which contains an Oracle Database server. The domain user can access all the resources the domain provides with a single user name and password.

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

External User Authentication Tasks on the Oracle Database Server
External User Authentication Tasks on the Client Computer

External User Authentication Tasks on the Oracle Database Server

Perform the following external user authentication tasks on the Oracle Database server:

Add parameter OS_AUTHENT_PREFIX to your init.ora file.

The OS_AUTHENT_PREFIX value is prefixed to local or domain usernames attempting to connect to the server with the user's operating system name and password. The prefixed user name is compared with Oracle Database usernames in the database when a connection request is attempted. Using parameter OS_AUTHENT_PREFIX with Windows native authentication methods is the recommended method for performing secure, trusted client connections to your server.
Set a value for OS_AUTHENT_PREFIX. Your choices are:
- Any character string
  
  If you specify xyz, as in this procedure's example, then xyz is prefixed to the beginning of the Windows user name (for example, xyzfrank for local user frank or xyzsales\frank for domain user frank on domain sales). String values are case insensitive.
- "" (two double quotes with no space between)
  
  This option is recommended, because it eliminates the need for any prefix to Windows usernames (for example, frank for local user frank or sales\frank for domain user frank on domain sales).
- No value specified
  
  If you do not specify a value for OS_AUTHENT_PREFIX, it defaults to OPS$ (for example, OPS$frank for local user frank or OPS$sales\frank for domain user frank on domain sales).
Create a Windows local user name for frank with the Computer Management tool, or create a domain user name for frank with Active Directory Users and Computers (if the appropriate name does not currently exist). See your operating system documentation for detailed instructions.
Do this step only if you are not authenticating a domain name with a user (for example, just frank instead of frank on domain sales). Otherwise, go to step 8.
1. Start Registry Editor from the command prompt:
```
C:\> regedit
```
2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID, where ID is the Oracle home directory you want to edit.
3. Select Edit and then select Add Value.
  
  The Add Value dialog appears:
  
  Description of the illustration addval.gif
4. Enter OSAUTH_PREFIX_DOMAIN in the Value Name field.
5. Choose REG_EXPAND_SZ from the Data Type list.
6. Click OK.
  
  The String Editor dialog appears:
  
  Description of the illustration addstr.gif
Enter true in the String field to enable authentication at the domain level.

There may be multiple frank usernames on your network, including local user frank, domain user frank on sales, and possibly several domain users frank on other domains. Entering true enables the server to differentiate among them. Entering false causes the domain to be ignored and local user frank to become the default value of the operating system user returned to the server.
Click OK.

Registry Editor adds the parameter.
Choose Exit from the Registry menu.

Registry Editor exits.
Ensure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
Start SQL*Plus:
```
C:\> sqlplus /NOLOG
```
Connect to the database with the SYSTEM database administrator (DBA) name:
```
SQL> CONNECT SYSTEM
Enter password: system_password
```
Unless you have changed it, the SYSTEM password is MANAGER by default.
Create a local external user by entering:
```
SQL> CREATE USER xyzfrank IDENTIFIED EXTERNALLY;
```
where xyz is the value you chose for initialization parameter OS_AUTHENT_PREFIX, and frank is the Windows local username.
Grant a local external user database roles by entering:
```
SQL> GRANT DBA TO xyzfrank;
```
Create a domain external user by entering:
```
SQL> CREATE USER "XYZSALES\FRANK" IDENTIFIED EXTERNALLY;
```
where XYZ is the value you chose for initialization parameter OS_AUTHENT_PREFIX, and SALES\FRANK is the domain name and Windows domain username. Double quotes are required and the entire syntax must be in uppercase.
Grant a domain external user database roles by entering:
```
SQL> GRANT DBA TO "XYZSALES\FRANK";
```
Double quotes are required and the entire syntax must be in uppercase.
Connect to the database with the SYSDBA name:
```
SQL> CONNECT / AS SYSDBA
```
Shut down the database:
```
SQL> SHUTDOWN
```
Restart the database:
```
SQL> STARTUP
```
This causes the change to parameter OS_AUTHENT_PREFIX to take effect.

External User Authentication Tasks on the Client Computer

Perform the following external user authentication tasks on the client computer:

Create Windows local or domain user name frank with the same user name and password that exist on the Windows server (if the appropriate name does not currently exist).
Ensure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
Use Oracle Net Configuration Assistant to configure a network connection from your client computer to the Windows server on which Oracle Database is installed. See Oracle Database Net Services Administrator's Guide for instructions.
Start SQL*Plus:
```
C:\> sqlplus /NOLOG
```
Connect to your Windows server:
```
SQL> CONNECT /@connect_identifier
```
where connect_identifier is the net service name for Oracle Database.

Oracle Database searches the data dictionary for an automatic login user name corresponding to the Windows local or domain username, verifies it, and enables connection as xyzfrank or xyzsales\frank.
Verify that you have connected to Oracle Database as local or domain user frank by viewing the roles assigned in steps 12 or 14 of "External User Authentication Tasks on the Oracle Database Server".
```
SQL> SELECT * FROM USER_ROLE_PRIVS;
```
which outputs for local user frank:
```
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
XYZFRANK                       DBA                            NO  YES NO 
1 row selected.
```
or, for domain user frank:
```
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
XYZSALES\FRANK                 DBA                            NO  YES NO 
1 row selected.
```
Because the Oracle Database user name is the whole name xyzfrank or xyzsales\frank, each object created by xyzfrank or xyzsales\frank (that is, table, view, index, and so on) is prefixed by this name. For another user to reference the table shark owned by xyzfrank, for example, the user must enter:
```
SQL> SELECT * FROM xyzfrank.shark 
```
Note:
Automatic authorization is supported for all Oracle Net protocols.

Manually Granting Administrator and Operator Privileges for Databases

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

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

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

Table 10-1 Windows Local Groups with SYSDBA, SYSOPER, and SYSASM Privileges

Local Group	Privileges
`ORA_OPER`	`SYSOPER` privileges for all databases on a computer
`ORA_DBA`Note	`SYSDBA` privileges for all databases on a computer
`ORA_DBA`	`SYSASM` privileges for all databases on a computer
`ORA_SID_OPER`	`SYSOPER` privileges for a single database (identified by `SID`)
`ORA_SID_DBA`	`SYSDBA` privileges for a single database (identified by `SID`)
`ORA_SID_DBA`	`SYSASM` privileges for a single database (identified by `SID`)

Note

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

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

SYSDBA/SYSOPER/SYSASM Authentication Tasks on the Oracle Database Server
SYSDBA/SYSOPER/SYSASM Authentication Tasks on the Client Computer

SYSDBA/SYSOPER/SYSASM Authentication Tasks on the Oracle Database Server

Create a Windows local group corresponding to the privileges you want Windows to grant (see Table 10-1).
Add your database administrator usernames to this group.

See Also:
Your operating system documentation for instructions on managing users and groups
Ensure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
Start Registry Editor from the command prompt:
```
C:\>regedit
```
Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID

where ID is the Oracle home that you want to edit.
Set parameter OSAUTH_PREFIX_DOMAIN to true.

SYSDBA/SYSOPER/SYSASM Authentication Tasks on the Client Computer

Create a Windows local or domain user name with the same user name and password that exist on the Windows server (if the appropriate user name does not currently exist).
Ensure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
Use Oracle Net Configuration Assistant to configure a network connection from your client computer to the Windows server on which Oracle Database is installed. See Oracle Database Net Services Administrator's Guide for instructions.
Start SQL*Plus:
```
C:\> sqlplus /NOLOG
```
Connect to Oracle Database:
```
SQL> SET INSTANCE net_service_name
```
where net_service_name is the Oracle Net net service name for Oracle Database.
If you specified ORA_DBA or ORA_SID_DBA in step 1 of "SYSDBA/SYSOPER/SYSASM Authentication Tasks on the Oracle Database Server", then enter either of the following:
```
SQL> CONNECT / AS SYSOPER
SQL> CONNECT / AS SYSDBA
SQL> CONNECT / AS SYSASM
```
If you specified ORA_OPER or ORA_SID_OPER in step 1, then enter:
```
SQL> CONNECT / AS SYSOPER
```
You are now connected to the Windows server. If you connect with SYSDBA, you are given DBA privileges.

Manually Creating an External Role

This section describes how to grant Oracle Database roles to users directly through Windows (known as external roles). When you use Windows to authenticate users, Windows local groups can grant these users external roles.

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 Database roles at the same time.

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

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

External Role Authorization Tasks on the Oracle Database Server
External Role Authorization Tasks on the Client Computer

External Role Authorization Tasks on the Oracle Database Server

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

The default setting for this parameter is false.
Ensure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
Start SQL*Plus:
```
C:\> sqlplus /NOLOG
```
Connect to your Windows server:
```
SQL> CONNECT / AS SYSDBA
```
Create a new database role. You can give this new role whatever name you want. In this example the role is named DBSALES3:
```
SQL> CREATE ROLE DBSALES3 IDENTIFIED EXTERNALLY;
```
Grant to DBSALES3 whatever Oracle Database roles are appropriate to your database environment:
```
SQL> GRANT DBA TO DBSALES3 WITH ADMIN OPTION;
```
Connect to the database as SYSDBA:
```
SQL> CONNECT / AS SYSDBA
```
Shut down the database:
```
SQL> SHUTDOWN
```
Restart the database:
```
SQL> STARTUP
```
Create a Windows local group with the following syntax:
```
ORA_sid_rolename[_D][_A] 
```
where
- sid identifies the database instance
- rolename identifies the database role granted
- D indicates that this database role is to be a default role of the database user
- A indicates that this database role includes ADMIN OPTION
Characters D and A are optional. If specified, they must be preceded by an underscore.

For this example, ORA_orcl_dbsales3_D is created.
Add one or more Windows local or domain usernames to this group.

See Also:
Your operating system documentation for instructions on managing users and groups

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

Database Roles Windows Groups

dbsales1 ORA_ORCL_dbsales1

dbsales2 ORA_ORCL_dbsales2_a

dbsales3 ORA_ORCL_dbsales3_d

dbsales4 ORA_ORCL_dbsales4_da

Note:
When Oracle Database converts the group name to a role name, it changes the name to uppercase.

Database Roles	Windows Groups
`dbsales1`	`ORA_ORCL_dbsales1`
`dbsales2`	`ORA_ORCL_dbsales2_a`
`dbsales3`	`ORA_ORCL_dbsales3_d`
`dbsales4`	`ORA_ORCL_dbsales4_da`

External Role Authorization Tasks on the Client Computer

Create a Windows local or domain user name with the same user name and password that exist on the Windows server (if the appropriate user name does not currently exist).
Ensure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
Use Oracle Net Configuration Assistant to configure a network connection from your client computer to Oracle Database. See Oracle Database Net Services Administrator's Guide for instructions.
Start SQL*Plus:
```
C:\> sqlplus /NOLOG
```
Connect to the correct instance:
```
SQL> SET INSTANCE connect_identifier
```
where connect_identifier is the net service name for the Oracle Database connection that you created in Step 3.
Connect to Oracle Database:
```
SQL> CONNECT SCOTT
Enter password: password
```
You are connected to the Windows server over net service with Oracle Database user name scott. Roles applied to Oracle Database user name scott consist of all roles defined for the Windows user name that were previously mapped to the database roles (in this case, ORA_DBSALES3_D). All roles available under an authenticated connection are determined by the Windows user name and the Oracle-specific Windows local groups to which the user belongs (for example, ORA_SID_DBSALES1 or ORA_SID_DBSALES4_DA).

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

Manually Migrating Users

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

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

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

C:\ umu parameters

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

C:\ umu help=yes