Re: Connect to Oracle from J2EE. Use Oracle connection pooling or Java

there are valid arguments on both ends. Given the size of your user population your position makes the best sense. The middle tier can be configured to support persistent or pooled connections. I believe there is also a mechanism to re-authenticate a user thru an existing pooled connection which doesn't have quite the same overhead but the username in v$session should represent the web user. Believe it is discussed in Oracle's 10 best practices paper on Oracle 9iAS.

On your end there are things you can do if you still start to lose this battle.

1. consider creating a packge of globals and require that the middle tier supply you with the user_id as a globals.g_user_id and their role as a globals.g_role_id. I'm assuming here that the business logic is in the middle tier and not back-end.
2. try and get the middle tier folks to store their user/role/priv checking in the database. they simply need to capture this info on the middle tier. Implement explicit priv checking in your stored procs/triggers using a role_priv model. They may be doing this but should be duplicated on the back end. What if someone got hold of the middle tier and connected as the pooled user? they can' t effect data if they don't know to set globals.g_user_id to an appropriate value.

also, the set_application_context may help in your scenrio. Believe it had been discussed by S.Feurenstein in the Oracle developer newsleter published by Pinnacle.

there are many arguments for/against:

3. no end user accounts provide better security (you can't hack if

    there are no user accounts).
4. pooled connections reduce the footprint of the database.

against
5. database level security can only be implemented against oracle

     user accounts.
6. views and other objects reliant upon a valid oracle user cannot be

    implemented. (well they can just not as easily...) 7. managing all privs in the middle tier reduces flexibility. The

    requisite role-priv-action mappings are what RDBMS's were designed     to handle.

revoke as many privs from the generic pooled user as possible.

In the app I'm working on we are migrating from a thick client dedicated interface to thin client. with 10,000+ users pooled connections made a great deal of sense. However, we had a LOT of code relying on the oracle level user account for privs. We've addressed all of this with a decent amount of effort so that the underling changes to the business logic (server side PL/SQL) are transparent. Transition for all users will likely take a year or two.

feel free to email me directly if you need to discuss further....

PS. An another annyoying subject I hope they are allowing for

     dynamically generated queries via NDS on the back end and not
     conditionally constructing statements in the middle tier before
     issuing the jdbc call. if not try and get them to do this and use
     an type object and collection based on the object to pass back the
     result set (i.e. SELECT * FROM TABLE (CAST ([function](arg 1, arg2)
     AS [object_name])). put the NDS sql in the function or appropriate
     proc called by the function. with 9i you can bulk collect using NDS
     into a complex collection.

the above works great!

-Gerry Bragg Brighton, MI gerry.bragg_at_altarum.org

--
Posted via dBforums
http://dbforums.com