Tom -- Thanks for the question regarding "authentication and connection pooling at a Java middle tier", version 9iAS

Submitted on 9-Jul-2001 10:35 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 13-May-2003 7:15

You Asked

Hi Tom -- and thank you for your site and help with my previous questions.

We use fine-grained access control in our database.  Our security is based on either the 
user or a role the user is assigned to, and the fine-grained access control works 
perfectly in both SQL*PLUS and our client-server apps--both of which log directly into 
the database so we know incontrovertibly who is using the database.

Now, I'm trying to develop a middle-tier app that does the same thing.  I know that I can 
do this with OCI and CONNECT THROUGH.  However, I don't know how to do it with a 
browser-based app calling JSP, JSERV, OSE or PSP.  If I make a connection with a static 
user, say USER_INTRANET, then my application context belongs to USER_INTRANET.  If I make 
a connection with a username/password entered from the browser, then I end up creating a 
connection for each individual user and don't get the benefits of connection pooling.

I apologize if you've already written to this question, but I can't find anything on 
doing this in JAVA.

Thank you,
Tom

and we said...

What I do with this is to NOT use the USER psuedo column directly but rather use an 
application context.  This is not java specific, this works with any middle tier solution 
that logs in using a common username.



I would create a package and context:


create or replace context my_ctx using my_pkg;

create or replace package my_pkg
as
   procedure set_ctx_val( p_name in varchar2, p_val in varchar2 );
end;
/

create or replace package body my_pkg
as

  procedure set_ctx_val( p_name in varchar2, p_val in varchar2 )
  as
  begin
     dbms_session.set_context( 'my_ctx', p_name, p_val );
  end;
end;
/

Now, where I would normally use the USER psuedo column -- I will use:


  nvl(sys_context( 'my_ctx', 'username' ),USER)


That is -- if the sys_context value of USER is set -- I use it, else I use the USER 
psuedo column.

Now, I can grant access on this pacakge MY_PKG to the finite set of "application schemas" 
that need to log in  -- your middle tier user for example.  Your middle tier must *know* 
who is talking to it (they have logged into your application in some fashion) so your 
middle tier can then call:

  begin my_pkg.set_ctx_val( 'username', ? ); end;

It is doing a "set user" call if you will.  Your fine grained access control will now see 
a value in sys_context( 'my_ctx', 'username' ) and will use that.  Since you control 
access to this package, allowing only priveleged users to execute it -- this is "safe" 
(as long as you trust that account, which you have to since everyone is logging in with 
it).

