11 Administering External Users and Roles on Windows

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

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

See Also:

See Oracle Database Enterprise User Security Administrator's Guide for more information about tools available for administering enterprise users and roles.

This chapter contains these topics:

Overview of 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
    

This section describes how to perform the following tasks with Oracle Administration Assistant for 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 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 or change the primary DNS suffix, refer to your Microsoft documentation.

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

Granting Administrator Privileges for All Databases on a Computer

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

Note:

If you use a domain account for database installation, then the domain user must be granted local administrative privileges. It is not sufficient 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, 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.

Granting Operator Privileges for All Databases on a Computer

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

  1. From the Start, 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.

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:

    Description of cntdb.gif follows
    Description of the illustration ''cntdb.gif''

  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. 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 also 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 existing trace file text into a more readable format. See "Using the Trace Assistant to Examine Trace Files" in Oracle Database Net Services Administrator's Guide.

Viewing Database Authentication Parameter Settings

To view database authentication parameter settings:

  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. See "Creating a Local Database Role" and "Creating an External Operating System Role" for more information.

Creating an External Operating System User

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

To create an external operating system user:

  1. Follow the steps in "Connecting to a Database" to connect to a database.

  2. Right-click External OS Users. A contextual menu appears.

    Description of mmc4.gif follows
    Description of the illustration ''mmc4.gif''

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

    Description of mmc6stp2.gif follows
    Description of the illustration ''mmc6stp2.gif''

  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.

    Description of step3.gif follows
    Description of the illustration ''step3.gif''

  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.

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" to connect 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. 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.

  6. Click Next. The System Privileges dialog appears.

    Description of locrol2.gif follows
    Description of the illustration ''locrol2.gif''

  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.

    Description of locrol3.gif follows
    Description of the illustration ''locrol3.gif''

  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.

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 for the database for which to 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.

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 to grant SYSDBA privileges from the Domain list.

  5. Select the user.

    The user now appears in OS Database Administrators.

  6. Click OK.

Granting Operator Privileges for a Single Database

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

  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.

Overview of Manually Administering External Users and Roles

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

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

  • External operating system users

  • Windows database administrators (with SYSDBA privilege)

  • Windows database operators (with SYSOPER privilege)

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

This section describes:

About Manually Creating an External Operating System User

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

Note that if a Windows Local User is used as the Oracle Home User for an Oracle home, then external user authentication of the Windows Local users is only supported from the same computer. Oracle recommends using Windows Domain User or Windows built-in user as the Oracle Home User to support external authentication of the Windows Domain User from the same computer or a different computer.

In the following procedure, two Windows user names are authenticated:

  • Local user jones

  • Domain user jones on domain sales

Local user jones logs into its local Windows client computer to access an Oracle Database server, which can be on a different computer. To access other databases and resources on other computers, the local user must provide a user name and password each time.

Domain user jones on domain sales logs into a sales domain that includes many other Windows computers and resources, one of which contains an Oracle Database server. The domain user can access all the resources the domain provides with a single user name and password.

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

Performing External User Authentication Tasks on the Oracle Database Server

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

  1. Add parameter OS_AUTHENT_PREFIX to your init.ora file.

    The OS_AUTHENT_PREFIX value is prefixed to local or domain user names attempting to connect to the server with the user's operating system name and password. The prefixed user name is compared with Oracle Database user names 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.

  2. Set a value for OS_AUTHENT_PREFIX. Your choices are:

    • Any character string

      If you specify xyz, as in this procedure's example, then xyz is prefixed to the beginning of the Windows user name (for example, xyzjones for local user jones or xyzsales\jones for domain user jones 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 user names (for example, jones for local user jones or sales\jones for domain user jones on domain sales).

    • No value specified

      If you do not specify a value for OS_AUTHENT_PREFIX, it defaults to OPS$ (for example, OPS$jones for local user jones or OPS$sales\jones for domain user jones on domain sales).

  3. Create a Windows local user name for jones with the Computer Management tool, or create a Windows domain user name for jones with Active Directory Users and Computers (if the appropriate name does not currently exist). See your operating system documentation for detailed instructions.

  4. Ensure that parameter SQLNET.AUTHENTICATN_SERVICES in file sqlnet.ora contains nts.

  5. Start SQL*Plus:

    C:\> sqlplus /NOLOG
    
  6. Connect to the database with the SYSTEM database administrator (DBA) name:

    SQL> CONNECT SYSTEM
    Enter password: system_password
    

    Unless you have changed it, the SYSTEM password is MANAGER by default.

  7. Create a local external user by entering:

    SQL> CREATE USER xyzjones IDENTIFIED EXTERNALLY;
    

    where xyz is the value you chose for initialization parameter OS_AUTHENT_PREFIX, and jones is the Windows local user name.

  8. Grant a local external user database roles by entering:

    SQL> GRANT DBA TO xyzjones;
    

    Note:

    External authentication of Windows Local users is supported from the same computer only. While external authentication of Windows Domain user is supported from the same computer or a different computer.
  9. Create a domain external user by entering:

    SQL> CREATE USER "XYZSALES\JONES" IDENTIFIED EXTERNALLY;
    

    where XYZ is the value you chose for initialization parameter OS_AUTHENT_PREFIX, and SALES\JONES is the domain name and Windows domain user name. Double quotes are required and the entire syntax must be in uppercase.

  10. Grant a domain external user database roles by entering:

    SQL> GRANT DBA TO "XYZSALES\JONES";
    

    Double quotes are required and the entire syntax must be in uppercase.

  11. Log on to the Windows system using the Windows local user jones or domain user SALES\JONES.

  12. Connect to the database with the SYSDBA name:

    SQL> CONNECT / AS SYSDBA
    
  13. Shut down the database:

    SQL> SHUTDOWN
    
  14. Restart the database:

    SQL> STARTUP
    

    This causes the change to parameter OS_AUTHENT_PREFIX to take effect.

