Overview of Manually Administering External Users and Roles

Use this procedure to perform external user authentication tasks.

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.

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

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

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

Learn how to perform 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.

    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.

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

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