0

In a desktop application, it is not good to store connection information in a file. I want to authenticate a user by using a guest user in sqlserver. This guest user can check login via a stored procedure or function in sqlserver. This function takes two parameters user id and password and returns true or false. After login to the system, he gets connection information and has user rights.

Also this guest user should not have a right to read login table. Because if he reads this table, so he can get admin or others' password.

How can i achieve this system ? Thanks.

engtuncay
  • 867
  • 10
  • 29
  • Since you would need a connection string anyway to connect to a database to retrieve a connection string, this seems like a bit of a waste of time. The best bet is probably to [encrypt the connection string in your app.config file](http://stackoverflow.com/q/4975377/1048425). Also, the fact that you are worried someone can read others passwords if they have read rights to the table suggests that the passwords might not be encrypted, which sounds far from ideal. – GarethD Oct 22 '15 at 15:10
  • Hi, if my answer was helpful it would be very kind to vote it up and - if it helped you solve your question - mark it as the accepted answer, thx! – Shnugo Oct 23 '15 at 13:19

1 Answers1

1

Somewhere you'll have to store the connection key and your application must get into the db somehow.

You have to decide if you want to use db-rights (GRANTS and PERMITS) or if there is something like a master password for your application and you own rights management handled by your application. DB rights can lead to hardly solveable problems and often leads to something like: Sh$$, we cannot solve this tiny problem, let's grant all rights to make it work...

If your application does not deal with extremely sensible data you should prefer the masterpassword approach with an application driven rights system.

In one of our projects we have an XML-config file with passwords in an encrypted form. The user's input is compared against this configured passwords - and there are some more hurdles I don't want to speak about naturally.

Your idea with a special login-user with a very restricted access to read just the passwords table sounds good. Your idea with an internal function to check the password sounds good too.

Better than just return TRUE or FALSE would be a session token, which is the leading key for rights management, persistance of session data (current rows, window positions, whatever...). Such a token could be used in (web)services. It could have a timeout, you can bind auditing to it...

Good luck in finding the best and fitting solution! Rights management is very tricky and it is really worth to think about this before you start...

Shnugo
  • 66,100
  • 9
  • 53
  • 114