Re: Application vs database users

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 31 May 2005 22:41:16 -0700
Message-ID: <1117604343.851493@yasure>

Mark Bole wrote:
> kochel_verz_at_yahoo.com wrote:
> > Hi.
> > Please give some opinion on this:
> > Wich is the best approach for managing users in a typical three-tier
> > web application, using jdbc:
>
> HansF wrote:
>

>> Some reasons to ensure that each app user has a unique database userid:
>>

> [...]
>

>>
>> Some reasons to avoid that:
>>

> [...]
>

>>

> DA Morgan wrote:
> > The method chosen is wholly dependent upon what the security needs of
> > the organization and the application are.
> >
> [...]
> > I would only deviate from this when circumstances so dictate.
> >
>
> To take advantage of JDBC connection pooling (my experience in this
> regard is with BEA Weblogic) would naturally lead to the choice of one
> or two database logins (connection pools) for the entire application,
> instead of individual database users for each application user, so I
> don't agree that security is the only issue to consider (I guess that
> falls under the "hedge" clause...).
>
> Also, on the flip side of security, creating new database users and
> resetting forgotten passwords requires DBA privilege, while
> adding/updating rows in an application-level table does not.
>
> As HansF mentioned, there are trade-offs that only your business needs
> can determine.
>
> -Mark Bole

I'm going to take issue with a few things you wrote. But yes they are nits.

If you don't have security, at least in the US, you are technically in violation of federal law if your company is public or meets other criteria such as Sarbanes-Oxley or HIPAA. So security is far more important than any other single criteria. It may run slowly but that isn't going to put you on the witness stand in federal court under oath. The rules have changed and DBAs need to adjust their thinking.
It is not true that DBA privileges are required to manage users. Yes the lazy DBA gets the DBA role: But it is a role and is irrelevant to creating or managing users. Grant any trusted person CREATE USER, DROP USER and maybe ALTER USER and that's all that is required. I could easily create a role such as the following:

CREATE ROLE maintain_users IDENTIFIED BY "N0Way!"; GRANT create session TO maintain_users; GRANT create user TO maintain_users; GRANT drop user TO maintain_users;

And the security issue is greatly minimized. Grant the role to a user who presents the the password via an application (thus they don't know it and can't log on with SQL*Plus) and the problem esentially is non-existant.

But we all agree there are trade-offs. And only the specific business case can dictate the correct path to take.

Reminds me a bit of what used to happen when someone asked me to create a table. First thing I did was CREATE TABLE .... These days I say what kind of table? For what purpose? How will it be used? What size? What percentage of the block will be filled during the initial insert? etc. The version 7 old DBA skills aren't all that relevant in the current environment.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Wed Jun 01 2005 - 00:41:16 CDT