Middle tier and real user auditabilty in the database

We currently have a requirement to build a 3 tier application using an application server (Oracle 8i DB, EJB, java GUI front end) for approx = 1000
users. There is a requirement to 'segregate' the data for different = types
of users=20
which I think we can achieve by using Oracle's Virtual Private = database.
There is
also a requirement to audit which users are accessing the DB etc. = Because
of the large number of users I have been looking at using an LDAP = server and
schemaless users. If we were to use Oracle's Internet Directory Server = the
Oracle 8i database is able to authenticate any users against the LDAP server and also get information regarding the user's privileges etc.=20

What I am not sure of is how the application server will pass the real username through to the database? Apparently Oracle have some OCI calls (ref - Oracle Call Interface Programmer's Guide Chap 8) that = allow=20
this to be done, I can see how this might work with a TP monitor like TUXEDO but how would these fit into the EJB world?=20

Any documentation and particularly a somebody to which I could make a=20 phone call would be really useful.

Thanks Tim Jones

tim.jones_at_compaq.com

++64 3 371 5773

Below are some extracts from the Oracle documentation

------------------------------------------------------------------------=
--

ref - Oracle8i Administrator's Guide Release 2 (8.1.6)

Auditing in a Multi-Tier Environment

In a multi-tier environment, Oracle preserves the identity of the = client
through=20
all tiers. This enables auditing of actions taken on behalf of the = client.
To do so, you use the BY proxy clause in your AUDIT statement.=20

This clause allows you a few options. You can:=20

     Audit SQL statements issued by the specified proxy on its own = behalf.=20

     Audit statements executed on behalf of a specified user or users.=20

     Audit all statements executed on behalf of any user.=20

The following example audits SELECT TABLE statements issued on behalf = of
client=20
JACKSON by the proxy application server APPSERVE.=20

AUDIT SELECT TABLE
    BY appserve ON BEHALF OF jackson;

------------------------------------------------------------------------=
--

ref - Database Security in Oracle8i, An Oracle Technical White Paper November 1999

pg 4
Authentication Through a Middle Tier
In applications which use a heavy middle tier, such as a transaction processing monitor,=20
it is important to be able to preserve the identity of the client = connecting
to the middle=20
tier. Yet, one advantage of a middle tier is connection pooling, to = allow
multiple users=20
to access a data server without each of them needing a separate = connection.
In such=20
environments, you need to be able to set up (and break down) = connections
very quickly,=20
without the overhead of establishing a separate, authenticated database session for each=20
connection. For these environments, Oracle8i offers n-tier = authentication,
lightweight
session creation via the Oracle =AE Call Interface, so that = applications can
have multiple=20
user sessions within a single database session. These lightweight = sessions
allow each=20
user to be authenticated by a database password, without the overhead = of a
separate=20
database connection, as well as preserving the identity of the real = user
through the middle=20
tier. See Authentication through a Middle Tier, described later in this paper.

pg 17

Auditing For Three-Tier Applications
Many three-tier applications authenticate users to the middle tier, = then the
TP monitor=20
or application server connects as super-privileged user, and does all activity on behalf=20
of all users. With Oracle8i, you are not only able to preserve the = identity
of the real=20
client over the middle tier and enforce least privilege through a = middle
tier, but you=20
can audit actions taken on behalf of the user by the middle tier. = Oracle8i's
audit records=20
capture both the logged-in user (e.g., the TP monitor) who initiated = the
connection, and=20
the user on whose behalf an action is taken. For example, to capture = all
SELECTs on
the BONUS table done by a middle tier called 'appsrv,' you would = enabled the
following=20
audit option:
AUDIT SELECT ON bonus BY appsrv ON BEHALF OF ANY; Audit records capture both the user taking the action and the user on = whose
behalf the=20
action was taken. Auditing user activity, whether users are connected through a middle=20
tier or directly to the data server, enhances user accountability, and = thus
the overall=20
security of multi-tier systems.

------------------------------------------------------------------------=

Chris Borrill=20
Analyst/Programmer Received on Thu Jan 18 2001 - 15:01:17 CST