How to NOT store user credentials in a database

Sometimes you need to create an application with your own role and user management module. In most cases you are creating it in a simple and understandable way. That way the user information with a password is stored in the database in the same table named User. In this post I will show you why this solution is not safe, why you should omit such structures and how to fix them.

I will not discuss which authentication and/or authorization services and/or existing libraries you should use in your application. I will only focus on the reason why the described solution is not safe and how to make it more secure. The example will be based on the .NET Framework with C# usage but you need to be aware of it when you are developing it in any language.

Our application…

Let’s imagine that we want to have some custom blogging system where the user is able to create posts, comments, notes and some other users. We will use some web service communication (WCF Data Services) between our database and a client application which can be for example a web application.

In the time of HTML5, many applications will have its UI created in HTML5 which will be placed in a web browser (using JavaScript, CSS, HTML). Some storage database will be placed in the cloud or on a private server. The communication between these two layers will be made with the use of a web services. The web service can be secured with the use of https protocol. Let’s forget about the security now. Imagine we transfer the data through a secure channel using JSON, XML or plain text format.

… BAD architecture …

Take a look at the database scheme below, you can see that we have one table where we store a user name, hashed password and information about “if the user is an administrator of the system and can access some restricted resources”. In the sample I created only User and Post tables where Post has a reference to User.  Additionally we can have more tables, where we store some other business data like blog posts, comments, user notes, reviews etc..

Now we need to have a webservice which has access to our database. I created some simple WCF Data Service as a sample. Thanks to this we can quickly select data from interesting tables directly from our browser. Let’s assume that we do not have access directly to the User table but we can access user details from Post table using the url.

Here we have access to user data like a User name, hashed password and information about whether the user is a system administrator. Of course, this example is so simple and unsafe that it is not possible that this can happen in a real enterprise system…. well, it can!

… and its security leaks …

When you create a bigger system with tons of tables using some request-response service layer, where you create handlers responsible for retrieving a piece of the database, you or your team member can one day return a list of user credentials, by returning for example a list of post comment authors. You can make this mistake easily by including the data in DataContext using Entity’s Framework Include method (eager loading, lazy loading).

When you are transferring POCO objects (DataObjects) directly from DataContext to a client, thanks to this you can easily include the hashed password in the web service. What is worse, the password is hashed using the SHA algorithm only. It’s hard but possible to break it. I downloaded a list of all users in the system using the method presented above and I see that some users have the same credentials (the same password hash).

I can ask some of my friends for their password, who have the same password hash like an admin or use some common passwords and knowing that I can break the system.

… and the fix

• The simple answer is, that you can restrict the data which is returned from a table by selecting only some columns. Unfortunately a developer is a human being (yes, that’s true), so  making mistakes and forgetting some rules is common. Probably sooner or later someone will return the credentials including the whole user entity, making the mistake described above.
• Regarding storing passwords in a database, instead of storing only its hash, store also some salt value (some explanation article). Thanks to this it will be hard to find the same passwords when someone will retrieve user passwords hash and salt values.
• Another way to get rid off the problem is to move user credentials to a separate table without creating a reference key to the user. Thanks to this the User will not have a direct reference to the UserCredentials table. You can retrieve user data from User table without credentials. The UserCredential table is placed in the same data model, but has no reference keys and because of this it will not be included in the result.
• Another more secure way is to make this in a similar way like presented previously, but with another database context, using some other database model. Here we come to user authentication/authorization separation. We can create a separate mechanism: web service, database, database model which will store such data. In our sample you can store the credentials in the same database but in another table named UserCredentials. You can also store a reference key between these tables but include the table with a reference in a separate data model (like below).

• You can also use the model which was presented at the beginning with a small modification. Remove the Password and IsAdministrator columns from the data model, and authorize/authenticate the user with the use of stored procedures. That’s an easy way to modify an existing system.

What’s your opinion about the presented solutions? Do you know and use some authorisation/authentication mechanisms? How do you store user credentials in your system?

Tags: Backend