3 Configuring Authentication

3.2.2.1 About Managing Passwords

Database security systems that depend on passwords require that passwords be kept secret at all times. Because passwords are vulnerable to theft, forgery, and misuse, Oracle Database uses a password management policy. Database administrators and security officers control this policy through user profiles, enabling greater control of database security.

Use the CREATE PROFILE statement to create a user profile. The profile is assigned to a user with the CREATE USER or ALTER USER statement. Details of creating and altering database users are not discussed in this section. This section is concerned with the password parameters that can be specified using the CREATE PROFILE (or ALTER PROFILE) statement.

3.2.2.2 Finding User Accounts That Have Default Passwords

When you create a database in Oracle Database 11g Release 1 (11.1), its default accounts are locked with the passwords expired. If you have upgraded from an earlier release of Oracle Database, you may have user accounts that have default passwords. These are default accounts that are created when you create a database, such as the HR, OE, and SCOTT accounts.

For greater security, change the passwords for these accounts. Using a default password that is commonly known can make your database vulnerable to attacks by intruders. To find both locked and unlocked accounts that use default passwords, log onto SQL*Plus using the SYSDBA privilege and then query the DBA_USERS_WITH_DEFPWD data dictionary view.

For example:

CONNECT / AS SYSDBA
Enter password: password
Connected.

SELECT * FROM DBA_USERS_WITH_DEFPWD;

USERNAME
---------
SCOTT

Then change the passwords for any accounts that the DBA_USERS_WITH_DEFPWD view lists:

ALTER USER scott IDENTIFIED BY password;

Replace password with a password that is secure. "How Oracle Database Checks the Complexity of Passwords" describes the minimum requirements for passwords.

3.2.2.3 Account Locking

When a user exceeds a designated number of failed login attempts, the server automatically locks that user account. You can specify the permissible number of failed login attempts by using the CREATE PROFILE statement. You can also specify the amount of time accounts remain locked.

Example 3-1 sets the maximum number of failed login attempts for the user johndoe to 10 (the default), and the amount of time the account locked to 30 days. The account will unlock automatically after 30 days.

CREATE PROFILE prof LIMIT
 FAILED_LOGIN_ATTEMPTS 10
 PASSWORD_LOCK_TIME 30;
ALTER USER johndoe PROFILE prof;

Each time the user unsuccessfully logs in, Oracle Database increases the delay exponentially with each login failure.

If you do not specify a time interval for unlocking the account, then PASSWORD_LOCK_TIME assumes the value specified in a default profile. (The recommended value is 1 day.) If you specify PASSWORD_LOCK_TIME as UNLIMITED, then you must explicitly unlock the account by using an ALTER USER statement. For example, assuming that PASSWORD_LOCK_TIME UNLIMITED is specified for johndoe, then you use the following statement to unlock the johndoe account:

ALTER USER johndoe ACCOUNT UNLOCK;

After a user successfully logs into an account, Oracle Database resets the unsuccessful login attempt count for the user, if it exists, to 0.

The security officer can also explicitly lock user accounts. When this occurs, the account cannot be unlocked automatically, and only the security officer should unlock the account. The CREATE USER or ALTER USER statements explicitly lock or unlock user accounts. For example, the following statement locks the user account, susan:

ALTER USER susan ACCOUNT LOCK;

3.2.2.4 Password Aging and Expiration

You can specify a password lifetime, after which the password expires and must be changed before logging into the account is permitted again. In addition, you can set a grace period, during which each attempt to log in to the database account receives a warning message to change the password. If the user does not change it by the end of that period, then Oracle Database expires the account. No further logins to that account are allowed without assistance by the database administrator.

You can also manually set the password state to expired, which sets the user account status to expired. The user or the database administrator must then change the password, using either the PASSWORD or ALTER USER statement, before the user can log in to the database.

Use the CREATE PROFILE or ALTER PROFILE statement to specify a maximum lifetime for passwords. When the specified amount of time passes and the password expires, the user or DBA must change the password.

Example 3-2 demonstrates how to create and assign a profile to user johndoe, and the PASSWORD_LIFE_TIME clause specifies that johndoe can use the same password for 180 days before it expires.

CREATE PROFILE prof LIMIT
 FAILED_LOGIN_ATTEMPTS 4
 PASSWORD_LOCK_TIME 30
 PASSWORD_LIFE_TIME 180;
ALTER USER johndoe PROFILE prof;

You can also specify a grace period for password expiration. Users enter the grace period upon the first attempt to log in to a database account after their password has expired. During the grace period, a warning message appears each time users try to log in to their accounts, and continues to appear until the grace period expires. Users must change the password within the grace period. If they do not change the password within the grace period, then they are prompted for a new password each time they try to access their accounts. Access to an account is denied until a new password is supplied.

Figure 3-1 shows the chronology of the password lifetime and grace period.

Figure 3-1 Chronology of Password Lifetime and Grace Period

Description of "Figure 3-1 Chronology of Password Lifetime and Grace Period"

In the following example, the profile assigned to johndoe includes the specification of a grace period: PASSWORD_GRACE_TIME = 3 (the recommended value). The first time johndoe tries to log in to the database after 90 days (this can be any day after the 90th day, that is, the 91st day, 100th day, or another day), he receives a warning message that his password will expire in 3 days. If 3 days pass, and if he does not change his password, then the password expires. After this, he receives a prompt to change his password on any attempt to log in, and cannot log in until he does so.

