16 Preserving User Identity in Multitiered Environments

16.1.4.1 Client to Middle Tier Authentication

Client authentication to the middle tier is clearly required if a system must conform to 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.

16.1.4.2 Middle Tier to Database Authentication

Because 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, Oracle Database does not allow unauthenticated sessions. Again, middle tier to database authentication can also be mutual if using a protocol that supports it, such as SSL.

16.1.4.3 Client Reauthentication Through Middle Tier to Database

Client reauthentication from the middle tier to the database is problematic in three-tier systems. The user name might not be the same on the middle tier and the database. In this case, users may need to reenter a user name 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 reauthenticate itself to the database, the middle tier either needs to ask the user for a credential (which it 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 user credentials.

Reauthenticating 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. It is therefore reasonable 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 reauthentication 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 reauthenticate a browser client (through the middle tier) to the database.

In short, organizations deploying three-tier systems require flexibility for the reauthentication of the client.

16.2.1.1 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 a number of user sessions within a single database connection, 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.

16.2.1.2 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, 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 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:

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 or thick 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.

16.2.1.3 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. A DBA 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.

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);

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

AUDIT SELECT TABLE BY hrappserver ON BEHALF OF Jane;

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:

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. A DBA can enable separate auditing options to capture SELECTs 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.

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

• 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 fat clients with a number of thin clients and an application server.

In addition, Oracle 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 and thick 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.

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

16.2.2.2 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 user name 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.

16.2.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 very 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 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 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, 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:

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

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

  See Also:

  • The chapter on the DBMS_SESSION interface in the PL/SQL Packages and Types Reference

  • The section on OCI_ATTR_CLIENT_IDENTIFIER user session handle attribute in the Oracle Call Interface Programmer's Guide

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