OraTransplant

Using Proxy Authentication with SQLPlus

15 November 2006 at 11:30 CET | In Database, Features and tips, Oracle | 7 Comments

For our web application we’re using a nice Oracle database feature: Proxy Authentication. I only managed to use it in a J2EE application, not in sqlplus or other SQL tools (like TOAD). Today I discovered how to use Proxy Authentication from any tool you like.

First let me explain a little bit about Proxy Authentication. Our application server maintains a pool of database sessions, as most application servers do. This means that each session from this pool uses the same (fixed) database user to connect to the database. Looking in V$SESSION you see tons of sessions from the same user.

This makes it difficult to distinguish the sessions for a DBA. But more importantly you loose a lot of security functionality in the database:

SELECT USER FROM DUAL now always returns the same username. This means that views restricting the returned rows based on the currently logged on user no longer work as expected
Journaling (writing changes to journal tables from database triggers) loses its value since all changes appear to have been made by the same user
Auditing (insert_user/update_user columns in a table) loses its value since all changes appear to have been made by the same user
You can no longer easily use database roles to limit access. The user being used for the database connection needs all the roles of all the application users combined. And by default all these roles are enabled when creating a new database connection, unless you add specific code to enable/disable specific roles

The answer to all of this is Proxy Authentication. This process is quite simple. The application server first establishes a connection to the database with the same fixed account. This account is configured to only allow database connections (CREATE SESSION privilege) and nothing else. Once this session is established you can open a proxy connection to another user. This other user is the actual user logged on to the web application (e.g. JSMITH). This immediately gives you all the built-in security features of the database (grants, journaling, auditing, etc).

This is all great, but to open a proxy connection you have to issue an OCI or JDBC call. You can do this from programs you code yourself, like or ADF Faces applications. But for testing and debugging, you also want access to this feature from sqlplus or other SQL tools (like TOAD).

Fortunately this feature was introduced in version 10.2. When connecting, specify “user[proxyuser]” as username. This will try to log you in as “user” and then immediately open a proxy connection to “proxyuser”:

C:\>sqlplus bigappuser[enduser]/bigappuser@dev01


SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 15 10:18:27
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:

Personal Oracle Database 10g Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options
ENDUSER@dev01> select user from dual;
USER

—————————————

ENDUSER
ENDUSER@dev01> select sys_context(’USERENV’,'PROXY_USER’) from dual;
SYS_CONTEXT(’USERENV’,'PROXY_USER’)

—————————————

BIGAPPUSER

The same trick also works when logging in with other tools like TOAD. Be sure to use an Oracle Net client version 10.2. If you use 10.1 or any previous version this won’t work.

Update 19 dec 2006: Jonathan Lewis blogged about this very same feature of using Proxy Authentication with SQLPlus

7 Comments

Using LiveHTTPHeaders with Firefox 2.0

7 November 2006 at 12:03 CET | In Other | 11 Comments

LiveHTTPHeaders was one of my most often used plug ins for Mozilla Firefox 1.5. It allows you to trace each and every HTTP request being made by your browser and includes all the HTTP headers being sent and received. This includes things like redirects and cookies being set. The plug in proved extremely valuable in debugging our Single Sign On environment. This includes a number of web servers with a user being redirected through different servers during the login process.

Recently I switched from Firefox 1.5 to Firefox 2.0 and found out that the LiveHTTPHeaders plug in wouldn’t run on Firefox 2.0 Unfortunately the official page still doesn’t have a download that will install on Firefox 2.0. I decided to take a risk and hack the plug in myself to force it to install on Firefox 2.0. It proved to be very simple and it worked! I now have LiveHTTPHeaders running in my Mozilla Firefox 2.0.

If you also want to run LiveHTTPHeaders in Firefox 2.0 you can do one of two things; follow the hard way and make your own hacked plug in, or just download it from my server. Save the file to your local disk and drag-and-drop it from a Windows Explorer to a Firefox window.

If you want to do it the hard way, here are the steps to do it yourself:

Go to the LiveHTTPHeaders download page
Right click the “download it” link of the latest version and select “Save Link As…”
Save the livehttpheaders-0.12.xpi file on your disk
Rename livehttpheaders-0.12.xpi to livehttpheaders-0.12.zip
Unzip the file
Edit install.rdf that is in the unzipped folder
Replace
<em:maxVersion>1.5+</em:maxVersion>
by
<em:maxVersion>2.0.*</em:maxVersion>
ZIP all files again in livehttpheaders-0.12-firefox2.0.zip
Rename livehttpheaders-0.12-firefox2.0.zip to livehttpheaders-0.12-firefox2.0.xpi
Drag and drop the .xpi file from a Windows Explorer to a Firefox 2.0 window
The LiveHTTPHeaders Add On will be installed
After restarting Firefox 2.0 you have a working LiveHTPHeaders plug in!