Storing application users in SQL: create a new "Users" table or use built-in database user management?

Question

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?

Answer 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.

Answer 2

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.

Answer 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!

Answer 4

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.