|Oracle® Database Security Guide
10g Release 1 (10.1)
Part Number B10773-01
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
While three-tier computing provides many benefits, it raises a number of security issues. These issues are described in the following sections:
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.
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.
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.
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 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.
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 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.
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:
Oracle Database provides proxy authentication in OCI or thick JDBC for database users or enterprise users. Enterprise users are those who are managed in Oracle Internet Directory and who access a shared schema in the database.
Oracle Database provides the
CLIENT_IDENTIFIER attribute of the built-in
USERENV application context namespace for application users. These users are known to an application but unknown to the database. The
CLIENT_IDENTIFIER attribute can be used to capture any value the application uses for identification or access control and pass it to the database.
CLIENT_IDENTIFIER is supported in OCI, thick JDBC, and thin JDBC.
The following sections explain how proxy authentication works and how to use it:
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.
The full authentication sequence from the client to the middle tier to the database occurs as follows:
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.
"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.
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
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:
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
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, OCI_HTYPE_SESSION, lxstp *password, (ub4) 0, OCI_ATTR_PASSWORD, OCIError *error_handle);
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, OCI_HTYPE_SESSION, lxstp *distinguished_name, (ub4) 0, OCI_ATTR_DISTINGUISHED_NAME, OCIError *error_handle);
To pass over the entire certificate, the middle tier would use the following pseudo-interface:
OCIAttrSet(OCISession *session_handle, OCI_HTYPE_SESSION, ub1 *certificate, ub4 certificate_length, OCI_ATTR_CERTIFICATE, 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.
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.
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:
The following sections explain how using client identifiers works and how to use them:
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.
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.
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.
"How to Use Global Application Context" for a discussion about implementing global application contexts and an example of 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
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, 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