RE: Ids and passwords for application users

  I don't think administering usernames should be the DBAs responsibilty. There are better things to do than create/manage hundreds of users.

  But there are applications (for instance Siebel) which require you to create individual logins for each user. We have handled that by creating a package with creates/drops the users and also grants/revokes roles/privileges and creating a front end to it and letting the application administrator deal with it. Then we had applications which use one login for every user. Security is handled within the application. At least for one of them, we have been able to use triggers on the application base tables to create/drop the users and manage roles. Security is still handled within the application. The idea was not to drastically change the application. Instead of being logged in to the database and then validating the user against the application security tables, each user would first get authenticated by the database and then by the application. The only issue was we had to use database login triggers to switch the schema to the application schema. For web based applications which only use one login there is practically no way to identify a specific user when trying to troubleshoot issues. In this case I would suggest using dbms_application_info to set the username or something in v$session to identify that user.

Regards,
Denny

Quoting DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>:

> I think the real issue is who is going to keep the usernames and
> passwords
> maintained. Is it you? Do you have that much spare time? Also, it can
> also
> be inefficient with everyone's time. Someone gets hired. Their
> supervisor
> sends you an email so you can create the userid. But wait, they forgot
> to
> tell you which functions the employee needs access to. So you email
> the
> supervisor back and wait for a reply. Then you complete the user
> signup.
> Then it turns out the supervisor misspelled the employee's name (people
> are
> so picky), so you get to remove what you did and repeat it again. Don't
> even
> get me started on how they never tell you when someone leaves the
> organization. And of course, there is the ever-popular "I forgot my
> password" requests. My point is that creating and maintaining
> individual
> Oracle accounts can be very time-consuming and put you, the DBA, in
> the
> middle of a lot of application issues you really wouldn't need to be
> concerned about otherwise. For some systems you will need to create
> and
> maintain individual Oracle accounts. But my experience has been that if
> the
> application can maintain the security and populate a table with logins,
> this
> saves everyone a lot of time and trouble. Often the application security
> can
> be handed off to a non-I.S. person, or even distributed among several
> people. These people probably understand the user community and the
> application requirements much better than a DBA does. Sure, on those
> rare
> occasions when you need to find a user, it is an additional step, but if
> the
> system is running pretty well that doesn't occur that often. As for
> hiding/maintaining the underlying password(s), normally these will be
> buried
> in the application. And it sure is easier to change the password in
> one
> location and recompile the application. Anyway, these have been my
> experiences with the applications I've worked with.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
> -----Original Message-----
> Sent: Tuesday, July 30, 2002 7:53 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> > -----Original Message-----
> > From: groups_at_koovakattu.com [ mailto:groups_at_koovakattu.com
> <mailto:groups_at_koovakattu.com> ]
> >
> > If a common login is used (which is the case with most
> > applications),
> > dbms_application_info can be used to set the actual username
> > in either the
> > module or action. As long as the application is not using
> > dbms_application_info
> > to set both, you should be able to get the info from v$session.
>
> Sure, but I will repeat what I said before:
> a) It's easier to write code if the user is determined by the Oracle
> userid
> rather than by
> v$session.client_info. Trigger example:
> create trigger orders_set_user
> before insert or update on orders
> for each row
> begin
> :new.last_upd_user := user ;
> end orders_set_user ;
> /
>
> b) How do you plan on hiding the password from the user, or, more
> importantly, changing the
> password if it becomes compromised?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: groups_at_koovakattu.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).