Re: Basic logon architecture for multiple apps in a db

Yosi
 The users at our location do both methods of logons. Some access the database directly with "create session" privileges and have a role granted to them that can access the data. Other applications have the user login from the application access the database and the table privileges are granted to the application id. The user assessing the database was inplace before I started working here. I control the tables the users have assess to by using roles on all of the new applications if the developer does not code it to have an application id hitting the database. Both methods work well and I am still able to "see" the originating user's machine name that they logged onto the client with. That helps in tracking down who is accessing the servers.
ROR mª¿ªm

>>> yosi_at_comhill.com 04/11/01 04:40PM >>>
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).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

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).