Hi all.

I have a security issue which I could do with some advice on.  To date the
solution to this issue has been to create a single static user on with admin
rights to the entire server, but I don't like that there is a user with that
much privilage.

Here's the details of the problem.

We have a web product which use ASP.Net and C#.net running on an IIS server
(on a closed network) this product is broken down into three modules. Module
1 needs access to execute stored procedures on a single know database,
Called Module1DB. Module 2 need access to create and execute stored
procedures on one database Called Module2DB, it also needs access to perform
any select on Module1DB.  Module3 need access to execute stored procedures
only on Module3DB.

Currently the system is not in production, but will be shipping very soon.
As per usual the security has been left to the last minute.  The system uses
connection pooling to help with performance, and this is the main area of
concern.

The solution that I'm thinking about goes like this...

First I will create two different users for the system to use, I'll call
this User1.  This user will have enough privilage to Use Module1DB and
Module3DB.  Other than this it will have no access rights at all.

User2 will have considerably more privilage.  This will be dbo rights to
Module2DB, and select rights only to Module1DB (it does not need access to
Module3DB.
Module2 will connect to SQL using User2.  At this point it will have access
to everything which it will need to complete all the tasks that it is
required to perform.

I will create three Application Roles.
Role1 will have rights to execute any stored procedure on Module1DB.
Module1 will call sp_setapprole at the beginning of the session to allow the
program only the privilage which it needs, and nothing more.
Role2 will have rights to execute any stored procedure on Module3DB.
Module3 will call sp_setapprole at the beginning of the session.

The idea of this is that Modules 1 & 3 have extremely limited rights.
Module2 needs more rights as it needs to generate reports. Module2 generates
stored procedures which are used to generate the reports.  Users can create
the stored procedures using the program front end (although they do not ever
see the code) using a user friendly report generator.

I know that this is a complex solution, but then nothing's ever easy.   The
question which I have is...

How is the connection pooling affected by having this setup?

Regards

Colin Dawson
www.cjdawson.com

Colin Dawson (newsgro***@cjdawson.com) writes:
Badly. Connection pooling and application roles usually don't go well
together. If you use application roles, you cannot use conneciton pooling.
When you reuse a connection from the pool, you get an error message.
The message I got when I tested was very insincere and not very helpful,
the generic "General network error".

In SQL 2005, there is an option to back out of an application role,
but you need to do so explicitly before you disconnect.

Show quote Hide quote Thanks Erland.

That's exactly the kind of advice that I needed to know.  I think that based
on your advice the best solution is a single user with a rolling password.
This user will have dbo access to Module2DB, and be a member of static roles
in Module1DB and Module3DB. These static roles will have access to execute
any stored procedure, but nothing more.    It's a little looser than the
previous scenario, but alot simpler to implement, and should cover
everyone's requirements.

Regards

Colin Dawson
www.cjdawson.com

Triggers - run for multiple updated
parsing varchar fields
INSTEAD OF INSERT trigger
Why does update run differently standalone vs in procedure?
SELECT different columns based on the value of another column