Do You Give Users Access To Your Database Or Are Queries Executed By A Shared Username

Question

I am building a C# app which will need to talk to a database - I have written a module which checks whether users are permissioned appropriately and then uses a shared username to run the query

This means that permissioning is done in C#.

I am now wondering whether this is a good idea and whether permissions should be applied a the database level ie each user set up with their own user name and account. The reason for NOT doing this is that we are not DBAs and getting users added

What are the benefits and problems of permissioning users at the database level rather then c# level?

Answer 1

I always use a database login/user set up for my application.

By doing this i can operate reasonably safely within a database that may be shared with other applications - my application can be granted just the permissions it needs on the objects it needs to access.

This decouples the users from the database - it means that user Jane Doe doesn't need to be given specific rights on the database to use my application, which DBAs love as because of that it also means she can't just start up SQL Management Studio and start looking through tables (because she has no rights).

Edit: note that it is not necessary to give each user a login in order to audit who made changes - my app can still be logged in as one (restricted) user and i can pass the machine/user name of the user in to whatever stored proc i am calling so that data changes can be logged.

You can certainly have a group set up on your database and allocate the correct permissions to this group, but that means you then have to add/remove users from that group as necessary, which can be an administrative annoyance.

Answer 2

How important is the data in your database? Is auditability (logging who changed the data, what they changed and when) important?

If all your C# program does is read the data and the data is public anyway, then what you are doing is fine. This is exactly what happens on a website where users do not need to login, all queries are run from the same user.

For a high security, mission critical application rolling your own security in the code is seldom a good idea, much better to utilize the security system built into the operating system.

Are your computers part of a domain and do you use Active Directory? If so, you might want to consider using Windows authentication and groups.