CREATE PROFILE prof LIMIT
 FAILED_LOGIN_ATTEMPTS 4
 PASSWORD_LOCK_TIME 30
 PASSWORD_LIFE_TIME 90
 PASSWORD_GRACE_TIME 3;
ALTER USER johndoe PROFILE prof;

You can explicitly expire a password by using the CREATE USER and ALTER USER statements. The following statement creates a user with an expired password. This setting forces the user to change the password before the user can log in to the database.

CREATE USER jbrown 
 IDENTIFIED BY z8990sghj
 ...
 PASSWORD EXPIRE;

3.2.2.5 Controlling User Ability to Reuse Old Passwords

You can ensure that users do not reuse their old passwords for a specified amount of time or for a specified number of password changes. To do so, configure the rules for password reuse with CREATE PROFILE or ALTER PROFILE statements. For the complete syntax of these statements, see the Oracle Database SQL Language Reference.

Table 3-1 lists the CREATE PROFILE and ALTER PROFILE parameters that control ability of a user to reuse an old password.

If you do not specify a parameter, then the user can reuse passwords at any time, which is not a good security practice.

If neither parameter is UNLIMITED, then password reuse is allowed, but only after meeting both conditions. The user must have changed the password the specified number of times, and the specified number of days must have passed since the old password was last used.

For example, suppose that the profile of user A had PASSWORD_REUSE_MAX set to 10 and PASSWORD_REUSE_TIME set to 30. User A cannot reuse a password until he or she has reset the password 10 times, and until 30 days had passed since the password was last used.

If either parameter is specified as UNLIMITED, then the user can never reuse a password.

If you set both parameters to UNLIMITED, then Oracle Database ignores both, and the user can reuse any password at any time.

3.2.2.6 Enforcing Password Complexity Verification

Password complexity refers to the creation of strong, secure passwords for database user accounts. You need to ensure that the passwords for your users are complex enough to provide reasonable protection against intruders who try to break into the system by guessing passwords.

How Oracle Database Checks the Complexity of Passwords

Oracle Database provides a sample password verification function in the PL/SQL script UTLPWDMG.SQL (located in ORACLE_BASE/ORACLE_HOME/RDBMS/ADMIN) that, when enabled, checks whether users are correctly creating or modifying their passwords. The UTLPWDMG.SQL script provides two password verification functions: one for previous releases of Oracle Database (which is commented out) and an updated version for Oracle Database Release 11g.

The UTLPWDMG.SQL script checks for the following when users create or modify passwords:

• The password contains no fewer than eight characters.

• The password is not the same as the user name, nor is it the user name spelled backward or with numeric characters appended.

• The password is not the same as the server name or the server name with the numbers 1–100 appended.

• The password is not too simple, for example, welcome1, database1, account1, user1234, password1, oracle, oracle123, computer1, abcdefg1, or change_on_install.

• The password includes at least 1 numeric and 1 alphabetic character.

• The password differs from the previous password by at least 3 letters.

Customizing Password Complexity Verification

You can create your own password complexity verification function by backing up and customizing the PASSWORD_VERIFY function in the UTLPWDMG.SQL script. In fact, Oracle recommends that you do so to further secure your site's passwords. See also Guideline 1 in "Guidelines for Securing Passwords" for general advice on creating passwords.

By default, password complexity verification is not enabled. To enable the password complexity verification:

1. Log in to SQL*Plus with administrative privileges and then run the UTLPWDMG.SQL script (or your modified version of this script) to create the password complexity function in the SYS schema.

   CONNECT SYS/AS SYSDBA
   Enter password: password
   Connected.
   
   @$ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql
   

2. In the default profile or the user profile, set the PASSWORD_VERIFY_FUNCTION setting to either the sample password complexity function in the UTLPWDMG.SQL script, or to your customized function. Use one of the following methods:

   • Log in to SQL*Plus with administrator privileges and use the CREATE PROFILE or ALTER PROFILE statement to enable the function. For example, to update the default profile to use the verify_function_11G function:

     ALTER PROFILE default 
      PASSWORD_VERIFY_FUNCTION verify_function_11G;
     

   • In Oracle Enterprise Manager, go to the Edit Profiles page and then under Complexity, select the name of the password complexity function from the Complexity function list.

After you have enabled password complexity verification, it takes effect immediately.

3.2.2.7 Enabling or Disabling Password Case Sensitivity

When you create or modify user accounts, by default, passwords are case sensitive. To control the use of case sensitivity in passwords, set the SEC_CASE_SENSITIVE_LOGON initialization parameter. Only users who have the ALTER SYSTEM privilege can set the SEC_CASE_SENSITIVE_LOGIN parameter. Set it to TRUE to enable case sensitivity or FALSE to disable case sensitivity.

For greater security, Oracle recommends that you enable case sensitivity in passwords. However, if you have compatibility issues with your applications, you can use this parameter to disable password case sensitivity. Examples of application compatibility issues are passwords for your applications being hard-coded to be case insensitive, or different application modules being inconsistent about case sensitivity when sending credentials to start a database session.

Example 3-3 shows how to enable case sensitivity in passwords.

CONNECT SYS/AS SYSDBA
Enter password: password
Connected.

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE

