1. Platform Guide
  2. Administering External Users and Roles on Windows
  3. Overview of Oracle Administration Assistant for Windows

Overview of Oracle Administration Assistant for Windows

Learn about the 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 needs to 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

Parent topic: Administering External Users and Roles on Windows

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 Oracle 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_HOMENAME

If a computer is not identified with a Domain Name System (DNS) domain name, you get 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 or change the primary DNS suffix, refer to your Microsoft documentation.

Parent topic: Overview of Oracle Administration Assistant for Windows

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:

  1. From the Start menu, select All Programs, then select Oracle - HOMENAME, then select Configuration and Migration Tools, and then select Administration Assistant for Windows.

    Microsoft Management Console starts.

  2. Double-click Oracle Managed Objects.

    The Computer icon appears.

  3. Right-click Computers.
  4. Select New and then select Computer.

    The Add Computer dialog appears.

    Description of addcomp.gif follows
    Description of the illustration addcomp.gif
  5. Specify the domain and computer name for the computer on which Oracle Database is installed.
  6. Click OK.
  7. Double-click Computers to display the computer you added.
  8. 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.

  9. Save your configuration in a console file by choosing File, then Save in the Console main menu. You can now authenticate database administrators and operators for all instances on the computer.

Parent topic: Overview of Oracle Administration Assistant for Windows

Granting Administrator Privileges for All Databases on a Computer

Use this procedure to grant 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 for the domain user to inherit membership privileges from another group. You must ensure that the user performing the installation is in the same domain, if not it results in an NTS authentication failure.

  1. From the Start menu, select All Programs, then select Oracle - HOMENAME, then select Configuration and Migration Tools, and then select Administration Assistant for Windows.

    Oracle Administration Assistant for Windows starts.

  2. Right-click OS Database Administrators - Computer.
  3. Click Add/Remove.

    The OS Database Administrators - Computer for host name dialog appears.

    Description of ntdba.gif follows
    Description of the illustration ntdba.gif
  4. In the NT Domain Users and Groups area, from the Domain list, select the domain of the user to whom you want to grant the SYSDBA system privilege.
  5. Select the user.
  6. Click Add.

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

  7. Click OK.

Parent topic: Overview of Oracle Administration Assistant for Windows

Granting Operator Privileges for All Databases on a Computer

Use this procedure to grant database operator (SYSOPER) privileges to the DBAs.

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

  1. From the Start menu, select All Programs, then select Oracle - HOMENAME, then, select Configuration and Migration Tools and then select Administration Assistant for Windows.

    Oracle Administration Assistant for Windows starts.

  2. Right-click OS Database Operators - Computer.
  3. Click Add/Remove.

    The OS Database Operators - Computer for host name dialog appears.

    Description of osdboper.gif follows
    Description of the illustration osdboper.gif
  4. In the NT Domain Users and Groups area, from the Domain list, select the domain of the user to whom you want to grant the SYSOPER system privilege.
  5. Select the user.
  6. Click Add.

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

  7. Click OK.

Parent topic: Overview of Oracle Administration Assistant for Windows

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.

See Also:

Oracle Database Security Guide for more information about SSL support

To connect to a database:

  1. 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:
  2. Choose Connect Database.

    If you connect to Oracle Database, the following Windows nodes appear under 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

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

Parent topic: Overview of Oracle Administration Assistant for Windows

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 cntdb4.gif follows
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 running. 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 cntdb2.gif follows
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 that Oracle Database is started.

  1. 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.
  2. 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.
  3. 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 the existing trace file text into a more readable format.

See Also:

Oracle Database Net Services Administrator's Guide for information about "Using the Trace Assistant to Examine Trace Files"

Parent topic: Connecting to a Database

Viewing Database Authentication Parameter Settings

Use this procedure to view database authentication parameter settings.

To view database authentication parameter settings:

  1. Right-click the database.
  2. Choose Properties.
  3. 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 user name.

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

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.

Parent topic: Overview of Oracle Administration Assistant for Windows

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:

  1. Follow the steps in "Connecting to a Database."
  2. Right-click External OS Users. A contextual menu appears.
  3. 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 mmc5.gif follows
    Description of the illustration mmc5.gif
  4. In NT Domain Users and Groups select the domain in which your Windows domain users and global groups are located.
  5. Select the Windows domain users and global groups to which you want to grant access to the database.
  6. Click Add. The selected users and groups now appear in the New External OS Users list.
  7. Click Next. The Profile and Tablespace dialog appears.
  8. 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.
  9. In Tablespace Quota double-click the tablespace to assign a tablespace quota.
  10. Click Next. The Roles dialog appears.
  11. In Available Roles select the database roles to grant to the new external users.
  12. Click Grant.
  13. Click Finish.
  14. 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.

Parent topic: Overview of 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:

  1. Follow the steps in "Connecting to a Database."
  2. Right-click Local Roles for the database for which you want to create a local role.
  3. 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 locrol1.gif follows
    Description of the illustration locrol1.gif
  4. Enter a local role name to use.
  5. 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 the user 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.

    Enter the password to use with this role.

    Confirm the password by entering it a second time.

  6. Click Next. The System Privileges dialog appears.
  7. In Available System Privileges select the system privileges you want to assign to the local role.
  8. 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.

  9. 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.
  10. Click Next. The Roles dialog appears.
  11. In Available Roles select the roles you want to assign to the local role. Both local roles and external roles appear in this list.
  12. 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.

  13. Click Finish.

See Also:

Oracle Database 2 Day DBA

Parent topic: Overview of Oracle Administration Assistant for Windows

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:

  1. Follow the steps in "Connecting to a Database" to connect to a database.
  2. Right-click External OS Roles create an external role.
  3. 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 exrol1.gif follows
    Description of the illustration exrol1.gif
  4. Enter an external role name to use. An external role is a role that is managed by the Windows operating system.
  5. Click Next.

    The System Privileges dialog appears.

    Description of exrol2.gif follows
    Description of the illustration exrol2.gif
  6. In Available System Privileges select the system privileges you want to assign to the external role.
  7. Choose Grant to grant the selected system privileges to the external role.
  8. 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.
  9. 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.
  10. Click Next.

    The Roles dialog appears.

    Description of exrol3.gif follows
    Description of the illustration exrol3.gif
  11. In Available Roles select the roles you want to assign to the external role. Both local roles and external roles appear in this list.
  12. 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.

  13. Click Finish.

Parent topic: Overview of Oracle Administration Assistant for Windows

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:

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

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

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

    The user now appears in OS Database Administrators.

  6. Click OK.

Parent topic: Overview of Oracle Administration Assistant for Windows

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:

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

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

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

    The user now appears in OS Database Operators.

  7. Click OK.

Parent topic: Overview of Oracle Administration Assistant for Windows