Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 3, 2010

Using Proxy Authentication Methods in Oracle Database 11g

By Karen Reliford

Oracle DBAs have many different areas of responsibility and accountability for the database that they maintain. With many applications using middle tier connections, DBAs face an interesting challenge in limiting access and auditing end user activities. Oracle offers an option called proxy authentication that can help to address these challenges.

Oracle DBAs have many different areas of responsibility and accountability for the database that they maintain. These areas include availability, recoverability, performance monitoring, accessibility and security.

One principal aspect of security is controlling user access to the database. Controlling user access can be further broken down into three major components. First, there must be a method for identifying the users. Second, there must be methods for limiting access to objects and activities within the database for the users. Third, the ability to audit user activities is a common requirement.

One method of identifying users would be to create specific user accounts in the database for each and every end user on the system. The DBA would then determine which activities the end user needs to be able to perform and (usually via roles) grant them the appropriate privileges to do those specific tasks.

However, in many cases with Oracle databases and associated applications the true end user is captured by the middle tier application, which then connects to the database and acts on behalf of the end user. In this case, the middle tier is authenticating the user rather than the actual database itself and the middle tier needs to be able to invoke roles and the specific privileges needed to act on behalf of the end user. This essentially is what proxy authentication is all about.

This through-the-middle-tier approach, while it has many administrative advantages, can make some aspects of security a little more challenging. Specifically, there are three challenges to deal with; how to actually identify the "real" end user, how to authenticate this end user to the database and how to restrict the privileges of the middle tier to only what is really required for the end user.

If all authentication and access control is only done via the middle tier, it can be "expensive" meaning that all applications must enforce security and whatever code is being used to manage security must appear in each and every application, which may mean a large increase in maintenance if security changes are ever required. Also, there must be a methodology in place to ensure that the only means end users have to get to the database is through the middle tier. Database auditing of specific user activities can also be particularly challenging if the database itself has no information on the true end user. Generally, auditing requires that the system is able to differentiate between something done by an end user, and an activity done by an application on behalf of a user.

Middle Tier Connection Approaches

There are several commonly used approaches to middle tier connections to an Oracle database.

First is known as pass-through where the end user is known by the database. Essentially the application asks for the user's credentials and sends them directly to the database for authentication. This method would require that users have their own account in the database. The application is not actually responsible for doing any user authentication or access control. The database is easily able to audit end user activities in this model. (Sounds an awful lot like a client server model doesn't it.)

A second option entails having the middle tier responsible for all aspects of user security in the database. The application user account has all privileges required for all users of the system. This approach goes 100% against the important security principle of "least-privilege". Auditing would be very difficult in this model unless the application is written or designed to somehow capture or keep mapping information of who asked for what actions to take place.

A third approach would be to re-authenticate the user to the database. This essentially means the application is going to forward login information to the database for authentication; however, it may not be the same as the login information used by the end user to log into the application in first place. This would generally use a configuration such as LDAP (lightweight directory access protocols) or single-sign-on in order to keep the user credential information stored securely on the system.

Yet another approach might involve passing information via some sort of token, which maps the end user to a session. The true end user is still not known to the database, however the database can use the information from the token to do any required end user auditing. The application would use the following packages and procedures to set the client identifier information: DBMS_APPLICTION_INFO.SET_CLIENT_IDENTIFIER or DBMS_SESSION.SET_IDENTIFIER.

Proxy authentication is the fifth method that can be used in Oracle databases to help deal with some of the potential issues around middle tier connections.

Oracle Proxy Authentication Options

If the user is either a database user or an enterprise user (maintained via Oracle Internet Directory with a distinguished name) then Oracle offers a solution based on the fact that the end user is actually known to the database. In either of these cases, a pass-through approach or re-authentication approach can be used.

Oracle also has a proxy method that can be used for situations where the end user is known only to the application and not directly to the database. This method is known as an application user model. In this case, the user is tracked via an assigned identifier and the activities can be audited using that identifier rather than end user information.

Proxy Authentication for End Users or Enterprise Users

Both OCI (Oracle Call Interface) and JDBC (Java Database Connectivity) enable a middle tier to set up a single database connection that will ultimately support a number of user sessions, which each specifically identify the end user who is connected to that particular session.

The step-by-step activities that take place during this type of authentication are

1) The client authenticates to the middle tier

2) The middle tier connects to the database as a database user