Performing External User Authentication Tasks on the Client Computer

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

  1. Ensure that parameter SQLNET.AUTHENTICATN_SERVICES in file sqlnet.ora contains nts.

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

  3. Start SQL*Plus:

    C:\> sqlplus /NOLOG
    
  4. Connect to your Windows server:

    SQL> CONNECT /@connect_identifier
    

    where connect_identifier is the net service name for Oracle Database.

    Oracle Database searches the data dictionary for an automatic login user name corresponding to the Windows local or domain user name, verifies it, and enables connection as xyzjones or xyzsales\jones.

  5. Verify that you have connected to Oracle Database as domain user jones by viewing the roles assigned in steps 12 or 14 of "Performing External User Authentication Tasks on the Oracle Database Server".

    SQL> SELECT * FROM USER_ROLE_PRIVS;
    

    which outputs for local user jones:

    USERNAME                       GRANTED_ROLE                   ADM DEF OS_
    ------------------------------ ------------------------------ --- --- ---
    XYZJONES                       DBA                            NO  YES NO 
    1 row selected.
    

    or, for domain user jones:

    USERNAME                       GRANTED_ROLE                   ADM DEF OS_
    ------------------------------ ------------------------------ --- --- ---
    XYZSALES\JONES                 DBA                            NO  YES NO 
    1 row selected.
    

    Because the Oracle Database user name is the whole name xyzjones or xyzsales\jones, each object created by xyzjones or xyzsales\jones (that is, table, view, index, and so on) is prefixed by this name. For another user to reference the table shark owned by xyzjones, for example, the user must enter:

    SQL> SELECT * FROM xyzjones.shark 
    

    Note:

    Automatic authorization is supported for all Oracle Net protocols.

Overview of Manually Granting Administrator, Operator, and Task-Specific Privileges for Databases

This section describes how to enable Windows to grant the database administrator (SYSDBA), database operator (SYSOPER), database administrator for ASM (SYSASM), and new task-specific and less privileged than the ORA_DBA/SYSDBA system privileges to administrators. With these privileges, the administrator can issue the following commands from a client computer and connect to Oracle Database without entering a password:

SQL> CONNECT / AS SYSOPER
SQL> CONNECT / AS SYSDBA
SQL> CONNECT / AS SYSASM
SQL> CONNECT / AS SYSBACKUP
SQL> CONNECT / AS SYSDG
SQL> CONNECT / AS SYSKM

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

Table 11-1 Windows Local Groups with SYSDBA, SYSOPER, SYSASM, SYSDG, SYSBACKUP, and SYSKM Privileges

Local Group System Privileges

ORA_OPER

SYSOPER privileges for all databases on a computer

ORA_DBANote

SYSDBA privileges for all databases on a computer

ORA_SID_OPER

SYSOPER privileges for a single database (identified by SID)

ORA_SID_DBA

SYSDBA privileges for a single database (identified by SID)

ORA_HOMENAME_DBA

SYSDBA privileges for all database instances of the specified Oracle home.

ORA_HOMENAME_OPER

SYSOPER privileges for starting up and shutting down all databases instances that run from a specified Oracle home.

ORA_HOMENAME_SYSDG

SYSDG privilege for all database instances that run from the particular Oracle home

ORA_HOMENAME_SYSBACKUP

SYSBACKUP privilege for all database instances that run from the particular Oracle home

ORA_HOMENAME_SYSKM

SYSKM privilege for all database instances that run from the particular Oracle home

ORA_ASMADMIN

SYSASM privileges for all ASM instances on a computer

ORA_ASMDBA

SYSDBA privileges for all ASM instances on a computer

ORA_ASMOPER

SYSOPER privileges for all ASM instances on a computer


Note

All the groups mentioned in the table above are automatically created during installation and the Oracle Home User is automatically added to ORA_HOMENAME_DBA group. See section "Overview of Operating System Authentication Enabled at Installation" for information.

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

