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.

About Proxy Authentication

Oracle Database provides proxy authentication in Oracle Call Interface (OCI), JDBC/OCI, or JDBC Thin Driver 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.

You can design a middle-tier server to authenticate clients in a secure fashion by using the following three forms of proxy authentication:

In all cases, an administrator must authorize the middle-tier server to act on behalf of the client.

Related Topics

Advantages of Proxy Authentication

In multitier environments, proxy authentication preserves client identities and privileges through all tiers in middle-tier applications and by auditing client actions.

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 the following benefits to organizations:

In addition, Oracle Database proxy authentication provides the following security benefits:

Who Can Create Proxy User Accounts?

To create proxy user accounts, users must have special privileges.

These privileges are as follows:

Guidelines for Creating Proxy User Accounts

Oracle provides special guidelines for when you create proxy user accounts.

Related Topics

Creating Proxy User Accounts and Authorizing Users to Connect Through Them

The CREATE USER and ALTER USER statements can be used to create a proxy user and authorize users to connect through it.

A proxy user in a proxy session can enable a password-protected role or secure application role only if the role has been allowed to be enabled with the WITH ROLE or WITH ROLE ALL clause. (If this clause is not specified, then WITH ROLE ALL is the default.) If WITH ROLE does not specify the secure roles, then those roles cannot be enabled, even with the correct password.

  1. Use the CREATE USER statement to create the proxy user account.

    For example:

    CREATE USER appuser IDENTIFIED BY password;

  2. Use the GRANT CONNECT THROUGH clause of the ALTER USER statement to enable an existing user to connect through the proxy user account.

    For example:

    ALTER USER preston GRANT CONNECT THROUGH appuser;

Be aware that the user name and proxy combination must not exceed 250 characters.

Suppose user preston has a large number of roles, but you only want this user to use one role (for example, the appuser_role) when this user is connected to the database through the appuser proxy account. You can use the following ALTER USER statement:

ALTER USER preston GRANT CONNECT THROUGH appuser WITH ROLE appuser_role;

Any other roles that user preston has will not be available to her as long as this user is connecting as the appuser proxy.

After you complete these steps, user preston can connect using the appuser proxy user as follows:

CONNECT appuser[preston]
Enter password: appuser_password

Related Topics

Proxy User Accounts and the Authorization of Users to Connect Through Them

The CREATE USER statement enables you to create the several types of user accounts, all of which can be used as proxy accounts.

These accounts are as follows:

Note the following:

Related Topics

Using Proxy Authentication with the Secure External Password Store

Use a secure external password store if you are concerned about the password used in proxy authentication being obtained by a malicious user.

To accomplish this, you use the secure external password store with the proxy authentication to store the password credentials in a wallet.

Connecting to Oracle Database using proxy authentication and the secure external password store is ideal for situations such as running batch files. When a proxy user connects to the database and authenticates using a secure external password, the password is not exposed in the event that a malicious user tries to obtain the password.

To use proxy authentication with the secure external password store:

  1. Configure the proxy authentication account.

  2. Configure the secure external password store.

Afterward, the user can connect using the proxy but without having to specify a password. For example:

sqlplus [preston]/@db_alias

When you use the secure external password store, the user logging in does not need to supply the user name and password. Only the SERVICE_NAME value (that is, db_alias) from the tnsnames.ora file must be specified. This SERVICE_NAME value maps to a PDB.

Related Topics

How the Identity of the Real User Is Passed with Proxy Authentication

You can use Oracle Call Interface, JDBC/OCI, or Thin drivers for enterprise users or database users.

These tools enable a middle tier to set up several user sessions within a single database connection, each of which uniquely identifies a connected user (connection pooling)

These sessions reduce the network overhead of creating separate network connections from the middle tier to the database.

If you want to authenticate from clients through a middle tier to the database, then 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 by using a user name and password or an X.509 certificate by means of SSL.

  2. 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 Database, 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, JDBC/OCI, or Thin driver.

    • 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 can include a password (which the database verifies against the password store in the database). The session can 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.

      Example 1: 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.

      Example 2: 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 use the roles on behalf of the client.

  4. The database verifies that the middle tier has the privilege to create sessions on behalf of the user.

    The OCISessionBegin call fails if the application server cannot perform a proxy authentication on behalf of the client by the administrator, or if the application server is not allowed to activate the specified roles.

Limits to the Privileges 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.

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 use only the clerk role on their behalf.

An administrator can grant permission for appsrv to initiate connections on behalf of Sarah using the clerk role only by using the following SQL statement:

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 enable appsrv to use all of the roles granted to the client Sarah, you can use the following statement:

