How to best store user information and user login and password

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.