Re: Novice question about users and...rights?

Laurenz,

* Laurenz Albe (laurenz.albe@cybertec.at) wrote:
> Dale Schmitz wrote:
> > My big question for this post regards database login by end users.
> > In the Access database, users logged in as a way of verifying to the database
> > super user that they were legitimate users of the database,
> > but everything ran as the DB owner (the super user I mentioned).
> > In converting this application I’m tempted to go the same route,
> > but only because I saw that it worked in Access.
> > The users will have flags in their records stating their role,
> > and hence what they can and can’t do, but I wonder if this is smart.
>
> The best setup is probably like this:

I wouldn't suggest this as being the 'best' setup- that really depends
on the user, the applications, environment, et al.

> Don't have a database user for each application user, but use
> one database user for the application to connect to the database.

This makes the application have to handle all of the authentication and
authorization for the user, which certainly requires not only more code
in the application but may also be more complex.

Of course, if the authorization requirements aren't able to be expressed
through SQL GRANT and POLICY commands, or VIEWs, TRIGGERs, et al, then
one might have to implement the authorization in the application.  One
might wish to consider if such a complicated authorization setup is a
good approach though since that's one of those things that can be tricky
to get correct in all of the various corner cases.

> That database user ideally should not own the tables, but only have
> the permissions necessary to run the application.

I agree that, generally speaking, you don't want your application user,
or end users, to be able to create tables unless you've specifically
thought about that and considered if it's a good thing or not.  If you
decide to allow them to create tables (which can be quite handy in some
cases), I'd suggest you consider user schemas to keep the users
segregated from each other, and be sure to REVOKE the default CREATE
rights on the public schema from PUBLIC.

> That reduces the damage that can be done if an attacker breaks into
> your application with SQL injection or otherwise.

This is further reduced if the application doesn't handle the
authentication or authorization but instead the database does.

> Manage your application users in a database table.

The database can handle this for you, and users would end up in the
system catalog tables.

> If you have different kinds of application users with different sets
> of permissions (e.g. some that may only read, but not modify the data)
> you could use more than one application user with the appropriate
> permissions and reconnect as that user after you have authenticated the
> application user.

This is handled much more cleanly by having end user accounts in the
database itself and provides much more flexibility than having to create
an application-level role for every combination of privileges and then
making sure the application uses the right role at the right time.

Thanks!

Stephen