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

  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.

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

  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.

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

• Overview of Manually Creating an External Role

  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.

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

  Note:

  Use extreme care when manually configuring administrators, operators, users, and roles to be authenticated by the operating system. If possible, use Oracle Administration Assistant for Windows to perform configuration procedures.

• About Manually Creating an External Operating System User
  Describes how to authenticate external operating system users (not database administrators) using Windows, so that a password is not required when accessing the database.
• Overview of Manually Granting Administrator, Operator, and Task-Specific Privileges for Databases
  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.
• Managing New Users and User Groups
  Learn how to manage new users and user groups.
• Overview of Manually Creating an External Role
  Describes how to grant Oracle Database roles to users directly through Windows (known as external roles).
• About Manually Migrating Users
  You can migrate local or external users to enterprise users with User Migration Utility.

About Manually Creating an External Operating System User

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.

• Performing External User Authentication Tasks on the Oracle Database Server
  Use this procedure to perform external user authentication tasks.
• Performing External User Authentication Tasks on the Client Computer
  Use this procedure to perform external user authentication tasks on the client computer.

Performing External User Authentication Tasks on the Oracle Database Server

Use this procedure to perform external user authentication tasks.

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

Use this procedure to perform 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.
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.

   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.

   See Also:

   Oracle Database Net Services Administrator's Guide

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

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, a Windows local or a domain user name of the administrator must belong to one of the Windows local groups listed in Windows Local Groups with SYSDBA, SYSOPER, SYSASM, SYSDG, SYSBACKUP, and SYSKM Privileges.

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

See Also:

• Oracle Database Administrator's Guide

• Oracle Automatic Storage Management Administrator's Guide

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
  Learn about running system privilege authentication tasks on the Oracle Database server.
• Running System Privilege Authentication Tasks on the Client Computer
  Learn about running system privilege authentication tasks on the client computer.

Running System Privilege Authentication Tasks on the Oracle Database Server

Learn about running system privilege authentication tasks on the Oracle Database server.

Perform the following steps:

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

Learn about running system privilege authentication tasks on the client computer.

Perform the following steps:

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. Windows local group membership is created on the server system where Oracle Database runs.
2. Ensure that the 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.
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.

   See Also:

   Oracle Database Net Services Administrator's Guide

Managing New Users and User Groups

Learn how to manage 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:

• Oracle Database Installation Guide for Microsoft Windows

• Oracle Grid Infrastructure Installation Guide

Overview of Manually Creating an External Role

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
  Learn how to perform external role authorization tasks on the Oracle Database server.
• Performing External Role Authorization Tasks on the Client Computer
  Learn how to perform external role authorization tasks on the client computer.

Performing External Role Authorization Tasks on the Oracle Database Server

Learn how to perform external role authorization tasks on the Oracle Database server.

Perform the following steps:

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.

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

See Also:

Your operating system documentation for instructions on managing users and groups

Performing External Role Authorization Tasks on the Client Computer

Learn how to perform external role authorization tasks on the client computer.

Perform the following steps:

1. Create a Windows local or a 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.
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 the generic names for the two special operating system groups that control database administrator logins when using operating system authentication.

   See Also:

   • Oracle Database Administrator's Guide

   • Oracle Database Net Services Administrator's Guide

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:

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