Skip Headers

Oracle® Database Security Guide
10g Release 1 (10.1)

Part Number B10773-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

Preserving User Identity in Multitiered Environments

Enforcing security in multitiered environments can be challenging. This chapter discusses the risks associated with computing environments that span multiple tiers, and explains how to implement proxy authentication and use client identifiers for preserving user identity.

This chapter contains the following topics:

Topic Category Links to Topics

Security Challenges of Three-tier Computing

Oracle Database Solutions for Preserving User Identity

Security Challenges of Three-tier Computing

While three-tier computing provides many benefits, it raises a number of security issues. These issues are described in the following sections:

Who Is the Real User?

Most organizations want to know the identity of the actual user who is accessing the database, for reasons of access control, resource monitoring, or auditing. User accountability is diminished if the identity of the users cannot be traced through all tiers of the application.

Furthermore, if only the application server knows who the user is, then all security enforcement for each user must be done by the application itself. Application-based security is very expensive. If each application that accesses the data must enforce security, then security must be re-implemented in each and every application. It is often preferable to build security on the data itself, accountability for each user enforced within the database.

Does the Middle Tier Have Too Much Privilege?

Some organizations are willing to accept three-tier systems within the enterprise, in which "all-privileged" middle tiers, such as transaction processing (TP) monitors, can perform all actions for all users. In this architecture, the middle tier connects to the database as the same user for all application users. It therefore needs to have all privileges that application users need to do their jobs.

This computing model can be undesirable in the Internet, where the middle tier resides outside, on, or just inside a firewall. More desirable, in this context, is a limited trust model, in which the identity of the real client is known to the data server, and the application server (or other middle tier) has a restricted privilege set.

Also useful is the ability to limit the users on whose behalf a middle tier can connect, and the roles the middle tier can assume for the user. For example, many organizations would prefer that users have different privileges depending on where they are connecting from. A user connecting to a Web server or application server on the firewall might only be able to use very minimal privileges to access data, whereas a user connecting to a Web server or application server within the enterprise might be able to exercise all privileges she is otherwise entitled to have.

How to Audit? Whom to Audit?

Accountability through auditing is a basic principle of information security. Most organizations want to know on whose behalf a transaction was accomplished, not just that a particular application server performed a transaction. A system must therefore be able to differentiate between a user performing a transaction, and an application server performing a transaction on behalf of a user.

Auditing in three-tier systems should be tied to the issue of knowing the real user: if you cannot preserve the user's identity through the middle tier of a three-tier application, then you cannot audit actions on behalf of the user.

What Are the Authentication Requirements for Three-tier Systems?

In client/server systems, authentication tends to be straightforward: the client authenticates to the server. In three-tier systems authentication is more difficult, because there are several potential types of authentication.

Client to Middle Tier Authentication

Client authentication to the middle tier is clearly required if a system is to conform with basic security principles. The middle tier is typically the first gateway to useful information that the user can access. Users must, therefore, authenticate to the middle tier. Note that such authentication can be mutual; that is, the middle tier authenticates to the client just as the client authenticates to the middle tier.

Middle Tier to Database Authentication

Since the middle tier must typically initiate a connection to a database to retrieve data (whether on its own behalf or on behalf of the user), this session clearly must be authenticated. In fact, the Oracle Database does not allow unauthenticated sessions. Again, middle tier to database authentication can also be mutual if using a protocol that supports this, such as SSL.

Client Re-Authentication Through Middle Tier to Database

Client re-authentication from the middle tier to the database is problematic in three-tier systems. The username might not be the same on the middle tier and the database. In this case, users may need to reenter a username and credential, which the middle tier uses to connect on their behalf. Or, more commonly, the middle tier may need to map the username provided, to a database username. This mapping is often done in an LDAP-compliant directory service, such as Oracle Internet Directory.

