How to best store user information and user login

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.

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

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

First, to state the (hopefully) obvious, if you can in any way at all avoid storing usernames and passwords do so; it's a big responsibility and if your credential store is breached it may provide access to many other places for the same users (due to password sharing).

If you must store credentials:

• Don't store a reversible form; store a hash using a recognized algorithm like SHA-256. Use cryptographic software from a reputable trustworthy source - DO NOT ATTEMPT TO ROLL YOUR OWN, YOU WILL LIKELY GET IT WRONG.
• For each credential set, store a salt along with the hashed data; this is used to "prime" the hash such that two identical passwords do not produce the same hash - since that gives away that the passwords are the same.
• Use a secure random generator. Weak randomness is the number one cause of encryption related security failures, not cipher algorithms.

If you must store reversible credentials:

• Choose a good encryption algorithm - AES-256, 3DES (dated), or a public key cipher. Use cryptographic software from a reputable trustworthy source - DO NOT ATTEMPT TO ROLL YOUR OWN, YOU WILL LIKELY GET IT WRONG.
• For each credential set, store a salt (unencrypted) along with the encrypted data; this is used to "prime" the encryption cipher such that two identical passwords do not produce the same cipher text - since that gives away that the passwords are the same.
• Use a secure random generator. Weak randomness is the number one cause of encryption related security failures, not cipher algorithms.
• Store the encryption/decryption key(s) separately from your database, in an O/S secured file, accessible only to your applications runtime profile. That way, if your DB is breached (e.g. through SQL injection) your key is not automatically vulnerable, since that would require access to to the HDD in general. If your O/S supports file encryption tied to a profile, use it - it can only help and it's generally transparent (e.g. NTFS encryption).
• If practical, store the keys themselves encrypted with a primary password. This usually means your app. will need that password keyed in at startup - it does no good to supply it in a parameter from a script since if your HDD is breached you must assume that both the key file and the script can be viewed.
• If the username is not necessary to locate the account record encrypt both the username and password.

Answer 6

In my personal experience, storing the personal information and the login information in individual databases is the best practice in this case. The reason being should an SQL injection take place, it is limited (unless the infiltrator knows the inner layout of your database(s)) to the table that the data pertains to, as opposed to providing access to the whole conglomerate of data.

However, do note that this may come at the expense of needing to perform more queries, hence a performance hit.