Preserving User Identity in Multitiered Environments

You can use middle tier servers for proxy authentication and client identifiers to identify application users who are not known to the database.

Middle Tier Server Use for Proxy Authentication

Oracle Call Interface (OCI), JDBC/OCI, or JDBC Thin Driver supports the middle tier for proxy authentication for database users or enterprise users.

Using Client Identifiers to Identify Application Users Unknown to the Database

Client identifiers preserve user identity in middle tier systems; they also can be used independently of the global application context.

About Client Identifiers

Oracle Database provides the CLIENT_IDENTIFIER attribute of the built-in USERENV application context namespace for application users.

These application users are known to an application but unknown to the database. The CLIENT_IDENTIFIER attribute can capture any value that the application uses for identification or access control, and passes it to the database. The CLIENT_IDENTIFIER attribute is supported in OCI, JDBC/OCI, or Thin driver.

How Client Identifiers Work in Middle Tier Systems

Many applications use session pooling to set up several 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.

Use of the CLIENT_IDENTIFIER Attribute to Preserve User Identity

The CLIENT_IDENTIFIER predefined attribute of the built-in application context namespace, USERENV, captures the application user name for use with a 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), JDBC/OCI, or Thin driver.

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.

Use of the 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, JDBC/OCI, or Thin driver call for additional performance benefits.

For example, the 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.

Setting the CLIENT_IDENTIFIER Independent of Global Application Context

You can set the CLIENT_IDENTIFIER setting with Oracle Call Interface to be independent of the global application context.

For example:

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

For applications that use JDBC, be aware that JDBC does not set the client identifier. To set the client identifier in a connection pooling environment, use Dynamic Monitoring Service (DMS) metrics. If DMS is not available, then use the connection.setClientInfo method. For example:

connection.setClientInfo("E2E_CONTEXT.CLIENT_IDENTIFIER", "appuser");

Use of the DBMS_SESSION PL/SQL Package to Set and Clear the Client Identifier

The DBMS_SESSION PL/SQL package manages client identifiers on both the middle tier and the database itself.

To use the DBMS_SESSION package to set and clear the CLIENT_IDENTIFIER value on the middle tier, you must use the SET_IDENTIFIER and CLEAR_IDENTIFIER procedures.

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.

If you plan to use the DBMS_SESSION.SET_IDENTIFIER procedure, then be aware of the following:

SHOW PARAMETER EVENT

NAME                           TYPE               VALUE

------------------------------ ------------------ ------------------
event                          string             clientid_overwrite

Enabling the CLIENTID_OVERWRITE Event System-Wide

The ALTER SYSTEM statement can enable the CLIENTID_OVERWRITE event system-wide.

  1. Enter the following ALTER SYSTEM statement:
ALTER SYSTEM SET EVENTS 'CLIENTID_OVERWRITE';
Or, enter the following line in your `init.ora` file:

event="clientid_overwrite"

  1. Restart the database.

    For example:

SHUTDOWN IMMEDIATE
STARTUP

Enabling the CLIENTID_OVERWRITE Event for the Current Session

The ALTER SESSION statement can enable the CLIENTID_OVERWRITE event for the current session only.

  1. Use the ALTER SESSION statement to set the CLIENTID_OVERWRITE value for the session only.

    For example:

ALTER SESSION SET EVENTS 'CLIENTID_OVERWRITE OFF';

  1. If you set the client identifier by using the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure, then run DBMS_SESSION.SET_IDENTIFIER so that the client identifier settings are the same.

    For example:

DBMS_SESSION.SET_IDENTIFIER(session_id_p);

Disabling the CLIENTID_OVERWRITE Event

The ALTER SYSTEM statement can disable the CLIENTID_OVERWRITE event.

  1. Enter the following ALTER SYSTEM statement:
ALTER SYSTEM SET EVENTS 'CLIENTID_OVERWRITE OFF';

  1. Restart the database.

    For example:

SHUTDOWN IMMEDIATE
STARTUP