1

I am specifically interested in SQL Server, but the same question applies in general. When creating a new application, the way I see it, there are two options:

  • Create a table called "Users" and store the user name, password, etc. Set up one database user called "application" (and possibly more users for various components of the system).
  • Set up each application user as the database user. This may allow for easier single log on setup on Windows-based systems.

Which approach is generally preferred? Why? What are the drawbacks and benefits?

1
  • <span style="text-decoration:line-through">password</span> password hash :)
    – Roy Tinker
    Aug 10, 2015 at 22:54

4 Answers 4

1

Another advantage of using the ad account or similar is that in your database itself you can run in built in functions to work out who is logged in. This can be useful for things like auditing, or to know who is accessing your database at any point in time using the tools provided by your database.

However as previously mentioned this doesn't scale particularly well. Lots of open connections on your database via different users is expensive in terms of server resource.

Using either scenario, for most larger apps, you're going to have a users table either way. Whichever authentication method you use, once you know who that user is you will likely need to know more about them - what their date if birth is, their nationality, financial authorisation limits, permission to click on the save button... Very often you will use a users table and associated tables to store whatever you need to know for the app.

Some frameworks, such as asp.net for example, provide tools that will automatically give you templates for maintaining your own user tables.

4

Database user management is intended to provide a mechanism for the users to access the database itself. Using it to store, for example, the accounts of your customers, is a perversion: it was never designed for that. By reusing this mechanism for anything else, you're decreasing your database security level, especially if you don't have a DBA capable of configuring the security aspects flawlessly.

If you are using SQL Server in Mixed mode (i.e. you can authenticate using not only your Active Directory credentials, but also by providing a user name and a password), you shouldn't do it in the first place.

If you are wondering whether Active Directory accounts can replace User table (especially your point "This may allow for easier single log on setup on Windows-based systems"), then see the recent question: Using a Directory Service instead of a Database.

3
  • 1
    @downvoter: feel free to comment about what's wrong with my answer, so I can improve it. Simply downvoting isn't helpful neither for me, nor for the community. Jul 22, 2013 at 4:08
  • 1
    Can you clarify why using database accounts decreases security level? I was under impression that it's possible to set pretty harsh restrictions on users. And the linked response seems to indicate that using Active Directory credentials may be useful in some cases. (Btw, I did not downvote you).
    – ikh
    Jul 22, 2013 at 16:03
  • @ikh: you can set harsh restrictions on users, but it's not an easy task and requires to understand pretty well how to administer the database. One mistake, and somebody, somewhere, has unlimited access to your data. Jul 22, 2013 at 17:39
3

When developing desktop applications for a customer office, per user accounts can have a lot of advantages (integrated security etc)

But for web application with potentially 1000000’s of users, it is quite common to use a service account to the SQL server, and run a custom users table which makes account selfservice a viable option. Just make sure to use proper encryption (salt and hash) for your passwords!

0

In SQL Server, you create logins at the instance level and users at the database level. This can be confusing, but consider that the instance login is for actually gaining access to instance objects, whereas the users are for assigning roles and rights in the database. If, for example, you restore a database from one machine with one set of logins to another instance on another machine, your logins will only be those configured on the target, whereas the users will still be included in the restored database - they just can't log in.

If you are using Windows integrated security then this is all you need to do. You can save the user name to a column as shown:

'insert into [Companies] (DateAdded, AddedBy)
' values (convert(varchar(50), getdate()),
' USER_NAME(DATABASE_PRINCIPAL_ID()))

If you have thousands of web subscribers, then you need to define your own security layer. If you're using ASP.NET, you can create a web based project where the Visual Studio program will script all this for you to start with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.