How to best store user information and user login and password

Question

I'm using Mysql and I was assuming it was better to separate out a users personal information and their login and password into two different tables and then just reference them between the two.

Note : To clarify my post, I understand the techniques of securing the password (hash, salt, etc). I just know that if I'm following practices from other parts of my life (investing, data backup, even personal storage) that in the worst case scenario (comprised table or fire) that having information split among tables provides the potential to protect your additional data.

Solution 1

Don't store passwords. If it's ever sitting on a disk, it can be stolen. Instead, store password hashes. Use the right hashing algorithm, like bcrypt (which includes a salt).

EDIT: The OP has responded that he understands the above issue.

There's no need to store the password in a physically different table from the login. If one database table is compromised, it's not a large leap to access another table in that same database.

If you're sufficiently concerned about security and security-in-depth, you might consider storing the user credentials in a completely separate data store from your domain data. One approach, commonly done, is to store credentials in an LDAP directory server. This might also help with any single-sign-on work you do later.

Solution 2

The passwords should be stored as a cryptographic hash, which is a non-reversible operation that prevents reading the plain text. When authenticating users, the password input is subjected to the same hashing process and the hashes compared.

Avoid the use of a fast and cheap hash such as MD5 or SHA1; the objective is to make it expensive for an attacker to compute rainbow tables (based on hash collisions); a fast hash counteracts this. Use of an expensive hash is not a problem for authentication scenarios, since it will have no effect on a single run of the hash.

In addition to hashing, salt the hash with a randomly generated value; a nonce, which is then stored in the database and concatenated with the data prior to hashing. This increases the number of possible combinations which have to be generated when computing collisions, and thus increases the overall time complexity of generating rainbow tables.

Your password hash column can be a fixed length; your cryptographic hash should output values which can be encoded into a fixed length, which will be the same for all hashes.

Wherever possible, avoid rolling your own password authentication mechanism; use an existing solution, such as bcrypt.

An excellent explanation of how to handle passwords, and what you need to concern yourself with, can be found at http://www.matasano.com/log/958/enough-with-the-rainbow-tables-what-you-need-to-know-about-secure-password-schemes.

As a final note, please remember that if an attacker obtains access to your database, then your immediate concern should probably be with any sensitive or personally-identifying information they may have access to, and any damage they may have done.

Solution 3

There's nothing wrong with putting them in the same table. In fact, it would be much faster, so I'd highly recommend it. I don't know why you'd want to split it up.

Solution 4

I'll attempt to answer your original question. Having it all in one table is fine unless you just have a lot of personal information to gather. In that case it may make sense to split it up. That decision should be made based on the amount of personal information you're dealing with and how often it needs to be accessed.

I'd say most of the time I'd do something like this in a single table:

UserID, FirstName, LastName, Email, Password, TempPassword

But... if you're gathering much more than that. Say you're gathering phone, fax, birth date, biography, etc, etc. And if most of that information is rarely accessed then I'd probably put that in its own table and connect it with a one-to-one relationship. After all, the fewer columns you have on a table, the faster your queries against that table will be. And sometimes it makes sense to simplify the tables that are most accessed. There is a performance hit with the JOIN though whenever you do need to access that personal information, so that's something you'll have to consider.

EDIT -- You know what, I just thought of something. If you create an index on the username or email field (whichever you prefer), it'll almost completely eliminate the performance drawback of creating so many columns in a user table. I say that because whenever you login the WHERE clause will actually be extremely quick to find the username if it has an index and it won't matter if you have 100 columns in that table. So I've changed my opinion. I'd put it all in one table. ;)

In either case, since security seems to be a popular topic, the password should be a hash value. I'd suggest SHA1 (or SHA256 if you're really concerned about it). TempPassword should also use a hash and it's only there for the forgot password functionality. Obviously with a hash you can't decrypt and send the user their original password. So instead you generate a temporary password they can login with, and then force them to change their password again after login.

Solution 5

Will all of this data always have a 1:1 relationship with the user? If you can forsee allowing users to have multiple addresses, phone numbers, etc, then you may want to break out the personal info into a separate table.

View more solutions

Comments

[nullException]

i seriously doubt it would be faster, since checking name/password and userdata would be on very different parts of any application, and done at very different moments. think: do you need the valid data for each login rejection? after you do accept it, do you need to check the password each time you read the profile? of course, i usually don't bother, and put it all together. but it's just for simplicity, not performance.

[Alex Weinstein]

Don't store the temporary password. It's a hole.

[Steve Wortham]

The temporary password should also be a SHA1 hash. How is that a hole?

[Steve Wortham]

Just to explain, the reason for the temporary password is to prevent your original password from being overwritten unintentionally when the forgot your password feature is used. It's still secure.

[Sasha Chedygov]

No, because you'd need to do JOINs whenever you get the userdata. Either that or you'd have a lot of duplicate information (e.g. username and ID stored in both, etc.), neither of which is optimal. Either way, there's no reason to split it up into two different tables; it just doesn't make any sense, whether for performance or simplicity.

[Bill Karwin]

+1 This is the only answer (so far) that answers the OP's question.

[Sasha Chedygov]

Doesn't really answer the question.

[Michael Petrotta]

@musicfreak: I disagree, in that the way the question was phrased could imply that the OP hadn't considered that aspect of user management, one that's orders of magnitude more important than whether to store the username and password hash in separate tables. A good answer addresses the larger question. You're free to disagree, of course.

[Steve Wortham]

Actually the question was asking if you should create a table for personal information (I'm assuming first & last name, phone number, etc) and a separate table for the login (username/password combination). It's actually a good question. But most of the answers I see here have gone off onto the tangent of security.

[Steve Wortham]

There is a performance consideration though as Javier was getting at. If you are gathering a lot of personal information that you don't necessarily need to load every time the user logs in, then it may make sense to put that information in its own table.

[Steve Wortham]

Sorry, the reason I say that is that every query is faster when you can fit more rows into a smaller amount of data. And if you have 100 columns in your user table where you're retrieving only two of the columns 95% of the time, then you may have a design problem.

[Sasha Chedygov]

But you could just retrieve only the columns you need, instead of doing a SELECT *. I see your point, but I still stand by my opinion that there is no reason for the two to be in different tables.

[Steve Wortham]

I was going to say that the WHERE clause itself will be slower on a table with more columns (even if you're explicitly selecting the columns you need). And that's true of table scans. But you know what -- I just thought of something. If you create an index for the username field it'll almost completely eliminate this problem for all purposes of logging in.

[Sasha Chedygov]

OR you could be a careful programmer and prevent SQL injection attacks in the first place. ;)

[Jason]

Of course =P Naturally, for every countermeasure implemented, there is a circumvention, though. So may as well cut losses and make it more difficult to get at nonrelated information.

[Tim]

Yes my original question had to do with if your table was compromised I would rather have one piece of data available instead of both pieces.

[Michael Petrotta]

Fair enough. Tim, if any of the other responses answer your question appropriately, I suggest you accept them, which will bump it over this one. Remember that if one table is compromised, it's not such a big leap to get at another table. A common best practice, one I've done before, is to store credentials in a completely separate datastore, like an LDAP directory server.

[Abneco]

en.wikipedia.org/wiki/One-way_encryption In fact, the choice to use one term over another is not quite important so much as the idea conveyed, and many people use the term 'One way encryption' and are understood commonly.

[jwhitlock]

The blog post has moved to chargen.matasano.com/chargen/2007/9/7/…

[Buttle Butkus]

@nikoss The bug is supposedly fixed. Read the second-to-last comment in your linked bug report.