For the client to re-authenticate himself to the database, the middle tier either needs to ask the user for a credential (which it then must be trusted to pass to the database), or the middle tier must retrieve a credential for the user and use that to authenticate the user. Both approaches involve security risks, because the middle tier is provided with the user's credentials.

Re-authenticating the client to the back-end database is not always beneficial. First, two sets of authentication handshakes for each user involves considerable network overhead. Second, you must trust the middle tier to have authenticated the user. (You clearly must trust the middle tier if it is privy to the user's credential.) It is therefore not unreasonable for the database to simply accept that the middle tier has performed proper authentication. In other words, the database accepts the identity of the real client without requiring the real client to authenticate herself.

For some authentication protocols, client re-authentication is just not possible. For example, many browsers and application servers support the Secure Sockets Layer (SSL) protocol. Both the Oracle Database (through Oracle Advanced Security) and Oracle Application Server support the use of SSL for client authentication. However, SSL is a point-to-point protocol, not an end-to-end protocol. It cannot be used to re-authenticate a browser client (through the middle tier) to the database.

In short, organizations deploying three-tier systems require flexibility as regards re-authentication of the client.

Oracle Database Solutions for Preserving User Identity

Many organizations want to know who the user is through all tiers of an application, without sacrificing the benefits of a middle tier. The Oracle Database supports the following ways of preserving user identity through the middle tier of an application:

Proxy Authentication

The following sections explain how proxy authentication works and how to use it:

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

For enterprise users or database users, OCI or thick JDBC enables a middle tier to set up, within a single database connection, a number of user sessions, each of which uniquely identifies a connected user. This is commonly referred to as connection pooling. These sessions reduce the network overhead of creating separate network connections from the middle tier to the database.

Authentication Process from Clients through Middle Tiers to the Database

The full authentication sequence from the client to the middle tier to the database occurs as follows:

  1. The client authenticates to the middle tier, using whatever form of authentication the middle tier will accept. For example, the client could authenticate to the middle tier using a username/password, or an X.509 certificate by means of SSL.
  2. The middle tier authenticates itself to the database, using whatever form of authentication the database accepts. This could be a password, or an authentication mechanism supported by Oracle Advanced Security, such as a Kerberos ticket or an X.509 certificate (SSL).
  3. The middle tier then creates one or more sessions for users using OCI or thick JDBC.
    • If the user is a database user, the session must, as a minimum, include the database username. If the database requires it, the session may also include a password (which the database verifies against the password store in the database). The session may also include a list of database roles for the user.
    • If the user is an enterprise user, the session may provide different information depending on how the user is authenticated. For example:
      • If the user authenticated to the middle tier by way of SSL, then the middle tier can provide the DN from the user's X.509 certificate, or the certificate itself in the session. The database uses the DN to look up the user in Oracle Internet Directory.
      • If the user is a password-authenticated enterprise user, then the middle tier must provide, as a minimum, a globally unique name for the user. The database uses this name to look up the user in Oracle Internet Directory. If the session also provides a password for the user, the database will verify the password against Oracle Internet Directory. The user's roles are automatically retrieved from Oracle Internet Directory after the session is established.
    • The middle tier may optionally provide a list of database roles for the client. These roles are enabled if the proxy is authorized to exercise the roles on the client's behalf.
  4. The database verifies that the middle tier has the privilege to create sessions on behalf of the user.

    The OCISessionBegin call will fail if the application server is not allowed to proxy on behalf of the client by the administrator, or if the application server is not allowed to activate the specified roles.

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. The 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, 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 exercise only the clerk role on her behalf.

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


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:


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


Instead of using default roles, create your own and assign only necessary privileges to them. For example, if users only need CREATE SESSION, then granting them CONNECT, which includes several privileges (see Table 10-1, " Predefined Roles"), may result in assigning too many privileges for your database application. Creating your own roles enables you to control the privileges granted by them, and protects you if Oracle changes or removes default roles.

Re-authenticating 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. A DBA could require that appsrv provides authentication credentials for Sarah by using the following syntax:


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


For backward compatibility, if a DBA uses the AUTHENTICATED USING PASSWORD proxy clause, then the system transforms it to AUTHENTICATION REQUIRED.

Using Password-Based Proxy Authentication

Using password-based proxy authentication, the password of the client is passed to the middle-tier server. The middle-tier server then passes the password as an attribute to the data server for verification. The main advantage to this is that the client machine does not have to have Oracle software actually installed on it to perform database operations.

To pass over the password of the client, the middle-tier server calls OCIAttrSet() with the following pseudo-interface:

OCIAttrSet (OCISession *session_handle,
lxstp *password,
(ub4) 0,
OCIError *error_handle);
Using Proxy Authentication with Enterprise Users

If the middle tier is connecting to the database as a client who is an enterprise user, either the distinguished name, or the X.509 certificate containing the distinguished name is passed over instead of the database user name. If the user is a password-authenticated enterprise user, then the middle tier must provide, as a minimum, a globally unique name for the user. The database uses this name to look up the user in Oracle Internet Directory.

To pass over the distinguished name of the client, the application server would call OCIAttrSet() with the following pseudo-interface.

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

To pass over the entire certificate, the middle tier would use the following pseudo-interface:

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

If the type is not specified, then the server will use its default certificate type of X.509.



If using proxy authentication for password-authenticated enterprise users, then use the same OCI attributes as for database users authenticated by password (OCI_ATTR_USERNAME). The database first checks the username against the database; if no user is found, then the database checks the username in the directory. This username must be globally unique.

Auditing Actions Taken on Behalf of the Real User

The proxy authentication features of the 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 DBA could enable auditing for SELECTs on the bonus table that hrappserver initiates for Jane as follows:


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


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


For audit actions taken on behalf of the real user, you cannot audit CONNECT ON BEHALF OF DN, since 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.

See Also:

Advantages of Proxy Authentication

In multitier environments, proxy authentication enables you to control 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 many benefits to organizations.

In addition, Oracle proxy authentication delivers the following security benefits:

Client Identifiers

The following sections explain how using client identifiers works and how to use them:

Support for Application User Models by Using Client Identifiers

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. Oracle Database supports use of a CLIENT_IDENTIFIER attribute that 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.

Using the CLIENT_IDENTIFIER Attribute to Preserve User Identity

The CLIENT_IDENTIFIER, a predefined attribute of the built-in application context namespace, USERENV, can be used to capture the "application username" for use with global application context, or it can be used independently. When used independent of global application context, CLIENT_IDENTIFIER can be set with the DBMS_SESSION interface. The ability to pass a CLIENT_IDENTIFIER to the database is supported in OCI and thick JDBC.

When CLIENT_IDENTIFIER is used 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 own session set up with individual application contexts, the application could set up global applications contexts for gold partners, silver partners, and bronze partners. Then, use the CLIENT_IDENTIFIER to point the session at the correct context, in order 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.

See Also:

"How to Use Global Application Context" for a discussion about implementing global application contexts and an example of using the CLIENT_IDENTIFIER attribute with it.

Using CLIENT_IDENTIFIER Independent of Global Application Context

Using the CLIENT_IDENTIFIER attribute is especially useful for applications whose 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. Since all user sessions are created as the same user, this security model makes it very difficult to achieve data separation for each user. These applications can use the CLIENT_IDENTIFIER attribute to preserve the "real" application user's identity to the database.

With this approach, sessions can be reused by multiple users by changing the value of the CLIENT_IDENTIFIER attribute, which is used to capture 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 piggybacked on the next OCI or thick 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 username.

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

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,

(dvoid *) "appuser1",
OCIError *error_handle);

For applications that use JDBC, in a connection pooling environment, the client identifier can be used to identify which light-weight user is currently using the database session. To set the CLIENT_IDENTIFIER for JDBC applications, use the following oracle.jdbc.OracleConnection interface methods: