Release 1 (9.0.1) for Windows
Part Number A90165-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Network, Directory, and Security Guide Release 1 (9.0.1) for Windows Part Number A90165-01 |
|
This chapter describes the administration of external users and roles.
This chapter contains these topics:
There are two methods for administering external users and roles:
Both methods can also administer external users and roles in Windows 2000 domains, but cannot be used to administer enterprise users and roles. See "Administering Enterprise Users and Roles" for more information on tools available for administering enterprise users and roles.
Note:
Oracle Administration Assistant for Windows NT runs from the Microsoft Management Console and enables you to configure the following Oracle database users and roles to be authenticated by the Windows operating system:
SYSDBA privilege) to access the Oracle database without a password.
SYSOPER privilege) to access the Oracle database without a password.
Oracle Administration Assistant for Windows NT eliminates the need to manually:
CREATE USER username IDENTIFIED EXTERNALLY syntax.
This section describes how to perform the following tasks with Oracle Administration Assistant for Windows NT:
When you use Oracle Administration Assistant for Windows NT for the first time, it adds the local computer in the navigation tree. You can then add other computers.
To add a computer to the Microsoft Management Console tree:
The Microsoft Management Console starts.
The Computer icon appears.
The Add Computer dialog box appears.
You can now authenticate database administrators and operators for all instances on the computer.
You can grant database administrator (SYSDBA) and database operator (SYSOPER) privileges to DBAs for all databases on a computer.
To grant privileges for all databases on a computer:
Oracle Administration Assistant for Windows NT starts.
| If You Want to Grant... | Then... |
|---|---|
|
Database administrator ( |
|
|
Database operator ( |
|
To grant administrator (SYSDBA) privileges for all databases on a computer:
The OS Database Administrators - Computer for hostname dialog box appears:
SYSDBA privileges from the Domain drop-down list box.
The user now appears in the OS Database Administrators - Computer window.
To grant operator (SYSOPER) privileges for all databases on a computer:
The OS Database Operators - Computer for hostname dialog box appears:
SYSOPER privileges from the Domain drop-down list box.
The user now appears in the OS Database Operators - Computer window.
Once you connect to a database, you can perform additional authentication tasks:
To connect to a database:
ORCL) in the Microsoft Management Console scope pane:
Text description of the illustration cntdb.gif
To enable Secure Sockets Layer (SSL) when connecting to an Oracle database, do not use the default user account in the Windows NT Services dialog box when starting the Oracle service and the listener service. Start these services in the same user account as the wallet created in Oracle Wallet Manager. If the Oracle service and the listener service are started in the default user accounts, 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.
If you connect to the Oracle database, the following Windows NT nodes appear beneath the instance. If these nodes do not appear, double-click the instance.
| This Node... | Enables You To... | For More Information... |
|---|---|---|
|
External OS Users |
Authenticate a Windows NT user to access the Oracle database as an external user without being prompted for a password. External users are typically regular database users (non-database administrators) to which you assign standard database roles (such as |
See "Creating a Nonprivileged Database User (External User)" |
|
Local Roles |
Create a role and have it managed by the database. Once a local role is created, you can grant or revoke that role to a database user. |
|
|
External OS Roles |
Create an external role and have it managed by the Windows operating system. Once an external role is created, you can grant or revoke that role to a database user. |
|
|
OS Database Administrators |
Authenticate a Windows NT user with |
See "Granting Administrator and Operator Privileges for a Single Database" |
|
OS Database Operators |
Authenticate a Windows NT user with |
See "Granting Administrator and Operator Privileges for a Single Database" |
When connecting to a local computer, Oracle Administration Assistant for Windows NT first tries to connect as a SYSDBA to the database using the Bequeath networking protocol. When connecting to a remote computer, Oracle Administration Assistant for Windows NT tries to connect using Windows native authentication as a SYSDBA to the database using the TCP/IP networking protocol (port 1521 or the deprecated 1526). If it is unsuccessful, the dialog boxes shown in Table 2-1 appear and prompt you to enter information to connect to the database:
| This Dialog Box Appears... | Because... |
|---|---|
Text description of the illustration cntdb4.gif |
The Windows NT domain user with which you are attempting to connect to the Oracle database is not recognized as an authenticated user with Enter an Oracle username 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.
|
Text description of the illustration cntdb2.gif |
You are not using the TCP/IP networking protocol to connect to a remote Oracle database or the Oracle database is not running. Using a protocol other than TCP/IP (Named Pipes for example) causes this dialog box to appear each time you attempt a remote connection. If you do not want this dialog to appear each time change to the TCP/IP protocol and make sure the Oracle Net Services listener is for the database listening on the default port 1521 (or the deprecated default port 1526). Otherwise, this dialog appears every time. Ensure also that the Oracle database is started.
|
To view database authentication parameter settings:
| Parameter | Description |
|---|---|
|
|
Therefore, a Windows domain username of |
|
|
|
OS_ROLES is a parameter in the init.ora file that, if set to true, enables the Windows NT operating system to manage the authorization of external roles for database users. You must set OS_ROLES to true and restart your Oracle database before you can create external roles.
If OS_ROLES is set to false, the Oracle database manages the granting and revoking of roles for database users.
If OS_ROLES is set to true and you assign an external role to an NT global group, it is granted only at the global group level, and not at the level of the individual user in this global group. This means that you cannot revoke or edit the external role assigned to an individual user in this global group through the Roles tab of the User Name Properties dialog box at a later time. Instead, you must use the Assign External OS Roles to an NT Global Group field in the dialog box to revoke the external role from this global group (and therefore all its individual users).
External roles assigned to an individual domain user or local roles (with OS_ROLES set to false) assigned to an individual domain user or NT global group are not affected by this issue, and can be edited or revoked.
If OS_ROLES is set to true, you cannot grant local roles in the database to any database user. You must grant the roles through Windows NT. See "Creating a Local Database Role" and "Creating an External Role" for more information.
You can create a nonprivileged database user (external user).
To create a nonprivileged database user:
Text description of the illustration mmc4.gif
The Create External OS User Wizard starts:
The user now appears in the New External OS Users window.
The assigned properties appear.
You can create a local database role.
To create a local database role:
The Create Local Role wizard appears:
SET ROLE command. See Oracle9i Database Administrator's Guide for additional information.
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.
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.
You can create external roles.
To create an external role:
The Granted Roles field displays the list of roles granted to the external role.
You can grant database administrator (SYSDBA) and database operator (SYSOPER) privileges to database administrators for a single database on a computer.
To grant privileges for a single database:
orcl) in the Microsoft Management Console scope pane.
Several icons, including OS Database Administrators and OS Database Operators, appear.
| If You Want to Grant... | Then... |
|---|---|
|
Database administrator ( |
|
|
Database operator ( |
|
To grant administrator (SYSDBA) privileges for a single database:
The OS Database Administrators for instance dialog box (MARK in this example) appears:
SYSDBA privileges from the NT Domain Users and Groups drop-down list box.
To grant operator (SYSOPER) privileges for a single database:
The OS Database Operators for instance dialog box (mark in this example) appears:
SYSOPER privileges from the NT Domain Users and Groups drop-down list box.
The user now appears in the OS Database Operators window.
Manual configuration involves using Oracle command line tools, editing the registry, and creating local groups in Windows NT User Manager. This enables you to:
SYSDBA privilege) to access the Oracle database without a password.
SYSOPER privilege) to access the Oracle database without a password.
This section describes:
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. See Table 2-2. When you use Windows NT to authenticate nonprivileged database users, your database relies solely on Windows NT to restrict access to database usernames. In the steps below, the following Windows NT usernames are authenticated:
The local and domain username frank and the domain sales are used in the steps. Substitute the appropriate local and domain username and domain name for your environment.
Follow the subsequent steps to connect without a password as a nonprivileged database user:
To perform authentication tasks on an Oracle9i database server:
OS_AUTHENT_PREFIX parameter 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 the Oracle usernames in the database when a connection request is attempted. Using the OS_AUTHENT_PREFIX parameter with Windows native authentication methods is the recommended method for performing secure, trusted client connections to your server.
OS_AUTHENT_PREFIX to an appropriate value. Values are case insensitive. For example:
The parameter value xyz is used in the subsequent steps. Substitute xyz with the value you set for OS_AUTHENT_PREFIX.
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.
frank instead of frank on domain sales). Otherwise, go to step 5.
C:\> regedt32
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID, where ID is the Oracle home directory you want to edit.
The Add Value dialog box appears:
OSAUTH_PREFIX_DOMAIN in the Value Name field.
REG_EXPAND_SZ from the Data Type drop-down list box.
The String Editor dialog box appears:
true in the String field to enable authentication at the domain level.
true enables the server to differentiate between multiple frank usernames, whether they are local user frank, domain user frank on sales, or domain user frank on another domain in your network. Entering false causes the domain to be ignored and local user frank to become the default value of the operating system user returned to the server.
The Registry Editor adds the parameter.
The String Editor exits.
SQLNET.AUTHENTICATION_SERVICES parameter in the sqlnet.ora file contains nts.
C:\> SQLPLUS
SYSTEM database administrator (DBA) name:
SQL> CONNECT
Enter user-name: SYSTEM/password
Unless you have changed it, the SYSTEM password is MANAGER by default.
| If Authenticating a... | Then Enter... |
|---|---|
|
Local username |
|
|
Domain username |
|
frank or domain user frank appropriate database roles:
| If Authenticating a... | Then Enter... |
|---|---|
|
Local username |
|
|
|
|
|
Domain usernameFoot 1 |
|
|
|
|
|
1
Enter the syntax for domain users in uppercase and with double quotes around the domain username. |
SYSDBA name:
SQL> CONNECT /ASSYSDBA
SQL> SHUTDOWN
SQL> STARTUP
This causes the change to the OS_AUTHENT_PREFIX parameter value to take affect.
To perform authentication tasks on the client computer:
frank with the same username and password that exist on the Windows NT server (if the appropriate name does not currently exist).
SQLNET.AUTHENTICATION_SERVICES parameter in the sqlnet.ora file contains nts.
C:\> SQLPLUS / NOLOG
SQL> CONNECT /@connect_identifier
where connect_identifier is the net service name for the Oracle9i database.
The Oracle9i database searches the data dictionary for an automatic login username corresponding to the Windows NT local or domain username, verifies it, and enables connection as xyzfrank or xyzsales\frank.
frank by viewing the roles assigned in step 5 of "Task 2: Perform Authentication Tasks on the Client Computer".
SQL> SELECT * FROM USER_ROLE_PRIVS;
which outputs for local user frank:
USERNAME GRANTED_ROLE ADM DEF OS_ ------------------------------ ------------------------------ --- --- --- XYZFRANK CONNECT NO YES NO XYZFRANK RESOURCE NO YES NO 2 rows selected.
or, for domain user frank:
USERNAME GRANTED_ROLE ADM DEF OS_ ------------------------------ ------------------------------ --- --- --- XYZSALES\FRANK CONNECT NO YES NO XYZSALES\FRANK RESOURCE NO YES NO 2 rows selected.
As the Oracle9i username is the whole name xyzfrank or xyzsales\frank, all objects created by xyzfrank or xyzsales\frank (that is, tables, views, indexes, and so on) are prefixed by this name. For another user to reference the table shark owned by xyzfrank, for example, the user must enter:
SQL> SELECT * FROM xyzfrank.shark
This section describes how to enable Windows NT to grant the database administrator (SYSDBA) and database operator (SYSOPER) privileges to database administrators. This enables database administrators to issue the following commands from a client computer and connect to the Oracle9i database without entering a password:
To enable this feature, the Windows NT local or domain username of the client must belong to one of the following four Windows NT local groups on the server. See Table 2-3.
Table 2-3 Windows NT Local Groups
| Local Group | This Local Group Includes All... |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
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. See Table 2-4.
| This Privilege... | Maps to the Local Group... |
|---|---|
|
|
|
|
|
|
Follow these steps to connect as SYSOPER or SYSDBA without a password:
To perform authentication tasks on the Oracle9i database server:
The New Local Group dialog box appears.
ORCL.
The Add Users and Groups dialog box appears:
Your selection is added to the Members field of the New Local Group dialog box:
SQLNET.AUTHENTICATION_SERVICES parameter in the sqlnet.ora file contains nts.
C:\>regedt32
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID.
OSAUTH_PREFIX_DOMAIN to true where ID is the Oracle home that you want to edit.
To perform authentication tasks on the client computer:
SQLNET.AUTHENTICATION_SERVICES parameter in the sqlnet.ora file contains nts.
C:\> sqlplus
SQL> SET INSTANCE net_service_name
where net_service_name is the Oracle Net network service name for the Oracle9i database.
SYSOPER or SYSDBA based on the local group you specified in step 3 of "Task 1: Perform Authentication Tasks on the Oracle9i Database Server":
| If The Local Group Is... | Then Enter... |
|---|---|
|
|
|
|
|
or |
|
|
|
|
|
|
You are connected to the Windows NT server. If you connect with SYSDBA, you are given DBA privileges.
This section describes how to grant Oracle9i database roles to users directly through Windows NT (known as external roles). When you use Windows NT to authenticate users, Windows NT local groups can grant these users external roles. Through User Manager, you can create, grant, or revoke external roles to users.
All privileges for these roles are active when the user connects. When using external roles, all roles are granted and managed through the operating system. You cannot use both external roles and Oracle roles at the same time. For example see Table 2-5:
| If You... | Then... |
|---|---|
|
|
|
|
You only receive the roles granted to |
Follow the subsequent tasks to grant external roles with Windows NT:
To perform authentication tasks on the Oracle9i database server:
OS_ROLES initialization parameter to the init.ora file.
OS_ROLES to true.
The default setting for this parameter is false.
SQLNET.AUTHENTICATION_SERVICES parameter in the sqlnet.ora file contains nts.
C:\> SQLPLUS / NOLOG
SQL> CONNECT /ASSYSDBA
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.
DBSALES3 that are appropriate to your database environment:
SQL> GRANT DBA TODBSALES3WITH ADMIN OPTION; SQL> GRANT RESOURCE TODBSALES3WITH ADMIN OPTION; SQL> GRANT CONNECT TODBSALES3WITH ADMIN OPTION;
SQL> CONNECT /ASSYSDBA
SQL> SHUTDOWN
SQL> STARTUP
The New Local Group dialog box appears:
ORA_sid_rolename [_D] [_A]
where:
For this example, ORA_orcl_dbsales3_D is entered.
The Add Users and Groups dialog box appears:
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 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 |
|
|
dbsales2 |
|
|
dbsales3 |
|
|
dbsales4 |
|
To perform authentication tasks on the client computer:
SQLNET.AUTHENTICATION_SERVICES parameter in the sqlnet.ora file contains nts.
C:\> SQLPLUS / NOLOG
SQL> SET INSTANCE connect_identifier
where connect_identifier is the net service name for the Oracle9i database that you created in Step 3.
SQL> CONNECT scott/tiger AS SYSDBA
You are connected to the Windows NT server over net service with the Oracle username scott/tiger. The roles applied to the Oracle username scott consist of all roles defined for the Windows NT username 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 username and the Oracle-specific Windows NT local groups to which the user belongs (for example, ORA_SID_DBSALES1 or ORA_SID_DBSALES4_DA).
|
Note:
OSDBA and OSOPER are the generic names for the two special operating system groups that control database administrator logins when using operating system authentication. On Windows NT, OSDBA and OSOPER are mapped to local groups in User Manager. The Windows NT-specific names for OSDBA and OSOPER are described in "Granting Administrator and Operator Privileges for Databases". See Oracle9i Database Administrator's Guide for more information on OSDBA and OSOPER. |
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|