Re: Generic user in web application and identification in database

From: bernard (bernard_at_bosvark.com) <bernard_at_bosvark.com>
Date: 11 Oct 2006 02:31:07 -0700
Message-ID: <1160559067.785364.194930@h48g2000cwc.googlegroups.com>

> But I need to inform which web user is doing the operations in
> database. At this moment, allways is the unique database user.

We have exaclty the same situation. Each user needs to be known (generic appserver user not acceptable) before they can perform any operations, which is needed for auditing. You have two options: (1) generic appserver connection to the database which sets an CLIENT_IDENTIFIER attribute of the built-in USERENV application context namespace for application users before each and every SQL operation or (2) a generic appserver user which manages a pool of connection to the database but only acts as a proxy user that allows authentication (the same as what Sybrand said).

To follow the line normally suggested by this group is: PLEASE READ Chapter 16 (Preserving User Identity in Multitiered Environments) of Oracle® Database Security Guide 10g Release 2 (10.2) B14266-01: http://download-east.oracle.com/docs/cd/B19306_01/network.102/b14266/apdvprxy.htm#DBSEG15000

It did take our Java developers a while to setup this type of connections in WebSphere but Ora App server should make this easy. See section 16.2.1.3 (Reauthenticating the User Through the Middle Tier to the Database)

The following link has some Java Examples of how they connect proxy with java
http://asktom.oracle.com/pls/ask/f?p=4950:8:5372384347108612280::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:13269994929803

A usefull query to indicate which users (client) are allowed to be connected through the proxy and how many connections (num_connections) are there of this type.
select a.proxy,

       a.client,
       count(b.username) num_connections
  from proxy_users a,
       v$session   b

where a.client = b.username(+)
group by a.proxy,

a.client

Regards
Bernard Received on Wed Oct 11 2006 - 04:31:07 CDT