Running System Privilege Authentication Tasks on the Oracle Database Server

  1. Add your administrator user names to this group. The client logs in using one of these user names so that it is granted the required system privilege.

    See Also:

    Your operating system documentation for instructions on managing users and groups
  2. Ensure that parameter SQLNET.AUTHENTICATN_SERVICES in file sqlnet.ora contains nts.

Running System Privilege Authentication Tasks on the Client Computer

  1. Log in as a Windows domain user who is a member of one of the Windows local group on the server, according to the system privilege that you want Windows to grant. The administrator must add this domain user to the required Windows local group as mentioned in step 1 of Running System Privilege Authentication Tasks on the Oracle Database Server. Windows local group membership is created on the server system where Oracle Database runs.

  2. Ensure that parameter SQLNET.AUTHENTICATN_SERVICES in file sqlnet.ora contains nts.

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

  4. Start SQL*Plus:

    C:\> sqlplus /NOLOG
    
  5. Connect to Oracle Database:

    SQL> SET INSTANCE net_service_name
    

    where net_service_name is the Oracle Net net service name for Oracle Database.

  6. Enter either of the following SQL*Plus commands so that you connect to the database with the required system privilege:

    SQL> CONNECT / AS SYSOPER
    SQL> CONNECT / AS SYSDBA
    SQL> CONNECT / AS SYSASM
    SQL> CONNECT / AS SYSDG
    SQL> CONNECT / AS SYSKM
    SQL> CONNECT / AS SYSBACKUP
    

    You are now connected to the Windows server. If you connect with SYSDBA, you are given DBA privileges.

Managing New Users and User Groups

During Oracle Database installation, ORA_INSTALL, ORA_DBA, ORA_OPER, ORA_HOMENAME_DBA, ORA_HOMENAME_OPER, ORA_HOMENAME_SYSDG, ORA_HOMENAME_SYSBACKUP, ORA_HOMENAME_SYSKM, ORA_ASMADMIN, ORA_ASMDBA, and ORA_ASMOPER user groups are automatically created with the required privileges.

See Also:

Overview of Manually Creating an External Role

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

All privileges for these roles are active when the user connects. When using external roles, all roles are granted and managed through the operating system. You cannot use both external roles and Oracle Database roles at the same time.

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

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

Performing External Role Authorization Tasks on the Oracle Database Server

  1. Add initialization parameter OS_ROLES to the init.ora file.

  2. Set OS_ROLES to true.

    The default setting for this parameter is false.

  3. Ensure that parameter SQLNET.AUTHENTICATN_SERVICES in file sqlnet.ora contains nts.

  4. Start SQL*Plus:

    C:\> sqlplus /NOLOG
    
  5. Connect to your Windows server:

    SQL> CONNECT / AS SYSDBA
    
  6. 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;
    
  7. Grant to DBSALES3 whatever Oracle Database roles are appropriate to your database environment:

    SQL> GRANT DBA TO DBSALES3 WITH ADMIN OPTN;
    
  8. Connect to the database as SYSDBA:

    SQL> CONNECT / AS SYSDBA
    
  9. Shut down the database:

    SQL> SHUTDOWN
    
  10. Restart the database:

    SQL> STARTUP
    
  11. Create a Windows local group with the following syntax:

    ORA_sid_rolename[_D][_A] 
    

    For this command, note the following:

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

    Characters D and A are optional. If specified, they must be preceded by an underscore.

    For this example, ORA_orcl_dbsales3_D is created.

  12. Add one or more Windows local or domain user names to this group.

    See Also:

    Your operating system documentation for instructions on managing users and groups

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

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

    Note:

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

Performing External Role Authorization Tasks on the Client Computer

  1. Create a Windows local or domain user name with the same user name and password that exist on the Windows server (if the appropriate user name does not currently exist).

  2. Ensure that parameter SQLNET.AUTHENTICATN_SERVICES in file sqlnet.ora contains nts.

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

  4. Start SQL*Plus:

    C:\> sqlplus /NOLOG
    
  5. 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.

  6. Connect to Oracle Database:

    SQL> CONNECT SMITH
    Enter password: password
    

    You are connected to the Windows server over net service with Oracle Database user name smith. Roles applied to Oracle Database user name smith consist of all roles defined for the Windows user name that were previously mapped to the database roles (in this case, ORA_DBSALES3_D). All roles available under an authenticated connection are determined by the Windows user name and the Oracle-specific Windows local groups to which the user belongs (for example, ORA_SID_DBSALES1 or ORA_SID_DBSALES4_DA).

    Note:

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

About Manually Migrating Users

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

  • Centralized storage and management of user information

  • Centralized user authentication

  • Enhanced security

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

C:\ umu parameters

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

C:\ umu help=yes

See Also:

"Using the User Migration Utility" in Oracle Database Enterprise User Security Administrator's Guide