How to NOT store user credentials in a database

by Marcin Dembowski on 10th January 2012

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

Storing credentials in a database - bad way

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.

Result of a query with a user password

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

Storing credentials with a separate database model

  • 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?

  • Mateusz Bukowicz

    Thanks for this great article. Indeed it is a common problem and it happens not only in .NET (also in Rails, JPA and generally anywhere you use ORM). I would like to add that adding salt does not solve the problem – you should also use other hashing function instead of SHA (because it is too fast :)). More info here: http://codahale.com/how-to-safely-store-a-password/

  • http://twitter.com/D3M80L Marcin Dembowski

    Mateusz, you are right. You need to be aware of this problem desiging the architecture in any language and technology. Thanks for your link and valuable comment because here I did not mentioned this area you wrote about, probably this should be another topic of blog post.

  • Kapil Viren Ahuja

    I dont get it. You title the article “Dont store passwords in Databsae” and none of your solutions talk about a solution to this. what you presented may be true, but it has nothing to do with how we are storing password or designing schemas – it is about how a webservice or a DAO is designed.

  • http://twitter.com/D3M80L Marcin Dembowski

    The title is ‘how to NOT store’, and it’s not the same like ‘do not store passwords’

  • Kapil Viren Ahuja

    ok my bad on the title. But, my point still remains the same. The problems you mentioned are not related to “how not to store”, but the design of the overall application and its layers.

  • http://twitter.com/D3M80L Marcin Dembowski

    I think I know what are you thinking about. When a developer is mapping a database schema to model he can forget that he should hide some data in the model (yes, model is not the same as database schema). For example when you are using Entity Framework database-first with WCF Data Service (here we expose the whole existing schema through webservice) then all references to the user with password are included. The problem can occur also when you are offering some reporting on database, you can easily get the password from database by using “select password from User”. How to fix the problem depends really always on the usage – the most important think is that you should take this piece of architecture and design into account during working with database – not always we require to have a seperate authenication service or provider where user credentials are stored on another machine, in most cases only one schema in database satisfies our needs.

  • Pingback: Cheatsheet: 2012 01.10 ~ 01.19 | 编程

  • Daniel Częstki

    I think the first example is not insecure untill you use it unsecured.