You should go one step further and make sure the predicate:

   if ( nvl(sys_context('my_ctx', 'username', user) = 'USER_INTRANET' ) then
        return '1=0';
   end;

is returned IF the user in the database is your middle tier user and the context has not 
been set.

Reviews

Excellent response July 9, 2001 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Tom Wurzbach from Florida

Thanks for the info.  This is exactly what I was looking for.

Excellent May 24, 2002 - 3am Central time zone

Bookmark | Bottom | Top

Reviewer: Alessandro Giannitelli from Italy

This is a good response.

opposite situation: identify individual from one APPUSER September 18, 2002 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: June from MD USA

while this was an good answer to original situation described, I have different scenario here:

we have production in place, where all users will access Oracle database via one application user
account: APPUSER. now we would like to allow different real user see different data based on some
criterial - should be a good exercise of FGA. all users information are stored in same database
under METADATA schema, while application data is under APPL schema accessed by APPUSER account.

once the user hit the production URL, persistent file will be created on web server in XML format
(using information in METADATA) instead of cookie, including real user_id, which potentially, could
be used to identify the real user for security policy implementation.

I was thinking to have another application user account such as USERA, USERB with different real
user associated, and assign security policy based on session_user which should be easy and
straightforward to implement in database.

however, since this has to change user registry on web server and may also have some other
technical issues involved, I have to explore the possibility to use application context.

As I described, we did have real user information stored in XML format. How can I extract
information from there and apply to my application context? any time user hit SUBMIT button, there
will be a call to Oracle database via written VB component. will this application context be
consistent during individual user's web browsing?

any suggestion/recommendation will be appreciated.

Followup September 18, 2002 - 2pm Central time zone:

an application context in 8i is specific to a session -- so as long as the VB component maintains a 
persistent connection to a single session -- yes.

In 9i, we added "global application contexts" which can migrate from session to session with a user 
and persist across sessions.  These might work better for you.

December 16, 2002 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Igor Racic from France

Hi, Tom

 I'm trying to see if it's possible to implement FGAC
on existing application on Oracle 8.1.7.

 In 2-tier mode, users log with their own account. It works with no problem.
 Using Tuxedo/Clarify on middle tier, Tuxedo holds certain number of connections which he use to 
execute queries of all users against Oracle.

 Correct me if I'm wrong:
Because Tuxedo doesn't reconnect for new session, we could not use FGAC - because connections are 
static. So returned predicate could not be reliable - caching problem.

 Thanks for your answer and for your site.

                                           Igor

Followup December 16, 2002 - 12pm Central time zone:

If tuxedo sent over the actual identity -- sure you could.  Eg: tuxedo calls a stored procedure 
that ONLY the tuxedo account is allowed to call.  This stored procedure is passed the identity of 
the user.  This stored procedure stuffs that identity in to the sys_context environment.  Your fgac 
drives off of that...

Why don't we do like this? December 17, 2002 - 12am Central time zone

Bookmark | Bottom | Top

Reviewer: Tony from India

I'm planning to do the following for my web application:

I create a centralized schema for my application. Then I create as many individual users for the 
application in the database itself. So, the end users will login from browser using their own 
username (schemaname) and password. For these end users, I'll write a pre-logon trigger to set 
current schema to the centralized application schema. If required, I'll create & assign roles for 
the users.
Is it a correct approach Tom?

Followup December 17, 2002 - 7am Central time zone:

why -- won't they just be running stored procedures?  why give them access to a schema at all.

what about FGA? December 19, 2002 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Joe from OH

Tom,

Doesn't FGA depend on the fact that you have real database users accessing the data?  The stuff I 
have read about proxy authentication in 9i and what is taught in the 9i Security course says you 
have to use OCI or thick jdbc to implement non - apps_user/application context solutions.  Does 
this mean web apps can't use built in FGA capabilities? The original poster referred to that type 
of solution with the "alter user bob grant connect through appsuser" syntax, but you directed 
him/her toward the application context solution.  Would you than roll your own FGA storing details 
somewhere based on attributes in USERENV namespace?

Also can you put the link back to the j2ee asktom equivalent site?

Followup December 19, 2002 - 1pm Central time zone:

thick jdbc = "i am a web application", virtually ALL thick jdbc apps are middle tier apps.


the question was asking about FGAC -- not FGA.  FGAC -- we just need the identity pushed down for 
us, and the policy function can make use of that.  using n-tier proxy authentication would be even 
BETTER.

curious December 19, 2002 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: Mike Yang from Jersey City, NJ USA

Tom,

Since an application context in 8i is specific to a session, why isn't it called "session context"? 
 I got confused when I first encountered this concept.  Just wonder.  Thanks.

against about caching January 20, 2003 - 8am Central time zone

Bookmark | Bottom | Top

Reviewer: Igor from France

Hi

 What makes confusion in all this story 
 is that returned pradicate 
 should be constant for a session.
 Is your approach to 
 avoid to put 
         if sys_context( 'abc', 'username' ) != 'ABC' then 
              return '1=0';
         else
              return '';

 but to do 
  return 'sys_context( ''abc'', ''username'' ) != ''ABC'' '

 Is that idea to avoid caching problem, or
 did I miss something ?

  Thanks for your clarification.
 
                                              Igor

Followup January 20, 2003 - 10am Central time zone:

It no long holds true.  that was true in 815/816 but in 817 -- if a context value is changes, the 
predicate policies will be re-evaluated during the next execute.

Thanks for your answer January 20, 2003 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Igor from France

Hi
 Is it documented somewhere that difference in 8.1.7 ?


                         Igor

Followup January 20, 2003 - 1pm Central time zone:

it was never documented that it worked the other way -- so there is no anti documentation, no.  
Just a change in the behaviour.

Connection pooling May 13, 2003 - 1am Central time zone

Bookmark | Bottom | Top

Reviewer: Tony

Hi,
  We have created separate user name and password for each users in the oracle database(8.1.7) for 
our JSP application, so there are hundreds of schemas accessing one central schema that has the 
data. I've some doubts here.

1) What I heared is that connection pooling is meaningful when we have only one user(schema) name 
and password used by all. Is it required to create connection pooling in the middle tier as, in my 
case, each user is given a separate user name?

2. As its known, in connection pooling, db connections are created and retained by the middle tier 
for reuse. Will the session id be same or different when the connections are reused? 
If the session is reused, what will be the impact on session specific global temporary tables being 
reused by different users with same sessionid?

Followup May 13, 2003 - 7am Central time zone:

1) not so, in Oracle we have ntier proxy authentication which fully supports connection pooling AND 
allows you to have separate authorization schemas in the database so you can still

o use grants instead of writing your own insecure security layer
o use auditing
o use fine grained access control
o and everything else...

http://tahiti.oracle.com/pls/db92/db92.drilldown?remark=&word=proxy+authentication&book=&preference=


2) depends on the implementation.  you should consider using "on commit delete rows" tables in 
order to avoid issues and making each "page" a transaction.  Don't use the sessionid as a unique 
id, use a sequence to generate one or have the middle tier assign a sessionid for you.

Write a Review