Re: .NET, connection pooling and security .

On 2003.11.29 22:49, Steve Perry wrote:
> I hope somebody on the list can help me out with this.
>
> All of our 3-tier apps are architected with a schema owner (owns all objects
> used by an application) and application user (no create privs, but it does
> have full dml privs to the schema owner objects).
> On the web side, connection pooling is setup with 10 connections logged in
> (all as the application user).
> When users connect, the application reads some active directory keys that
> tell if the user is a reader, dml user or admin user (all privs).
>
> I don't feel the application should be managing security and I'd like to
> take that responsibility away.
> The 10 identical connections logged into the database bothers me too.
>
> I'd like to make it work similar to our 2-tier apps where we use roles,
> assign them to a user and they connect individually. We don't have OID setup
> and I imagine that would solve this. Short of that, is there any other way
> to work around having the 10 identical connections logging in and having the
> application maintaining security? Is there another way of assigning the
> security?
>
> I don't have any web development experience and I thought I'd check here
> first to see how others deal with this. I hope somebody else has worked
> this out at their shop.
>
> I'm not sure if the answers will change, but it's an all M$ shop, except for
> Oracle.
>
> Any help would be appreciated.
> Steve
>

Steve, I am not a .NOT user or admirer but I think that all security should be in one place because then it is non-conflicting and more easily controlled. If the business decision is made that this place is LDAP, then you don't have much choice.
For the sake of the DBA staff, you can adopt a standard mandating that every application should call DBMS_APPLICATION_INFO.SET_CLIENT_INFO immediately after it connects to the database. Client info information is visible from V$SESSION so you can use alternative means of determining sid and serial#. What does seem as an objectionable practice is granting "admin" authority through LDAP. Only DBA should have DBA role and nobody else. Hopefully, this "admin" role granted through the active directory does not mean "DBA", but only "application admin". Application admins are helpful people who know the application and administer certain parts of it. They can take the burden of mundane tasks like granting & revoking roles as well as creating users away from the DBA and have him working on more important tasks like helping developers, documenting best practices, planning disaster recovery, setting standards, planning upgrades and tuning buffer cache hit ratio. In other words, everything seems to be hunky dory except the posibiliity that the DBA role is granted away lightheartedy. You are a DBA and as a DBA, you took the oath of enforcing the first DBA commandment which reads:
Thou shalt not have other DBAs but me.

No ifs, no buts, no active directories here.

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

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