Opinions about authentication between application

Question

I'm puzzling over a technical dilemma where two folks on our team a recommending two different security models each with pros and cons.

The greenfield looks like this: We have a an asp.net web app, talking to a business layer, talking to a database.

*One of the requirements is to be able to have higher level users delegate business layer rights to other users.

One of the folks is lobbying for the capability of an internet user to pass their credentials all the way down into the database so the connection can use an actual sqlserver account for querying, etc. (Some aspects of this I like - auditing capabilities for instance)

The alternate approach on hand is to simply go with suite of users,passwords,roles,resources tables in the database, and manage the security up in the business layer.

It could be because I come from a java to oracle background where in most cases you use a connection pool that provides connections which were already authenticated using a service type account. Our internet clientele never had actual database accounts.

Am I flawed in my thinking that managing delegatable security (by internet users) inside the builtin, internal credentials store that mssql server provides seems fraught with peril security wise?

Any one have any recommendations?

Answer 1

How may potential users can you have and how may of those users could be active at once?

For example, if you have 100,000 users, and thousands can be online at once time, then you will need 1000's of database connections open to serve them all as each user can only use their own connection. Setting up and tearing down a connection for each transaction is very expensive and will make the application slow.

Personally I would go for a connection pool, and would not have a database user account per internet user. That is how web applications are generally built.

Something like Oracle Fine Grained Access Control may give you a middle ground of security, whereby you set the 'internet user' in the session and then the database ensures that internet user can only access what it is allowed to based on rules in the database.

Answer 2

In most web applications, you the security model is defined at the business logic layer, not the data layer.

For instance, my ability to edit a post on Stack Overflow is not controlled by my ability to read/write to the "posts" table - in fact, you could probably not even design a database schema that would allow you to implement database-level security at this level. Instead, there's a business logic layer which compares my privileges with the action I'm trying to take (I assume); security is implemented at the business logic layer.

I frankly see almost no benefit to passing through credentials to the database layer - if somehow I'd bypassed the business logic for controlling who can edit SO posts, the database "read/write" controls wouldn't prevent it, and auditing wouldn't really help you.

I see LOTS of drawbacks - not least the fact you'll be splitting your authorization logic into two (business logic and database), and introduce all kinds of entertaining failure modes with synchronizing accounts across your business logic layer and database layer (users changing their password, or leaving the web site). I can't begin to imagine how you'd sanely test and debug all this - what happens if an end user gets an error related to their database privileges?