Web Dev practices - user authentication question

[Dmitri Zagidulin]

Web dev question for you all.

When you write your web apps and set up access lists (users and
groups), do you a) also set up corresponding users in the database
(and limit their db rights only to appropriate tables and operations),
or b) do you just have one master-type user that connects the web app
to the database, and manage user access rights solely within the app?

I've worked at places that have done both of those. In case of a), you
gain an extra layer of protection (even if the app code somehow falls
through, the logged in user can only access certain parts of the
database), but you also get more hassle (when designing the databases,
you have to think about who can see and do what, and write appropriate
GRANT statements).

I'm curious what you do (or think) on this matter.

~Dmitri

[Adam Bair]

Dimitri,

I manage access control through the application. I just don't care for mucking around that much at the database level. This may be a requirement for some applications -- but most of the apps I work on are pretty low key.

The database connections for my applications go through a single user.

Cheers,

[CLR]

FWIW, Rails is MVC in the application layer, so all access control is typically handled in the application in Rails apps.  I use to handle 'M' in the database, where it should be handled, but that runs contrary to most conventions that I am aware of in Rapid Application Design. 

Later!
-CLR

[eric draut]

I also follow the Rails convention of using a single database user to connect to the database. The only ways I know of for a nefarious type to break in this way are via poorly designed application access controls or via identity hijacking. There may be others, but those are the main ones I know of.

If you restrict access to resources at a low level, by checking privileges at the door, say in a before filter on a controller, then you should be safe from the application design problem. If your resources are well organized, you should find that most access controls will be simple: people with this role may access this controller's actions, others may not.

Other than that, if someone can either a) hijack a user's session to gain access or b) get their password, then that hacker can use the same techniques to gain access to a privileged user's session in the database-controlled access system as well.

I don't see any real benefits to a web application using multiple database accounts to safeguard data.

On Mon, Aug 24, 2009 at 11:54 AM, Dmitri Zagidulin <dzagi...@gmail.com> wrote:

[eric draut]

A convention I use that has proven extremely helpful and efficient: I create controller namespaces for roles, e.g.

Superuser::UsersController

Superuser::RatesController

Manager::ContentController

Manager::ProductController

Shopper::ProfileController

etc.

This way, each namespace can have a root controller class that gates access by role, simplifying the whole thing.

Of course, if you are using fine-grained, dynamic, per-item ACL's, then this won't get you all the way, but it will solve the role problem anyhow.

[CodeOfficer]

I only handle authentication in my apps at the application level. I
will typically combine resource level restrictions with model level
restrictions.

Here is some example code:

http://gist.github.com/174378

My controllers are rarely ever more complex than this. Note the 2nd
line of every action:

raise ResourceNotFound unless @listing.is_showable_by?(current_user)

And all primary models define the methods you see there:

def is_createable_by?(current_user)
def is_showable_by?(current_user)
def is_editable_by?(current_user)
def is_destroyable_by?(current_user)