Re: Entity Beans w/ User-Specific DB Connections

[Quoted] Hi David G. Young, thanks for writing this:
> Our team is building a classic three-tier J2EE system, but we're
> having a problem with needing user-specific database connections. Our
> customer requires us to use both J2EE, Oracle's Virtual Private
> Database (VPD) and Oracle audit trail. These Oracle-specific
> technologies require that the user associated with each database
> transaction be known by the database, either by a DB connection
> specific to the user, or by passing context information through a
> general purpose connection.
>
> The problem is that CMP uses a javax.sql.DataSource to get
> connections, which use the same database username/password for every
> access. Callback methods don't help either -- since ejbLoad gets
> called after the load takes place, there is no way to set the user
> context before the load happens.
>
> Has anybody found a solution to this problem?
>
> I would think it would be a very common desire to use database-level
> auditing in a J2EE system. I am trying to avoid having to write a
> huge amount of bug-prone custom persistence code in BMP EJBs or in
> DAOs.
>
> Any advice appreciated.
> David
>
>
> P.S. Other options I have considered seem to have the same problem.
> It appears all OR tools and technologies share this problem.
>
> JDO - Java Database Objects rely on a PersistenceManagerFactory, which
> must be set up with a single database username/password. Once this is
> set, it cannot be changed.
>
> Toplink - This Oracle tool can generate code with CMP EJBs, BMP EJBs,
> a JDO framework, or light Java classes. Each of the sub-solutions
> appears to use a database user-independent ServerSession object.
>

Oracle Magazine May/June 2003 had a (very) brief discussion of N-Tier Authentication (what Oracle terms the feature you're looking for). It's not very comprehensive, but might give you a start:

http://otn.oracle.com/oramag/oracle/03-may/o33trends.html

You might also find some help with Java Authentication and Authorization Service (JAAS), such as this article (URL will wrap):

http://otn.oracle.com/sample_code/tech/java/j2ee/javacookbook/JAAS/OverVie w.html

I am a DBA, not a developer, so I don't know if these will help. Forgive me if you already know all the stuff mentioned in these articles.

-- 
[:%s/Karsten Farrell/Oracle DBA/g]