35

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.

Tim
  • 962
  • 3
  • 9
  • 25
  • Just to clarify; do you want to know how to properly store passwords in the database, or do you simply wish to know whether user profile data should be separated from base account data? I answered for the former, but upon re-reading the question, you appear to want the latter. – Rob Jun 03 '09 at 23:09
  • 1
    Most answers focus on the method for hashing and salting passwords. This is worthwhile information, but doesn't address the OP's question. – Bill Karwin Jun 03 '09 at 23:20
  • Yes I will be storing the password using sha1 and a salting. – Tim Jun 04 '09 at 12:47

8 Answers8

44

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.

Community
  • 1
  • 1
Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
  • 3
    +1 for everything but the SHA recommendation. SHA and MD5 are in the same, insufficient class of hashing algos -- from a security standpoint, they both pale in comparison to adaptive hashing, as mentioned by Rob below. – Frank Farmer Jun 03 '09 at 23:22
  • Doesn't really answer the question. – Sasha Chedygov Jun 03 '09 at 23:26
  • 2
    @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. – Michael Petrotta Jun 03 '09 at 23:36
  • 1
    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 Jun 03 '09 at 23:47
  • 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. – Tim Jun 04 '09 at 12:48
  • 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. – Michael Petrotta Jun 04 '09 at 15:41
18

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.

Rob
  • 47,999
  • 5
  • 74
  • 91
  • 2
    The blog post has moved to http://chargen.matasano.com/chargen/2007/9/7/enough-with-the-rainbow-tables-what-you-need-to-know-about-s.html – jwhitlock Dec 22 '10 at 15:55
  • 1
    @nikoss The bug is supposedly fixed. Read the second-to-last comment in your linked bug report. – Buttle Butkus Jan 10 '19 at 22:06
14

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.

Sasha Chedygov
  • 127,549
  • 26
  • 102
  • 115
  • 1
    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. – Javier Jun 03 '09 at 22:43
  • 2
    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. – Sasha Chedygov Jun 03 '09 at 23:02
  • 1
    +1 This is the only answer (so far) that answers the OP's question. – Bill Karwin Jun 03 '09 at 23:18
  • 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 Jun 03 '09 at 23:51
  • 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. – Steve Wortham Jun 03 '09 at 23:56
  • 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. – Sasha Chedygov Jun 04 '09 at 00:23
  • 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. – Steve Wortham Jun 04 '09 at 00:45
8

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.

Steve Wortham
  • 21,740
  • 5
  • 68
  • 90
4

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.

dkaylor
  • 466
  • 2
  • 3
3

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.
Lawrence Dol
  • 63,018
  • 25
  • 139
  • 189
2

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.

Jason
  • 452
  • 4
  • 9
  • 3
    OR you could be a careful programmer and prevent SQL injection attacks in the first place. ;) – Sasha Chedygov Jun 04 '09 at 01:25
  • 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. – Jason Jun 04 '09 at 01:57
0

You ought to store them in the same table, and use one-way encryption. MD5 will work, but is weak, so you might consider something like SHA1 or another method. There's no benefit to storing the 2 items in seperate tables.

dreadwail
  • 15,098
  • 21
  • 65
  • 96
  • 2
    There's no such thing as one-way encryption; the term is cryptographic hashing. MD5 and SHA1 are fast hash algorithms and not a good idea for password hashing. You should salt the hash with a random nonce stored in the database. – Rob Jun 03 '09 at 22:57
  • 2
    http://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. – dreadwail Nov 01 '09 at 21:03