In previous releases of Oracle Database, passwords were not case sensitive. If you import user accounts from a previous release, for example, Release 10g, into the current database release, the case-insensitive passwords in these accounts remain case insensitive until the user changes his or her password. If the account was granted SYSDBA or SYSOPER privileges, it is imported to the password file. (See "How Case Sensitivity Affects Password Files" for more information.) When a password from a user account from the previous release is changed, it then becomes case sensitive.

You can find users who have case sensitive or case insensitive passwords by querying the DBA_USERS view. The PASSWORD_VERSIONS column in this view indicates the release in which the password was created. For example:

SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

USERNAME                       PASSWORD_VERSIONS
------------------------------ -----------------
JONES                          10G 11G
ADAMS                          10G 11G
CLARK                          10G 11G
PRESTON                        11G
BLAKE                          10G

The passwords for accounts jones, adams, and clark were originally created in Release 10g and then reset in Release 11g. Their passwords, assuming case sensitivity has been enabled, are now case sensitive, as is the password for preston. However, the account for blake is still using the Release 10g standard, so it is case insensitive. Ask him to reset his password so that it will be case sensitive, and therefore more secure.

See Oracle Database Reference for more information about the DBA_USERS view.

How Case Sensitivity Affects Password Files

You can enable or disable case sensitivity for password files by using the ignorecase argument in the ORAPWD command line utility. The default value for ignorecase is n (no), which enforces case sensitivity.

Example 3-4 shows how to enable case sensitivity in password files.

orapwd file=orapw password=manag23?er entries=100 ignorecase=n

This creates a password file called orapw, whose password for SYS is manag23?er and will be case sensitive. Afterwards, this connection succeeds:

CONNECT SYS/AS SYSDBA
Enter password: manag23?er

But the following connection, which uses incorrect case for the password, fails:

CONNECT SYS/AS SYSDBA
Enter password: Manag23?er

If you set ignorecase to y, the passwords in the password file are case insensitive. In other words, entering the password manag23?er as manag23?er, Manag23?er, or MANAG23?ER succeeds.

If you imported user accounts from a previous release and these accounts were created with SYSDBA or SYSOPER privileges, they will be included in the password file. The passwords for these accounts are case insensitive. The next time these users change their passwords, and assuming case sensitivity is enabled, the passwords become case sensitive. For greater security, have these users change their passwords.

See Oracle Database Administrator's Guide for more information about password files.

How Case Sensitivity Affects Accounts Created for Database Link Connections

When you create a database link connection, you need to define a user name and password for the connection. When you create the database link connection, the password is case sensitive. How this user enters his or her password for connections depends on the release in which the database link was created:

• Before a user can connect from a pre-Release 11g database to a Release 11g database, and assuming that case sensitivity is enabled, you must re-create the password for this database link using all uppercase letters, for example, SEBASTIAN2GO.

  The reason you need to re-create the password using all uppercase letters is so that it will match how Oracle Database stores database link passwords. Oracle Database always stores this type of password in uppercase letters, even if the password had originally been created using lower or mixed case letters (for example, sebastian2go or Sebastian2Go). If case sensitivity is disabled, the user can enter the password using the case the password was created in, for example, Sebastian2Go if it was created as Sebastian2Go.

• If the user is connecting from a Release 11g database to another Release 11g database, he or she must enter the password using the case in which it was created, assuming that case sensitivity is enabled.

• If the user connecting from a Release 11g database to a pre-Release 11g database, he or she can enter his or her password using any case, because the password is still case insensitive.

In other words, any time a user connects to a Release 11g database from a database link, he or she needs to enter the password in its exact case.

You can find the user accounts for existing database links by running the V$DBLINK view. For example:

SELECT DB_LINK, OWNER_ID FROM V$DBLINK;

See Oracle Database Reference for more information about the V$DBLINK view.

3.2.4.1 About the Secure External Password Store

You can store password credentials for connecting to databases by using a client-side Oracle wallet. An Oracle wallet is a secure software container that stores authentication and signing credentials.

This wallet usage can simplify large-scale deployments that rely on password credentials for connecting to databases. When this feature is configured, application code, batch jobs, and scripts no longer need embedded user names and passwords. This reduces risk because the passwords are no longer exposed, and password management policies are more easily enforced without changing application code whenever user names or passwords change.

3.2.4.2 How Does the External Password Store Work?

Typically, users (and as applications, batch jobs, and scripts) connect to databases by using a standard CONNECT statement that specifies a database connection string. This string can include a user name and password, and an Oracle Net service name identifying the database on an Oracle Database network. For example, the service name could be the URL that identifies that database, or a TNS alias you entered in the tnsnames.ora file in the database. Another possibility is a host:port:sid string.

The following examples are standard CONNECT statements that could be used for a client that is not configured to use the external password store:

CONNECT SALESAPP/2Ip6Cg8@sales_db.us.acme.com

CONNECT SALESAPP/2Ip6Cg8@ORASALES

CONNECT SALESAPP/2Ip6Cg8@ourhost37:1527:DB17

In these examples, salesapp is the user name and 2Ip6Cg8 is the password, with the unique connect string for the database shown as specified in three different ways. You could use its URL sales_db.us.acme.com, or its TNS alias ORASALES from the tnsnames.ora file, or its host:port:sid string.

