Omnis Technical Note TNSQ0032 October 2013

Making a Proxy Connection using the Oracle DAM

for Omnis Studio 4.3.2 and later
by Gary Ashford

Introduction
This article describes how to connect to an Oracle database "by proxy". A proxy user is a user that is allowed to "connect on behalf of another user".

This is useful in the context of an Omnis application server which provides an Oracle session pool. The session pool is created using a single user name, for example; base_user.
Typically base_user will have only the CONNECT database privilege, meaning that unless a proxy connection is made- the base user will not be able to effect changes to the database.

A user account with resource privileges on the database is then used to "grant connect through" to the base user. A session taken from the pool can then request a proxy connection. Upon authentication, the base user effectively becomes the resource user- taking on that user's name, access rights and privileges for the duration of the proxy connection. When the user elects to end the proxy connection, the privileges revert back to those of the base user. The client may also proxy to a different user, in which case the current proxy ends and the new one takes its place.

The advantages of connection by proxy are:

  • Security- the base user does not need to know the resource user's password.
  • Audit control- the database knows who is using it. Every connection has its own user name and the user's identity is preserved right across the connection from the DAM and the network layer to the database.
  • Administration- grants and privileges are applied automatically. There is no need to re-apply security privileges to individual users.

Making a Proxy Connection

Using the Oracle command line utility or the Enterprise Manager, create a base user account for use with the session pool.

SQL> connect sys/passwd@oradbms as sysdba
SQL> create user base_user identified by base_passwd;
SQL> grant connect to base_user;

Grant connect through privilege to the base user from a resource user account.

SQL> alter user scott grant connect through base_user;

From within Omnis Studio, it is then possible make a proxy connection using the Oracle DAM session object $proxyas() method. For example:

Do sessObj.$logon('orainst','base_user','base_passwd') Returns #F
Do sessObj.$newstatement() Returns cStatObj
Do statObj.$execdirect('select user from dual') Returns #F
Do statObj.$fetchinto(lUser)     ;; returns "BASE_USER"
...
Do sessObj.$proxyas('scott') Returns #F
Do statObj.$execdirect('select user from dual') Returns #F
Do statObj.$fetchinto(lUser)     ;; returns "SCOTT"

...
Do sessObj.$endproxy()

If using a session pool:

Do $extobjects.ORACLE8DAM.$objects.ORACLE8SESS.$makepool('poolone',10,'orainst','base_user','base_pwd') Returns lPoolRef
...
Calculate sessObj as $sessionpools.poolone.$new()
Do sessObj.$proxyas('scott') Returns #F
Do statObj.$execdirect('select user from dual') Returns #F
Do statObj.$fetchinto(lUser)     ;; returns "SCOTT"

The $proxyas() method also accepts a password parameter if required during proxy authentication. The password requirement is stipulated from within the ALTER USER command when granting proxy connection through to the base user, i.e.

SQL> alter user scott grant connect through base_user authenticated using password;

The $proxyas() method also accepts a third parameter if required. This is a single-column list containing additional roles or privileges that should be assigned to the proxy connection. Note however that if the resource is not allowed to act on behalf of the client or if the resource is not allowed to activate the specified roles, the proxy request will fail.

Links and References
 
asktom.oracle.com