RE: N-tier data access and security

Tracy

   We are wrestling with these issues. I certainly can't claim to be an expert, although I certainly hope you receive some replies, but I can share what I have learned in this area so far. I'm going to rephrase your questions slightly so you can verify whether I've understood you.

1. Your second question first. I agree with Robert that you should consider Oracle's Fine Grain Access Control and Application Context. This may mean that you have separate usernames and maybe even separate schemas. We are wrestling with that currently. The developers feel one big schema suits them, but I'm wondering how we will be able to locate users. Given geographic dispersal, giving each user their own username would be very cumbersome. And by the way, Oracle9i has a way to get away from embedding the password in the application code, at least the password for the role.
2. Should my SQL statements be in separate Java classes or scattered throughout my Java? There are advantages and disadvantages to both: Advantages: Fits the O-O model.
   • Access style will be more uniform.
   • You will probably have a person or a small team that creates the data access classes. Not all developers will need to be proficient in SQL.
   • If you have other systems besides Oracle, like legacy systems, then the data interfaces can shield the developers from the complexity of accessing those systems.

  Disadvantages: You will not get the full power of the database. The O-O model is distinctly different from the relational model. The tendency is to modularize data access so that, for example, joins are performed in the database itself.

• Individual developers won't need to learn SQL, but they will need to learn the data interfaces that are built for them. Depending on your development organization, they will need to request additional classes from the database interface group. Or rather than wait, they may simply use the interfaces that are currently available to them, which means they do the join in Java.
• Another issue is referential integrity. Essentially the O-O model doesn't consider RI, but we database people don't like to see it left out for many reasons. But by restricting the access to the O-O interfaces, RI may be either ignored or at least slighted.

Whether you go as far as Robert is suggesting and move the business logic itself into Oracle may depend to some extent on your development organization. If your organization has pried the developers away from Microsoft and they are irritated about not getting to learn .NET, then suggesting PL/SQL may sound akin to proprietary. I haven't seen very much interest in Java in Oracle recently even from Oracle.

    As to batch, my feeling is that there will be situations where performance is a key issue that only PL/SQL will satisfy. However, again it depends on how much commitment to PL/SQL your organization is willing to make. Having a batch program run from the application server shouldn't be too bad because the app server should have good bandwidth to Oracle.

I have been searching for some good information on this subject, but have found very little.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Monday, November 04, 2002 9:09 AM
To: Multiple recipients of list ORACLE-L

We are heading down the N-tier path, (browser, websphere app server, oracle database). What is the best method to setup this architecture for security and
the logic?

1. Should the business logic be physically separate from the data access on the app server? And if yes, where should the data access component reside? (database or app server) How does batch affect the decision? What variables should we be considering when making this decision?
2. How should the database connection be established from the app server to the database? Should it use a generic account on the app server with the password encrypted in a file?

In addition, does anybody have any good white papers or urls? Thanks

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tracy Rahmlow
  INET: tracy.rahmlow_at_aexp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).