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\HOME
ID
, 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 /AS
SYSDBA
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\HOME
ID
.
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 /AS
SYSDBA
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 TODBSALES3
WITH ADMIN OPTION; SQL> GRANT RESOURCE TODBSALES3
WITH ADMIN OPTION; SQL> GRANT CONNECT TODBSALES3
WITH ADMIN OPTION;
SQL> CONNECT /AS
SYSDBA
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. |
|