However, when clients are configured to use the secure external password store, applications can connect to a database with the following CONNECT statement syntax, without specifying database login credentials:

CONNECT /@db_connect_string

In this specification, db_connect_string is a valid connection string to access the intended database, such as the service name, URL, or alias as shown in the earlier examples.

In this case, the database credentials, user name and password, are securely stored in an Oracle wallet created for this purpose. The autologin feature of this wallet is turned on, so the system does not need a password to open the wallet. From the wallet, it gets the credentials to access the database for the user they represent.

3.2.4.3 Configuring Clients to Use the External Password Store

If your client is already configured to use external authentication, such as Windows native authentication or Secure Sockets Layer (SSL), then Oracle Database uses that authentication method. The same credentials used for such authentication are typically also used to log in to the database.

For clients not using such authentication methods or wanting to override them for database authentication, you can set the SQLNET.WALLET_OVERRIDE parameter in sqlnet.ora to TRUE. The default value for SQLNET.WALLET_OVERRIDE is FALSE, allowing standard use of authentication credentials as before.

If you want a client to use the secure external password store feature, then perform the following configuration tasks.

To enable clients to use the external password store:

1. Create a wallet on the client by using the following syntax at the command line:

   mkstore -wrl wallet_location -create
   

   For example:

   mkstore -wrl c:\oracle\product\11.1.0\db_1\wallets -create
   

   wallet_location is the path to the directory where you want to create and store the wallet. This command creates an Oracle wallet with the autologin feature enabled at the location you specify. The autologin feature enables the client to access the wallet contents without supplying a password. See Oracle Database Advanced Security Administrator's Guide for information about autologin wallets.

2. Create database connection credentials in the wallet by using the following syntax at the command line:

   For example:

   mkstore -wrl c:\oracle\product\11.1.0\db_1\wallets -createCredential orcl system
   Enter passwword: password
   

   In this specification:

   • wallet_location is the path to the directory where you created the wallet in Step 1.

   • db_connect_string is the TNS alias you use to specify the database in the tnsnames.ora file or any service name you use to identify the database on an Oracle network. By default, tnsnames.ora is located in the $ORACLE_HOME/network/admin directory on UNIX systems and in ORACLE_HOME\network\admin on Windows.

   • username is the database login credential. When prompted, enter the password for this user.

   Repeat this step for each database you want accessible using the CONNECT /@db_connect_string syntax.

   Note:

   The db_connect_string used in the CONNECT /@db_connect_string statement must be identical to the db_connect_string specified in the -createCredential command.

3. In the client sqlnet.ora file, enter the WALLET_LOCATION parameter and set it to the directory location of the wallet you created in Step 1.

   For example, if you created the wallet in $ORACLE_HOME/network/admin and your Oracle home is set to /private/ora11, then you need to enter the following into your client sqlnet.ora file:

   WALLET_LOCATION =
     (SOURCE =
       (METHOD = FILE)
       (METHOD_DATA =
     (DIRECTORY = /private/ora11/network/admin)
     )
    )
   

4. In the client sqlnet.ora file, enter the SQLNET.WALLET_OVERRIDE parameter and set it to TRUE as follows:

   SQLNET.WALLET_OVERRIDE = TRUE
   

   This setting causes all CONNECT /@db_connect_string statements to use the information in the wallet at the specified location to authenticate to databases.

   When external authentication is in use, an authenticated user with such a wallet can use the CONNECT /@db_connect_string syntax to access the previously specified databases without providing a user name and password. However, if a user fails that external authentication, then these connect statements also fail.

   Note:

   If an application uses SSL for encryption, then the sqlnet.ora parameter, SQLNET.AUTHENTICATION_SERVICES, specifies SSL and an SSL wallet is created. If this application wants to use secret store credentials to authenticate to databases (instead of the SSL certificate), then those credentials must be stored in the SSL wallet. After SSL authentication, if SQLNET.WALLET_OVERRIDE = TRUE, then the user names and passwords from the wallet are used to authenticate to databases. If SQLNET.WALLET_OVERRIDE = FALSE, then the SSL certificate is used.

Example 3-5 shows a sample sqlnet.ora file with the WALLET_LOCATION and the SQLNET.WALLET_OVERRIDE parameters set as described in Steps 3 and 4.

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /private/ora11/network/admin)
     )
    )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSESSL_VERSION = 0

3.2.4.4 Managing External Password Store Credentials

This section summarizes the following tasks you can perform to manage credentials in the external password store by using the mkstore command-line utility:

• Listing External Password Store Contents

• Adding Credentials to an External Password Store

• Modifying Credentials in an External Password Store

• Deleting Credentials from an External Password Store

mkstore -wrl wallet_location -listCredential

mkstore -wrl c:\oracle\product\11.1.0\db_1\wallets -listCredential

mkstore -wrl wallet_location -createCredential db_alias username

mkstore -wrl c:\oracle\product\11.1.0\db_1\wallets -createCredential orcl system
Enter password: password

mkstore -wrl wallet_location -modifyCredential dbase_alias username

mkstore -wrl c:\oracle\product\11.1.0\db_1\wallets -modifyCredential sales_db
Enter password: password

mkstore -wrl wallet_location -deleteCredential db_alias

mkstore -wrl c:\oracle\product\11.1.0\db_1\wallets -deleteCredential orcl

