Using Proxy Authentication Methods in Oracle Database 11g


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.

ALTER USER karen GRANT CONNECT THROUGH midtier;

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.

ALTER USER karen GRANT CONNECT THROUGH midtier AUTHENTICATION REQUIRED
PASSWORD;

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,

ALTER USER karen GRANT CONNECT THROUGH midtier AUTHENTICATED USING
DISTINGUISHED NAME;

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.

ALTER USER karen GRANT CONNECT THROUGH midtier AUTHENTICATED USING
CERTIFICATE TYPE 'X.509' VERSION '3';

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.

ALTER USER acct_mgr GRANT CONNECT THROUGH ENTERPRISE USERS;

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]/[email protected]

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

ALTER USER karen REVOKE CONNECT THROUGH midtier;

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

V$SESSION PROGRAM/MODULE shows proxy-user

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.

Summary

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

Karen Reliford
Karen Reliford
Karen Reliford is an IT professional who has been in the industry for over 25 years. Karen's experience ranges from programming, to database administration, to Information Systems Auditing, to consulting and now primarily to sharing her knowledge as an Oracle Certified Instructor in the Oracle University Partner Network. Karen currently works for TransAmerica Training Management, one of the foremost Oracle Authorized Education Centers (OAEC) in the Oracle University North America region. TransAmerica Training Management offers official Oracle and Peoplesoft Training in Coral Gables FL, Fayetteville AR, Albuquerque NM, Providence RI and San Juan PR. Karen has now been teaching Oracle for Oracle University for more than 15 years. Karen has attained her Certified Technical Trainer designation along with several Oracle certifications including OCP-DBA, OCP-Internet Developer, Oracle Expert - Oracle 10g RAC and Oracle Expert - Oracle Application Express (3.2). Additionally, Karen achieved her Oracle 10g Oracle Certified Master (OCM) in 2008. Karen was raised in Canada, and in November 2009 became a US Citizen. Karen resides in Columbus OH with her husband, Ron along with their 20 pets, affectionately referred to as the "Reliford Zoo".

Latest Articles