What is the Best way for database desktop application permission managing ?

Question

Many desktop applications use SQL tables to manage their users permissions and roles. In fact they restrict access to some parts of application in their application code. It means they need a constant connection string to SQL server with maximum permissions.

My target is C# 2010, Sql Server 2005 or 2008.

What if with any reason someone find that connection string ?(Network tracing, Software hacking, Fired Employee or ...), He can change everything with just Managemenst Studio and you can't understand which client did it. If you want to change connectionstring. You must do it in many clients and in a bad design you need to recompile application.

So i want to know is it good idea to use real SQL login instead of username and password in tables ? make connection string for each user by SQL Logins ?

In this way there is no afraid to loose connection string. Also in SQL codes you can use GetUser() function to indicate which user really runed the query ?

But maybe the managing permission is difficult with this way because there is no simple table to manage permissions and users.

In one long sentence. I want to know What is the best way for authenticating desktop application users in a way that managing permission can be done and also sql server can log user activity ?

Answer 1

Whenever possible, use Windows Authentication.

Define Roles. Add Groups to Roles. Add Users to Groups (as per usual Windows way of managing file system permissions).

Choosing an Authentication Mode

See: Application Roles

Answer 2

You can encrypt sensitive sections of your app.config file. Last time I checked, you have to go through some hoops, but it's not that difficult to set up.

See for example this question

Answer 3

For best security, you need one dedicated database account per user; each account should only have the privilegeds necessary for the tasks that the corresponding user may perform. Normally you grant privileges to roles (e.g. "clerk", "manager", "supervisor") and grant those roles to the users. Obviously, it requires some work to identify which privileges each role needs. In many cases, it might be necessary to not allow direct access to some tables, but only to database views that hide some parts that should not be visible to that role, or to stored procedures that do some additional checks and make sure that each transaction is processed as a whole.