3) The middle tier creates a session using OCI or JDBC

    1. If the user is a database user then the session must include the user name and depending on database requirements, may also include a password. Optionally database roles required may be included.
    2. If the user is an enterprise user the middle tier may provide a distinguished name or certificate, which the database uses to look up in the OID to get the specific user information and retrieve information on roles.
    3. If the user is a password authenticated enterprise user the middle tier provides a global unique name for the user, which the database uses to check the OID and retrieve the role information.

4) If the user is a database user, the database checks to ensure the middle tier has the
necessary privileges to create a session on behalf of the database user with the role information provided.

The DBA uses the ALTER USER command to indicate that a given database user is to be authenticated through a middle tier process.


This command can be used if the middle tier is trusted to perform the authentication of the end user and the end user does not have to provide a password to the middle tier. This method can be used if the middle tier is in a trusted location such as behind a firewall. The session will behave as if karen has been connected normally to the database.


This method requires that the end user provides a password for the middle tier to use to authenticate to the database, This option is a better choice if the middle tier application is outside of firewalls.

If the user is an enterprise user, the middle tier is assumed to be a trusted system and the middle tier has already authenticated the user and can provide a distinguished name or certificate as part of the information to the database,


In this case, the distinguished name from the OID or LDAP server is used instead of a password. The distinguished name is retrieved by the middle tier application. The application would use the OCIAttrSet() with the OCI_ATTR_DISTINGUISHED_NAME attribute.


The OCIAttrSet() with the OCI_ATTR_CERTIFICATE would be used to retrieve and pass the certificate information to the database. The default certificate type is X.509 and the default version is 3. It must be noted however, the certificate method is not recommended as Oracle might be de-supporting this authentication method in the future.

Proxy based user connections can also be made through SQL*PLUS and the syntax is slightly different depending on whether the end user is a database user or not.

CONNECT midtier[karen]/midtier_pwd;

This option would be used if user karen is known to the database. In this case the user is karen connected to the schema karen and midtier may have the privileges to invoke some or all of the roles assigned to user karen.

CONNECT karen[midtier]/midtier_pwd

In this case, karen is an enterprise user and not directly known to the database. In this situation, the user is midtier and only has roles and privileges assigned to midtier.

CONNECT karen[midtier]/karen_pwd;

This allows for karen to be authenticated through the OID. All of the proxy permissions are maintained through the OID. In this case the user actually connected to the database is midtier, however the authentication is done on the enterprise user karen via the OID.

The DBA can also set up a user account that can then be used by any enterprise user to connect to the database.


If enterprise users karen and ron are both account managers and need to be able to login with the privileges of the user acct_mgr (essentially a shared schema approach) then setting up a proxy account tied to enterprise user accounts may be a solution for DBAs to consider. Only local database user accounts can be given the CONNECT THROUGH ENTERPRISE USERS option - and secure application roles can be used if either karen or ron require additional/different role access in the database. The specific steps involved in setting this up are as follows:

1) Create a proxy permission in the database

2) Assign users karen and ron to the proxy permission

3) Assign the database user acct_mgr to the proxy permission

4) Change the acct_mgr user to accept CONNECT THROUGH ENTERPRISE USERS

5) karen or ron would connect using CONNECT karen[acct_mgr]/pwd@database

If proxy authentication can be granted, it can also be revoked if necessary.


Using Client Identifiers with Proxy Authentication for Non Oracle or Enterprise Users

This method is used in situations where the end users are not known to the database as either database users or enterprise users. The middle tier passes a client identifier to the database rather than user name information. The client identifier can be anything from an IP address to an end user ID name and becomes part of the application context and can be accessed using the USERENV parameter.

The full sequence of authentication events is as follows:

1) The middle tier authenticates to the database and sets up a connection pool.

2) The end user authenticates to the middle tier

3) The middle tier uses an established connection from its connection pool and passes the client identifier information to the database using OCI or JDBC

4) The middle tier may also set the roles for the end user as necessary

Dictionary Views for Proxy Authentication

DBA_PROXIES All proxy connections

USER_PROXIES Connections the current user can proxy through

PROXY_USERS Users that can do proxy connections

V$SESSION_CONNECT_INFO Network connection information


Auditing Proxy Activities

Actions taken by proxy connections can be audited with standard database auditing commands such as the following:

AUDIT SELECT TABLE ON customers BY midtier ON BEHALF OF karen;

AUDIT SELECT TABLE ON customers by acct_mgr ON BEHALF OF all;

In either of these cases, the proxy information is included in the audit trail views.


Managing privileges and auditing through middle tier connections can be a challenging area of security for DBAs. With the proxy connection options that Oracle offers, database administrators do have some tools available to help them secure these connections, and audit and track the activities done by a proxy connection.

» See All Articles by Columnist Karen Reliford

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM