Asp.net SQL Server user impersonation

Question

I wonder if somebody could point me in the right direction. We're currently looking to update an ASP.NET application that uses SQL Server 2005 for its db. We need to provide robust and consistent functionality that prevents certain users creating, updating, deleting certain records from the database.

Unfortunately, the application isn't written using OO concepts, instead favouring many data access stored procedures called directly from the web page code behind files. Due to the sheer amount of stored procedures we'd prefer to investigate database triggers to implement our security.

The question is; is it possible for the db trigger to be aware of the specific application user (managed using custom user account tables in the db) which executed the sp? All we'd need is an id. The connection string used to connect to the db is identical for all users (taken from the web.config), is there a way for the connection to some how either impersonate a user, or set a specific variable accessible to the trigger?

Remus Rusanu · Accepted Answer · 2012-10-22 21:01:27Z

1

If the application uses a SqlConnection to 'login' the user and then performs the data access/updates using the very same SqlConnection then you can use CONTEXT_INFO(). Set the context in the 'login' procedure, then check it in the data access/update procedure(s). See Using Session Context Information.

But if the application uses a SqlConnection to 'login', caches the result (perhaps in a cookie or in the ASP session state), and then uses a different SqlConnection to access data then there is no way access the needed login information w/o application support. If you can locate all the places where SqlConnection objects are created and refactor it to use the factory pattern then you can augment each connection open with a call to set the context info before returning it to the caller for use and that would also work, with minimal application changes.

answered Oct 22, 2012 at 21:01

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DavidReid Over a year ago

Further query: how does this work with connection pooling? For example, is there a risk that somebody else could pick out a connection that already has the context set?

Remus Rusanu Over a year ago

Connection pooling will reset the context_info

Collectives™ on Stack Overflow

Asp.net SQL Server user impersonation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related