3.3.1.1 Configuring Directory Authentication for Administrative Users

To configure directory authentication for administrative users:

1. Configure the administrative user by using the same procedures you would use to configure a typical user.

2. In Oracle Internet Directory, grant the SYSDBA or SYSOPER privilege to the user for the database that this user will administer.

   Grant SYSDBA or SYSOPER only to trusted users. See "Guidelines for Securing User Accounts and Privileges" for advice on this topic.

3. Set the LDAP_DIRECTORY_SYSAUTH initialization parameter to YES:

   ALTER SYSTEM SET LDAP_DIRECTORY_SYSAUTH = YES;
   

   When set to YES, the LDAP_DIRECTORY_SYSAUTH parameter enables SYSDBA and SYSOPER users to authenticate to the database by using a strong authentication method.

   See Oracle Database Reference for more information about LDAP_DIRECTORY_SYSAUTH.

4. Set the LDAP_DIRECTORY_ACCESS parameter to either PASSWORD or SSL. For example:

   ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS = PASSWORD;
   

   Ensure that the LDAP_DIRECTORY_ACCESS initialization parameter is not set to NONE. Setting this parameter to PASSWORD or SSL ensures that users can be authenticated using SYSDBA or SYSOPER through Oracle Internet Directory. See Oracle Database Reference for more information about LDAP_DIRECTORY_ACCESS.

Afterward, this user can log in by including the net service name in the CONNECT statement in SQL*Plus. For example, to log on as SYSDBA if the net service name is orcl:

CONNECT user/password@orcl AS SYSDBA

If the database is configured to use a password file for remote authentication, Oracle Database checks the password file first.

3.3.1.2 Configuring Kerberos Authentication for Administrative Users

To configure Kerberos authentication for administrative users:

1. Configure the administrative user by using the same procedures you would use to configure a typical user.

   See Oracle Database Advanced Security Administrator's Guide for more information.

2. Configure Oracle Internet Directory for Kerberos authentication.

   See Oracle Database Enterprise User Security Administrator's Guide for more information.

3. In Oracle Internet Directory, grant the SYSDBA or SYSOPER privilege to the user for the database that this user will administer.

   Grant SYSDBA or SYSOPER only to trusted users. See "Guidelines for Securing User Accounts and Privileges" for advice on this topic.

4. Set the LDAP_DIRECTORY_SYSAUTH initialization parameter to YES:

   ALTER SYSTEM SET LDAP_DIRECTORY_SYSAUTH = YES;
   

   When set to YES, the LDAP_DIRECTORY_SYSAUTH parameter enables SYSDBA and SYSOPER users to authenticate to the database by using strong authentication methods. See Oracle Database Reference for more information about LDAP_DIRECTORY_SYSAUTH.

5. Set the LDAP_DIRECTORY_ACCESS parameter to either PASSWORD or SSL. For example:

   ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS = SSL;
   

   Ensure that the LDAP_DIRECTORY_ACCESS initialization parameter is not set to NONE. Setting this parameter to PASSWORD or SSL ensures that users can be authenticated using SYSDBA or SYSOPER through Oracle Internet Directory. See Oracle Database Reference for more information about LDAP_DIRECTORY_ACCESS.

Afterward, this user can log in by including the net service name in the CONNECT statement in SQL*Plus. For example, to log on as SYSDBA if the net service name is orcl:

CONNECT /@orcl AS SYSDBA

3.3.1.3 Configuring Secure Sockets Layer Authentication for Administrative Users

To configure Secure Sockets Layer (SSL) authentication for administrative users:

1. Configure the client to use SSL:

   1. Configure the client wallet and user certificate. Update the wallet location in the sqlnet.ora configuration file.

      You can use Wallet Manager to configure the client wallet and user certificate. See Oracle Database Advanced Security Administrator's Guide for more information.

   2. Configure the Oracle net service name to include server DNs and use TCP/IP with SSL in tnsnames.ora.

   3. Configure TCP/IP with SSL in listener.ora.

   4. Set the client SSL cipher suites and the required SSL version, and then set SSL as an authentication service in sqlnet.ora.

2. Configure the server to use SSL:

   1. Enable SSL for your database listener on TCPS and provide a corresponding TNS name. You can use Net Configuration Assistant to configure the TNS name.

   2. Store the database PKI credentials in the database wallet. You can use Wallet Manager do this.

   3. Set the LDAP_DIRECTORY_ACCESS initialization parameter to SSL:

      ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS = SSL;
      

      See Oracle Database Reference for more information about LDAP_DIRECTORY_ACCESS.

3. Configure Oracle Internet Directory for SSL user authentications.

   See Oracle Database Enterprise User Security Administrator's Guide for information on configuring enterprise user security SSL authentication.

4. In Oracle Internet Directory, grant the SYSDBA or SYSOPER privilege to the user for the database that the user will administer.

5. On the server computer, set the LDAP_DIRECTORY_SYSAUTH initialization parameter to YES.

   ALTER SYSTEM SET LDAP_DIRECTORY_SYSAUTH = YES;
   

   When set to YES, the LDAP_DIRECTORY_SYSAUTH parameter enables SYSDBA and SYSOPER users to authenticate to the database by using a strong authentication method. See Oracle Database Reference for more information about LDAP_DIRECTORY_SYSAUTH.

Afterward, this user can log in by including the net service name in the CONNECT statement in SQL*Plus. For example, to log on as SYSDBA if the net service name is orcl:

CONNECT /@orcl AS SYSDBA

3.7.1.1 Creating a Global User Who Has a Private Schema

The following statement shows the creation of a global user with a private schema, authenticated by SSL, and authorized by the enterprise directory service:

CREATE USER scott IDENTIFIED GLOBALLY AS 'CN=scott,OU=division1,O=oracle,C=US';

The string provided in the AS clause provides an identifier (distinguished name, or DN) meaningful to the enterprise directory.

In this case, scott is a global user. But, the disadvantage here is that user scott must then be created in every database that he must access, plus the directory.

3.7.1.2 Creating Multiple Enterprise Users Who Share Schemas

Multiple enterprise users can share a single schema in the database. These users are authorized by the enterprise directory service but do not own individual private schemas in the database. These users are not individually created in the database. They connect to a shared schema in the database.

To create a schema-independent user:

1. Create a shared schema in the database as follows.

   CREATE USER appschema IDENTIFIED GLOBALLY AS '';
   

2. In the directory, create multiple enterprise users and a mapping object.

   The mapping object tells the database how you want to map the DNs for the users to the shared schema. You can either create a full DN mapping (one directory entry for each unique DN), or you can map, for each user, multiple DN components to one schema. For example:

   OU=division,O=Oracle,C=US 
   

   See Also:

   Oracle Database Enterprise User Security Administrator's Guide for an explanation of these mappings

Most users do not need their own schemas, and implementing schema-independent users separates users from databases. You create multiple users who share the same schema in a database, and as enterprise users, they can also access shared schemas in other databases.

3.10.1.1 About Proxy Authentication

You can design a middle-tier server to authenticate clients in a secure fashion by using the following three forms of proxy authentication:

• The middle-tier server authenticates itself with the database server and a client, in this case an application user or another application, authenticates itself with the middle-tier server. Client identities can be maintained all the way through to the database.

• The client, in this case a database user, is not authenticated by the middle-tier server. The clients identity and database password are passed through the middle-tier server to the database server for authentication.

• The client, in this case a global user, is authenticated by the middle-tier server, and passes one of the following through the middle tier for retrieving the client's user name.

  • Distinguished name (DN)

  • Certificate

In all cases, an administrator must authorize the middle-tier server to act on behalf of the client.

3.10.1.2 Advantages of Proxy Authentication

In multitier environments, proxy authentication controls the security of middle-tier applications by preserving client identities and privileges through all tiers and by auditing actions taken on behalf of clients. For example, this feature allows the identity of a user using a Web application (which acts as a proxy) to be passed through the application to the database server.

Three-tier systems provide the following benefits to organizations:

• Organizations can separate application logic from data storage, partitioning the former in application servers and the latter in databases.

• Application servers and Web servers enable users to access data stored in databases.

• Users like using a familiar, easy-to-use browser interface.

• Organizations can also lower their cost of computing by replacing many thick clients with a number of thin clients and an application server.

In addition, Oracle Database proxy authentication provides the following security benefits:

• A limited trust model, by controlling the users on whose behalf middle tiers can connect and the roles that the middle tiers can assume for the user

• Scalability, by supporting user sessions through OCI, thick JDBC, or thin JDBC, and eliminating the overhead of reauthenticating clients

• Accountability, by preserving the identity of the real user through to the database, and enabling auditing of actions taken on behalf of the real user

• Flexibility, by supporting environments in which users are known to the database, and in which users are merely application users of which the database has no awareness

  Note:

  Oracle Database supports this proxy authentication functionality in three tiers only. It does not support it across multiple middle tiers.

3.10.1.3 Altering a User Account to Connect Through a Proxy

To authorize a user account to connect using a proxy account, use the GRANT CONNECT THROUGH clause of the ALTER USER statement.

Example 3-6 shows how to alter user preston to connect through the proxy user jward.

ALTER USER preston GRANT CONNECT THROUGH jward;

Afterward, user jward can connect using the preston proxy user as follows:

CONNECT jward[preston]
Enter password: password

Note the following:

• Using roles with middle-tier clients. You can also specify roles that the middle tier is permitted to activate when connecting as the client. Operations performed on behalf of a client by a middle-tier server can be audited.

• Finding proxy users. To find the users who are currently authorized to connect through a middle tier, query the PROXY_USERS data dictionary view, for example:

  SELECT * FROM PROXY_USERS;
  

• Removing proxy connections. Use the REVOKE CONNECT THROUGH clause of ALTER USER to disallow a proxy connection. For example, to revoke user preston from connecting through the proxy user jward, enter the following statement:

  ALTER USER preston REVOKE CONNECT THROUGH jward
  

• Password expiration and proxy connections. Middle-tier use of password expiration does not apply to accounts that are authenticated through a proxy. Instead, lock the account rather than expire the password.

3.10.1.4 Passing Through the Identity of the Real User by Using Proxy Authentication

For enterprise users or database users, Oracle Call Interface, thick JDBC, or thin JDBC enables a middle tier to set up a number of user sessions within a single database connection, each of which uniquely identifies a connected user (connection pooling). These sessions reduce the network overhead of creating separate network connections from the middle tier to the database.