ALTER USER sarah GRANT CONNECT THROUGH appsrv;

Each time a middle tier initiates an OCI, JDBC/OCI, or Thin driver 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 roles 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 Database changes or removes default roles. For example, the CONNECT role now has only the CREATE 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.

A proxy user in a proxy session can enable a password-protected role or secure application role only if the role has been allowed to be enabled with the WITH ROLE or WITH ROLE ALL clause. (If this clause is not specified, then WITH ROLE ALL is the default.) If WITH ROLE does not specify the secure roles, then those roles cannot be enabled, even with the correct password.

Related Topics

Authorizing a Middle Tier to Proxy and Authenticate a User

You can authorize a middle-tier server to connect as a user.

A proxy user in a proxy session can enable a password-protected role or secure application role only if the role has been allowed to be enabled with the WITH ROLE or WITH ROLE ALL clause. (If this clause is not specified, then WITH ROLE ALL is the default.) If WITH ROLE does not specify the secure roles, then those roles cannot be enabled, even with the correct password.

To authorize a middle-tier server to connect as a user, use the ALTER USER statement.

The following statement authorizes the middle-tier server appserve to connect as user bill. It uses the WITH ROLE clause to specify that appserve activate all roles associated with bill, except payroll.

ALTER USER bill
    GRANT CONNECT THROUGH appserve
    WITH ROLE ALL EXCEPT payroll;

To revoke the middle-tier server (appserve) authorization to connect as user bill, you can use the REVOKE CONNECT THROUGH clause. For example:

ALTER USER bill REVOKE CONNECT THROUGH appserve;

Authorizing a Middle Tier to Proxy a User Authenticated by Other Means

You can authorize a middle tier to proxy a user that has been authenticated by other means.

Currently, PASSWORD is the only means supported.

Use the AUTHENTICATION REQURED clause of the ALTER USER ... GRANT CONNECT THROUGH statement to authorize a user to be proxied, but not authenticated, by a middle tier.

For example:

ALTER USER mary
    GRANT CONNECT THROUGH midtier
    AUTHENTICATION REQUIRED;

In the preceding statement, middle-tier server midtier is authorized to connect as user mary, and midtier must also pass the user password to the database server for authorization.

Reauthenticating a User Through the Middle Tier to the Database

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

To require that appsrv provides authentication credentials for the user Sarah, use 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 you use the AUTHENTICATED USING PASSWORD proxy clause, then Oracle Database transforms it to AUTHENTICATION REQUIRED.

Using Password-Based Proxy Authentication

When you use password-based proxy authentication, Oracle Database passes the password of the client 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 type of authentication is that the client computer does not have to have Oracle software installed on it to perform database operations.

To pass the password of the client, configure the the middle-tier server to call the OCIAttrSet() function as follows, passing OCI_ATTR_PASSWORD as the type of the attribute being set.

OCIAttrSet(
  session_handle,    /* Pointer to a handle whose attribute gets modified. */
  OCI_HTYPE_SESSION, /* Handle type: OCI user session handle. */
  password_ptr,      /* Pointer to the value of the password attribute. */
  0,                 /* The size of the password attribute value is already
                        known by the OCI library. */
  OCI_ATTR_PASSWORD, /* The attribute type. */
  error_handle);     /* An error handle used to retrieve diagnostic
                        information in the event of an error. */

Using Proxy Authentication with Enterprise Users

How the middle-tier responds for proxy authentication depends on how the user is authenticated, either as an enterprise user or a password-authenticated user.

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 configure proxy authentication with enterprise users, configure the application server and the middle tier to use the appropriate Oracle Call Interface settings:

If the type is not specified, then the database uses its default certificate type of X. 509.

Note:

If you are using proxy authentication for password-authenticated enterprise users, then use the same OCI attributes as for database users authenticated by password (OCI_ATTR_USERNAME). Oracle Database first checks the user name against the database. If it finds no user, then the database checks the user name in the directory. This user name must be globally unique.

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

Related Topics

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; this user 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 the 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.

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,
*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");

See Also:

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:

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"

  2. Connect to the CDB with the SYSDBA administrative privilege.

    CONNECT / AS SYSDBA

  3. Do one of the following:

    • To restart the entire CDB:

      SHUTDOWN IMMEDIATE
STARTUP

    • To restart a specific PDB:

      ALTER PLUGGABLE DATABASE pdb_name CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE pdb_name OPEN;

      To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

See Also:

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

  2. 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';

  2. Restart the database.

    For example:

    SHUTDOWN IMMEDIATE
STARTUP