Using Proxy Authentication with SQLPlus

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

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

TrackBack URI

  1. Hi,

    Our application is using Oracle Database and one of its module is using MicroSoft Visual Studio Runtime Macros Vsmsvr.exe to be precise and its behaviour is strange.

    Server Client Result
    ====== ====== ======
    8.1.7. 8.1.7. Works
    10.2.0 8.1.7. Works
    10.2.0 10.2.0. Error gives Ora-07445*

    * Details:-

    ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_opiosq0+86] [PC:0×22AB20A] [ADDR:0×4A] [UNABLE_TO_READ] []

    (internally it shows ORA-00022 Invalid Session in the trace files)

    I have applied PatchSet 9 Supplied by oracle.

    Implemented work around suggested by metalink:

    INBOUND_CONNECT_TIMEOUT_LISTENER = 0
    direct_handoff_ttc_listener=off

    Nothing helps.

    All most no help from anywhere.

    Any help on this would be appreciated.

    Thanks & Regards,
    Kartik

    Comment by Kartik Shingala — 22 November 2006 #

  2. ORA-00600 and ORA-07445 are the internal error codes of Oracle. It means something happened internally that was unexpected. Most of the times it means your hitting a bug.
    However, the chance your the first one to encounter this bug is very slim. So, you’re best of contacting Oracle support at http://metalink.oracle.com and have them find out what the problem is.

    Comment by Wilfred — 23 November 2006 #

  3. Hi Again,

    I am sorry to trouble a lot.

    I am facing this problem. At any point of time our application hangs from all the workstation at that particular point. I mean no one can do that workflow and everyone stops at that same point.
    There are no locks on database then. And the wait even for session is :

    cursor: pin s wait on x

    Strange thing is that then database shows too many active sessions(almost 15 - 20) constantly even when some of those sessions are disconnected from db 5 ten days back and if i kill the oldest active session amongst them everything starts working fine.

    No of active sessions also gets back to normal.

    There is almost no help available anywhere. And i am facing this problem on Windows Platform server and DB version is 10G R2.

    Any comments on this would be a great help.

    Thanks & Regards,
    Kartik

    Comment by Kartik Shingala — 29 November 2006 #

  4. Very informative article indeed though a little complexed in nature :)

    Do we have Proxy authentication mechanism for all kind of Application servers ?

    Comment by Coders2020 — 15 March 2007 #

  5. Thanks for showinh SQL+ example of Proxy User connection.

    If you have any Java connection example ?

    Thanks.

    Comment by Adarsh — 20 June 2007 #

  6. Adarsh,

    Have a look at OTN article about the subject. In general you can search for Proxy Authentication on OTN and get a number of interesting articles.

    Comment by wilfred — 20 June 2007 #

  7. You can read about most of the presentations on any of the other Oracle blogs;

    Comment by bilginin adresi bilginin tek kaynağı — 18 August 2008 #

Trackback/Pingbacks:

  1. Oracle » Blog Archives » sqlplus commands

    [...] Using Proxy Authentication with SQLPlus 15 November 2006 at 11:30 CET | In Oracle , Database , Features and tips | For our web application we re using a nice Oracle database feature: Read This [...]

  2. Oracle » Blog Archives » Autotrace in SQLPLUS. Here is what I like

    [...] OraTransplant ” Using Proxy Authentication with SQLPlus I only managed to use it in a J2EE application, not in sqlplus or other SQL tools (like TOAD) … Oracle ” Blog Archives ” sqlplus commands … [...]

  3. Oracle » Blog Archives » … for dev-db/oracle-instantclient-sqlplus # Copyright 2000-2007 Gentoo Foundation;

    [...] OraTransplant ” Using Proxy Authentication with SQLPlus I only managed to use it in a J2EE application, not in sqlplus or other SQL tools (like TOAD) … Oracle ” Blog Archives ” sqlplus commands … [...]

Sorry, the comment form is closed at this time.

Powered by WordPress with Pool theme design by Borja Fernandez.
Entries and comments feeds.