Basic logon architecture for multiple apps in a db

O Esteemed and Wise Colleagues,

(My first sending of this didn't seem to make it to the list... Knowing
our mail server it may show up in a few weeks!)

How do application (Forms or other) users access your tables? Do they logon as themselves? Do you switch their logon behind their backs to that of the app owner (like Oracle Apps does?)

I'm wrestling with this now.

The way I see it, I've got two choices, with several subchoices:

1. User logs in as self and accesses the tables either:
2. via synonyms (to tables or to table API package), or
3. via full table path qualification, i.e., GL.ACCOUNT or GL.ACCOUNT_API (package).
4. User logs in (knowingly or unknowingly via behind the scenes smoke-and-mirrors) as app owner, and accesses tables directly.

Peronally, I much prefer the logging in as self route. It's easier to trace users, sessions, security, access, performance, etc. I also prefer using synonyms, since most application design environments - including Forms - don't fully qualify tables or views by default.

The problem is that synonym names can conflict between applications. One solution is to prefix the app_short_name to the name of each table or view. I hate that. Another thought is to create synonyms dynamically as the user logs on to an application. That's no good if the user logs on to two apps at the same time.

If you go with relogging in as the app owner, you somehow have to keep track of who the user really is (some common package variable, most likely) and then use that info as needed. That sounds like lots of extra code.

So, how do YOUR users access your apps? Any ideas? I need guidance, and I'll really, truly, honestly, very much appreciate any you can send my way.

TIA, Yosi

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yosi Greenfield
  INET: yosi_at_comhill.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).