3.10.1.5 Limiting the Privilege of the Middle Tier

Least privilege is the principle that users should have the fewest privileges necessary to perform their duties and no more. As applied to middle tier applications, this means that the middle tier should not have more privileges than it needs. Oracle Database enables you to limit the middle tier such that it can connect only on behalf of certain database users, using only specific database roles. You can limit the privilege of the middle tier to connect on behalf of an enterprise user, stored in an LDAP directory, by granting to the middle tier the privilege to connect as the mapped database user. For instance, if the enterprise user is mapped to the APPUSER schema, then you must at least grant to the middle tier the ability to connect on behalf of APPUSER. Otherwise, attempts to create a session for the enterprise user will fail.

However, you cannot limit the ability of the middle tier to connect on behalf of enterprise users. For example, suppose that user Sarah wants to connect to the database through a middle tier, appsrv (which is also a database user). Sarah has multiple roles, but it is desirable to restrict the middle tier to use only the clerk role on her behalf.

An administrator could effectively grant permission for appsrv to initiate connections on behalf of Sarah using her clerk role only, using the following syntax:

ALTER USER sarah GRANT CONNECT THROUGH appsrv WITH ROLE clerk;

By default, the middle tier cannot create connections for any client. The permission must be granted for each user.

To allow appsrv to use all of the roles granted to the client Sarah, the following statement would be used:

ALTER USER sarah GRANT CONNECT THROUGH appsrv;

Each time a middle tier initiates an OCI, thick JDBC, or thin JDBC session for another database user, the database verifies that the middle tier is authorized to connect for that user by using the role specified.

3.10.1.6 Authorizing a Middle Tier to Proxy and Authenticate a User

The following statement authorizes the middle-tier server appserve to connect as user bill. It uses the WITH ROLE clause to specify that appserve activate all roles associated with bill, except payroll.

ALTER USER bill
    GRANT CONNECT THROUGH appserve 
    WITH ROLE ALL EXCEPT payroll;

To revoke the middle-tier server (appserve) authorization to connect as user bill, the following statement is used:

ALTER USER bill REVOKE CONNECT THROUGH appserve;

3.10.1.7 Authorizing a Middle Tier to Proxy a User Authenticated by Other Means

Use the AUTHENTICATED USING clause of the ALTER USER ... GRANT CONNECT THROUGH statement to authorize a user to be proxied, but not authenticated, by a middle tier. Currently, PASSWORD is the only means supported.

The following statement illustrates this form of authentication:

ALTER USER mary
    GRANT CONNECT THROUGH midtier
    AUTHENTICATED USING PASSWORD;

In the preceding statement, middle-tier server midtier is authorized to connect as user mary, and midtier must also pass the user password to the database server for authorization.

3.10.1.8 Reauthenticating the User Through the Middle Tier to the Database

Administrators can specify that authentication is required by using the AUTHENTICATION REQUIRED proxy clause with the ALTER USER SQL statement. In this case, the middle tier must provide user authentication credentials.

For example, suppose that user Sarah wants to connect to the database through a middle tier, appsrv. An administrator could require that appsrv provides authentication credentials for Sarah by using the following syntax:

ALTER USER sarah GRANT CONNECT THROUGH appsrv AUTHENTICATION REQUIRED;

The AUTHENTICATION REQUIRED clause ensures that authentication credentials for the user must be presented when the user is authenticated through the specified proxy.

The type of credentials can be either a password or a Kerberos ticket. When Kerberos tickets are passed to the database during proxy authentication, they are tested for validity. If the Kerberos tickets are invalid, the authentication attempt is rejected by the database server.

OCIAttrSet (OCISession *session_handle,
OCI_HTYPE_SESSION,
lxstp *password,
(ub4) 0,
OCI_ATTR_PASSWORD,
OCIError *error_handle);

OCIAttrSet(OCISession *session_handle,
OCI_HTYPE_SESSION,
lxstp *distinguished_name,
(ub4) 0,
OCI_ATTR_DISTINGUISHED_NAME,
OCIError *error_handle);

OCIAttrSet(OCISession *session_handle,
OCI_HTYPE_SESSION,
ub1 *certificate,
ub4 certificate_length,
OCI_ATTR_CERTIFICATE,
OCIError *error_handle);

3.10.1.9 Auditing Actions Taken on Behalf of the Real User

The proxy authentication features of Oracle Database enable you to audit actions that a middle tier performs on behalf of a user. For example, suppose an application server hrappserver creates multiple sessions for users Ajit and Jane. A database administrator could enable auditing for SELECT statements performed on the bonus table that hrappserver initiates for Jane as follows:

AUDIT SELECT TABLE BY hrappserver ON BEHALF OF Jane;

Alternatively, you could enable auditing on behalf of multiple users (in this case, both Jane and Ajit) connecting through a middle tier as follows:

AUDIT SELECT TABLE BY hrappserver ON BEHALF OF ANY;

This auditing option only audits SELECT statements being initiated by hrappserver on behalf of other users. You can enable separate auditing options to capture SELECT statements against the bonus table from clients connecting directly to the database:

AUDIT SELECT TABLE;

For audit actions taken on behalf of the real user, you cannot audit CONNECT ON BEHALF OF DN, because the user in the LDAP directory is not known to the database. However, if the user accesses a shared schema (for example, APPUSER), then you can audit CONNECT ON BEHALF OF APPUSER.

