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 sections:
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 reimplemented in each and every application. It is often preferable to build security on the data itself, and also to have accountability for each user enforced within the database.
Some organizations are willing to accept three-tier systems within the enterprise. In this architecture, all-privileged middle tiers, such as transaction processing (TP) monitors, can perform all actions for all users. The middle tier connects to the database as the same user for all application users. It, therefore, needs all privileges that application users need to do their jobs.
This computing model can be undesirable on 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 user 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 many types of 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.
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.
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.
Many organizations would like to know who the user is through all tiers of an application without sacrificing the benefits of a middle tier. 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 that 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:
Passing Through the Identity of the Real User by Using Proxy Authentication
Reauthenticating the User Through the Middle Tier to the Database
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.
The full authentication sequence from the client to the middle tier to the database occurs as follows:
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 by using a username and password or an X.509 certificate by means of SSL.
The middle tier authenticates itself to the database by 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).
The middle tier then creates one or more sessions for users using OCI or thick JDBC.
If the user is a database user, then the session must, as a minimum, include the database user name. If the database requires it, then 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, then the session may provide different information depending on how the user is authenticated. For example:
If the user authenticates to the middle tier using SSL, then the middle tier can provide the DN from the X.509 certificate of the user, 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, then the database will verify the password against Oracle Internet Directory. User 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 behalf of the client.
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.
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.
Note:
Instead of using default roles, create your own and assign only necessary privileges to them. Creating your own roles enables you to control the privileges granted by them and protects you if Oracle changes or removes default roles. For example, the CONNECT role now has only theCREATE SESSION
privilege, the one most directly needed when connecting to a database.
However, CONNECT formerly provided several additional privileges, often not needed or appropriate for most users. Extra privileges can endanger the security of your database and applications. These have now been removed from CONNECT
, and both CONNECT
and RESOURCE
roles will be deprecated in future Oracle Database versions
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.
Note:
For backward compatibility, if a DBA uses theAUTHENTICATED USING PASSWORD
proxy clause, then the system transforms it to AUTHENTICATION REQUIRED
.When 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 the OCIAttrSet()
function 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 connects to the database as a client who is an enterprise user, then 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.
Note:
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 user name against the database. If no user is found, then the database checks the user name in the directory. This user name must be globally unique.
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 SELECT
s 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 SELECT
s 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
.
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.The following sections explain how using client identifiers works and how to use them:
Support for Application User Models by Using Client Identifiers
Using the CLIENT_IDENTIFIER Attribute to Preserve User Identity
Using CLIENT_IDENTIFIER Independent of Global Application Context
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 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.
See Also:
"How to Use Global Application Context" for a discussion about implementing global application contexts and an example of using theCLIENT_IDENTIFIER
attribute with itUsing 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