12

Is it any safer to create a table holding user information and another one for their passwords than using the same table for everything?

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
expora
  • 601
  • 5
  • 14

8 Answers8

10

No I would just do this:

id, username, password.

Where id is just autoincrement, username is a varchar of 20 (or so, depending on your needs) and password is an MD5 or SHA1 hashed password with a salt.

Using two tables for this just doesn't make sense. Then you need to work with joins to get the data. And that's just an unnecessary burden.

Anemoia
  • 7,928
  • 7
  • 46
  • 71
  • 3
    Don't forget to add the salt to the table, too. – Michael Burr Apr 28 '10 at 07:37
  • 2
    You don't necessarily need to do that. You can use one salt for every password. If the database is hacked they don't have the salt. If you add it to the database they do. The idea of a salt is just that the end user does not have final control over the resulting hash. – Anemoia Apr 28 '10 at 07:41
  • Thanks to everyone! I'm aware of the salted hash. I was just wondering if having two tables adds any valuable security feature. Cheers! – expora Apr 28 '10 at 07:47
  • 2
    Agree with Michael - you need unique salts per password. You should always assume the salt is available to the attacker. With a global salt, the attacker can build a dictionary of passwords *once* and use it against all users in the database. With a unique-per-password salt, the attacker has to build a different dictionary *for each user*, which is difficult. If he only wants the password for one specific user, neither approach is going to help; but when you are speaking about thousands of users, the unique salt will help a lot. – Sripathi Krishnan Apr 28 '10 at 09:17
  • Agreed with the unique (per user) salt, but rather than storing the salt separately to a new column you could use an existing column (with static data) as the salt - such as the username. – PaulG Apr 28 '10 at 15:39
  • I agree with the idea of a unique salt. Sounds like a fine solution! Cheers! – expora Apr 28 '10 at 16:05
  • There's far too much misinformation here. First, don't use MD5 or SHA1 anymore. There are better hashes available, such as one of the SHA2 hashes. Second, you need a separate salt for each password you store, and you need to change the salt every time you change the password. A good salt would be a 128-bit value generated by a cryptographically strong pseudo-random number generator (every language has one of these). You can store the salt and the hashed password in the same column if you have a good serialization scheme (see Django's), but it can be easier to store them in separate columns. – yfeldblum Jan 10 '11 at 20:57
  • Third, it is a good idea to use an application-wide key *also*, in addition to a salt. This key does not necessarily need to be stored in the database. However, key management is hard, so do this only if you know what you're doing. Fourth, you should use HMAC with either the salt or the key, if you have a key. Fifth, you should run multiple iterations (4096 or 65536) of the hash. – yfeldblum Jan 10 '11 at 21:00
  • @Justice, I don't see the point in multiple iterations of a salted hash? Surely if somebody can "crack" your hash algorithm (which is sort of against the concept of hashes) they can crack N iterations of it... – Matthew Jan 10 '11 at 22:55
  • The whole point is to make it take a orders of magnitude longer for an attacker to try to brute-force one or all of the hashes back into passwords, or to generate a rainbow table for your database. While running hmac-sha-256 is fast, running it 2^16 times is far slower, and an attacker will need 2^16 times more time or horsepower to try to bruteforce passwords or generate rainbow tables. – yfeldblum Jan 10 '11 at 23:19
  • But a rainbow table containing all possible SHA256 hashes will certainly also contain all repeat hashes.... That is: any 64 character random password of numbers, letters, symbols etc is equally likely to be cracked as any other 64 character random password of numbers, letters, symbols – Matthew Jan 10 '11 at 23:22
6

No, I cannot see how that can make it safer.

You should actually refrain from storing passwords at all. Just store their salted hash.

Further reading:

Community
  • 1
  • 1
Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
1

I disagree with other people - put the authentication information in a separate table and to the greatest extent possible pull authentication out of your application entirely. You shouldn't care. Think siteminder and the like - your web application doesn't have any information about how the user is authenticated. Password, smart card, etc. (Same thing with Kerberos or Active Directory on desktop applications.)

This approach even works if you use a framework like Spring Security. Just set up your interceptor so it looks at the authentication tables alone. You could even use separate DataSources so your interceptor can't see application data or vice versa.

Obviously your application will still need to manage information about the user's authorizations, something usually handled in a 'roles' table. But there's no need to for it to know how the user was authenticated.

bgiles
  • 1,200
  • 11
  • 12
  • How would storing usernames and passwords in separate table have any effect on whether or not the application handles authentication? – Matthew Jan 10 '11 at 22:51
1

i think having the username and password in the same table is ok ,

but also l have seen people doing silly stuff especially when working with the ORM , someone might end up exposing password hashes etc

for example entity framework C#

someone can just do

 appcontext.Users.ToList();

no attributes kept ensuring that password is hidden nor DTOs (Data Transfer Object) ,

upon noticing this l just keep another authentication table and the other thing l there are a lot of fields for forgot password, last password change all those fields l will have them in another table with the password

Billy Watsy
  • 74
  • 1
  • 10
0

No it is not safer. Just make sure your passwords are Salted+Hashed before you stored them in the DB.

UpTheCreek
  • 31,444
  • 34
  • 152
  • 221
0

No. Not unless each table required a different user account to access it - which would make querying it a complete pain - and even then, the hacker has worked out one login, so chances are they can get the other.

Just make sure that you are storing passwords in a hashed form, using a secure hash like SHA-2 and a salt. Do not store them in plain text and (IMHO) don't store them encrypted.

Adam Pope
  • 3,234
  • 23
  • 32
  • Thank you Adam. I was in that wavelength while thinking about this: would the hacker get access to everything? I guess it occured to me while learning a bit about Unix and files such as /etc/passwd and /etc/shadow. After reading the answers to my question it became clear this idea doesn't help in a Web application (not to mention the Unix case has a history). – expora Apr 28 '10 at 16:33
0

Btw, there is already a pretty simple (and powerful) c# salted hash class library (they've also included a small demonstration of how to use the library) out there - Link .

It also provides a verification mechanism (so you can verify the user's input as a valid password) and you can choose the Hash algorithm yourself (Sha1/MD5/etc.)

programmer
  • 3,043
  • 1
  • 22
  • 30
0

There is no security benefit, but using multiple tables can be useful for storing credentials to multiple systems tied to a single login.

As mentioned above, security should be provided by salted hash for passwords.

Matthew
  • 10,244
  • 5
  • 49
  • 104