| Oracle® Database Platform Guide 10g Release 2 (10.2) for Microsoft Windows (x64) Part Number B15688-03 |
|
|
View PDF |
| Oracle® Database Platform Guide 10g Release 2 (10.2) for Microsoft Windows (x64) Part Number B15688-03 |
|
|
View PDF |
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.
Note:
Both methods can also administer external users and roles in Windows 2000 domains, but cannot be used to administer an enterprise user or an enterprise role. See Oracle Database Advanced Security Administrator's Guide for more information on tools available for administering enterprise users and roles.This chapter contains these topics:
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 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
CREATE USER username IDENTIFIED EXTERNALLY
This section describes how to perform the following tasks with Oracle Administration Assistant for Windows:
Granting Administrator Privileges for All Databases on a Computer
Granting Operator Privileges for All Databases on a 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 Windows 2000 computer is not identified with a Domain Name System (DNS) domain name, you will receive the following error message:
Calling query w32RegQueries1.7.0.17.0 RegGetValue Key = HKEY_LOCAL_MACHINE SubKey = SYSTEM\CurrentControlSet\Services\Tcpip\Parameters Value = Domain Query Exception: GetValueKeyNotFoundException Query Exception Class: class oracle.sysman.oii.oiil.OiilQueryException ...
To assign a DNS name:
Choose Start > Settings > Control Panel > System > Network Identification > Property > More > Primary DNS.
Enter a domain name, such as US.ORACLE.COM.
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:
Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows.
Microsoft Management Console starts.
Double-click Oracle Managed Objects.
The Computer icon appears.
Right-click Computers.
Choose New > Computer.
The Add Computer dialog appears.
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.
To grant database administrator (SYSDBA) privileges to database administrators (DBAs) for all databases on a computer:
Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > 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.
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.
To grant database operator (SYSOPER) privileges to DBAs for all databases on a computer:
Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > 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.
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.
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.
See Also:
Oracle Database Advanced Security Administrator's Guide for more information on SSL supportTo connect to a database:
Right-click the database instance you want to access in the Microsoft Management Console scope pane. In the example here, a connection is to be made to ORCL:
Choose Connect Database.
If you connect to Oracle Database, the following Windows nodes appear beneath the instance. If these nodes do not appear, double-click the instance.
External OS Users
Local Roles
External OS Roles
OS Database Administrators
OS Database Operators
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.
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 running. Using a protocol other than TCP/IP (Named Pipes for example) causes this dialog to appear each time you attempt a remote connection.
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 username and password, select the Database Authenticated option. This username 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.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 username and password. The value of this parameter is attached to the beginning of every user's Windows username.
By default, the parameter is set to none ("") during Oracle Database creation. Therefore, a Windows domain username of frank is authenticated as username 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.
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.
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.
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.
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.
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.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.
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.
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.
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.
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 ifinit.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.
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.
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.
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.
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:
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.
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:
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.
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 Granting Administrator and Operator Privileges for Databases
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.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 username 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 username and password.
The procedure is divided into two sets of tasks performed on different computers:
Add parameter OS_AUTHENT_PREFIX to your init.ora file.
The OS_AUTHENT_PREFIX value is prefixed to local or domain usernames attempting to connect to the server with the user's operating system name and password. The prefixed username is compared with Oracle 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 username (for example, xyzfrank for local user frank or xyzsales\frank for domain user frank on domain sales). String values are case insensitive.
"" (two double quotes with no space between)
This option is recommended, because it eliminates the need for any prefix to Windows 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 username for frank with the Computer Management tool, or create a domain username 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.
Start Registry Editor from the command prompt:
C:\> regedt
Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID, where ID is the Oracle home directory you want to edit.
Choose Edit > Add Value.
The Add Value dialog appears:
Enter OSAUTH_PREFIX_DOMAIN in the Value Name field.
Choose REG_EXPAND_SZ from the Data Type list.
Click OK.
The String Editor dialog appears:
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
Enter user-name: 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.
Create Windows local or domain username frank with the same username 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 username 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 username 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.This section describes how to enable Windows to grant the database administrator (SYSDBA) and database operator (SYSOPER) privileges to database administrators. With this privilege, database administrators can issue the following commands from a client computer and connect to Oracle Database without entering a password:
CONNECT / AS SYSOPER CONNECT / AS SYSDBA
To enable this feature, the Windows local or domain username of the database administrator must belong to one of the Windows local groups listed in Table 9-1.
Table 9-1 Windows Local Groups with SYSDBA and SYSOPER Privileges
| Local Group | Privileges |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Footnote 1 ORA_DBA is automatically created during installation. See section "Operating System Authentication Enabled at Installation" for information.
The manual procedure for enabling database administrators to connect as SYSOPER or SYSDBA without a password is divided into two sets of tasks performed on different computers:
Create a Windows local group corresponding to the privileges you want Windows to grant (see Table 9-1).
Add your database administrator usernames to this group.
See Also:
Your operating system documentation for instructions on managing users and groupsEnsure that parameter SQLNET.AUTHENTICATION_SERVICES in file sqlnet.ora contains nts.
Start Registry Editor from the command prompt:
C:\>regedt
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.
Create a Windows local or domain username with the same username and password that exist on the Windows server (if the appropriate username 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 Authentication Tasks on the Oracle Database Server", then enter either of the following:
SQL> CONNECT / AS SYSOPER SQL> CONNECT / AS SYSDBA
If you specified ORA_OPER or ORA_SID_OPER in step 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.
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 username 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:
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 groupsYou 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.Create a Windows local or domain username with the same username and password that exist on the Windows server (if the appropriate username 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/tiger AS SYSDBA
You are connected to the Windows server over net service with Oracle Database username scott/tiger. Roles applied to Oracle Database username scott consist of all roles defined for the Windows username that were previously mapped to the database roles (in this case, ORA_DBSALES3_D). All roles available under an authenticated connection are determined by the Windows username 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.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
See Also:
"Using the User Migration Utility" in Oracle Database Advanced Security Administrator's Guide