3.10.2.1 How Client Identifiers Work in Middle Tier Systems

Many applications use session pooling to set up a number of sessions to be reused by multiple application users. Users authenticate themselves to a middle-tier application, which uses a single identity to log in to the database and maintains all the user connections. In this model, application users are users who are authenticated to the middle tier of an application, but who are not known to the database. You can use a CLIENT_IDENTIFIER attribute, which acts like an application user proxy for these types of applications.

In this model, the middle tier passes a client identifier to the database upon the session establishment. The client identifier could actually be anything that represents a client connecting to the middle tier, for example, a cookie or an IP address. The client identifier, representing the application user, is available in user session information and can also be accessed with an application context (by using the USERENV naming context). In this way, applications can set up and reuse sessions, while still being able to keep track of the application user in the session. Applications can reset the client identifier and thus reuse the session for a different user, enabling high performance.

3.10.2.2 Using the CLIENT_IDENTIFIER Attribute to Preserve User Identity

You can use the CLIENT_IDENTIFIER predefined attribute of the built-in application context namespace, USERENV, to capture the application user name for use with global application context. You also can use the CLIENT_IDENTIFIER attribute independently. When you use the CLIENT_IDENTIFIER attribute independently from a global application context, you can set CLIENT_IDENTIFIER with the DBMS_SESSION interface. The ability to pass a CLIENT_IDENTIFIER to the database is supported in Oracle Call Interface (OCI), thick JDBC, and thin JDBC.

When you use the CLIENT_IDENTIFIER attribute with global application context, it provides flexibility and high performance for building applications. For example, suppose a Web-based application that provides information to business partners has three types of users: gold partner, silver partner, and bronze partner, representing different levels of information available. Instead of each user having his or her own session set up with individual application contexts, the application could set up global application contexts for gold partners, silver partners, and bronze partners. Then, use the CLIENT_IDENTIFIER to point the session at the correct context to retrieve the appropriate type of data. The application need only initialize the three global contexts once and use the CLIENT_IDENTIFIER to access the correct application context to limit data access. This provides performance benefits through session reuse and through accessing global application contexts set up once, instead of having to initialize application contexts for each session individually.

3.10.2.3 Using CLIENT_IDENTIFIER Independent of Global Application Context

Using the CLIENT_IDENTIFIER attribute is especially useful for those applications in which the users are unknown to the database. In these situations, the application typically connects as a single database user and all actions are taken as that user. Because all user sessions are created as the same user, this security model makes it difficult to achieve data separation for each user. These applications can use the CLIENT_IDENTIFIER attribute to preserve the real application user identity through to the database.

With this approach, sessions can be reused by multiple users by changing the value of the CLIENT_IDENTIFIER attribute, which captures the name of the real application user. This avoids the overhead of setting up a separate session and separate attributes for each user, and enables reuse of sessions by the application. When the CLIENT_IDENTIFIER attribute value changes, the change is added to the next OCI, thick JDBC, or thin JDBC call for additional performance benefits.

For example, a user Daniel connects to a Web Expense application. Daniel is not a database user; he is a typical Web Expense application user. The application accesses the built-in application context namespace and sets DANIEL as the CLIENT_IDENTIFIER attribute value. Daniel completes his Web Expense form and exits the application. Then, Ajit connects to the Web Expense application. Instead of setting up a new session for Ajit, the application reuses the session that currently exists for Daniel, by changing the CLIENT_IDENTIFIER to AJIT. This avoids the overhead of setting up a new connection to the database and the overhead of setting up a global application context. The CLIENT_IDENTIFIER attribute can be set to any value on which the application bases access control. It does not have to be the application user name.

To use the DBMS_SESSION package to set and clear the CLIENT_IDENTIFIER on the middle tier, use the following interfaces:

• SET_IDENTIFIER

• CLEAR_IDENTIFIER

The middle tier uses SET_IDENTIFIER to associate the database session with a particular user or group. Then, the CLIENT_IDENTIFIER is an attribute of the session and can be viewed in session information.

To set the CLIENT_IDENTIFIER attribute with OCI, use the OCI_ATTR_CLIENT_IDENTIFIER attribute in the call to OCIAttrSet(). Then, on the next request to the server, the information is propagated and stored in the server sessions. For example:

OCIAttrSet (session,

OCI_HTYPE_SESSION,
(dvoid *) "appuser1",
(ub4)strlen("appuser1"),
OCI_ATTR_CLIENT_IDENTIFIER,
OCIError *error_handle);

For applications that use JDBC, in a connection pooling environment, an application developer can use the client identifier to identify which lightweight user is currently using the database session. To set the CLIENT_IDENTIFIER attribute for JDBC applications, use the following oracle.jdbc.OracleConnection interface methods:

• setClientIdentifier(): Sets the client identifier for a connection

• clearClientIdentifier(): Clears the client identifier for a connection

  See Also:

  • Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_SESSION package

  • Oracle Call Interface Programmer's Guide about how the OCI_ATTR_CLIENT_IDENTIFIER user session handle attribute is used in middle-tier applications

  • The section about the oracle.jdbc.OracleConnection interface in the Oracle Database JDBC Developer's Guide and Reference for information about the setClientIdentifer() and the clearClientIdentifier() methods