3

I understand the importance of salts, hashes and all that good stuff for passwords. My question relates to relational database theory.

My understanding of 3rd normal form is that every element must provide a fact about the key, the whole key, and nothing but the key (So help me Codd. Thanks Wikipedia). So I was reviewing some of my tables, and I came across this.

-- Users
CREATE TABLE accounts(
    player_id mediumint NOT NULL AUTO_INCREMENT, -- Surrogate Key
    username VARCHAR(32) UNIQUE NOT NULL, -- True primary key
    salt char(29), -- Passwords are stored in bcrypt hash
    hash char(60), -- Salt + Hash stored
    created DATETIME,
    lastlogin DATETIME,
    PRIMARY KEY (player_id)
  ) ENGINE = InnoDB;

Question: is this table in 3rd normal form? My understanding is... the "Hash" is dependant on the player_id and the salt. IE: hash -> (username, salt).

I just can't see any real benefit to splitting up this table. But I'm worried that there's a possible update anomaly or something I can't see.

Algorithmist
  • 6,657
  • 7
  • 35
  • 49
Dragontamer5788
  • 1,957
  • 1
  • 12
  • 20

3 Answers3

1

the "Hash" is dependant on the player_id and the salt. IE: hash -> (username, salt).

That's weird.

Usually the hash is derived from the salt and the password.

In that case, the hash does provide additional and essential information about the specific user, because the password itself is not stored anywhere. If you stored both the hash and the password, the hash would be functionally dependent on the combination of password and salt (and maybe username). Storing both hash and password would thus violate 3NF and the whole purpose of using a hash.

It must be impossible to calculate the hash from any other information in your database without the extra input of the password (not stored anywhere). Otherwise, the hash would be pretty useless. And since that is the case, the hash column is not functionally dependent on any other data in the DB, and the table conforms to 3NF.

If your hash has nothing to do with the password, i.e. can be calculated from the other columns, then, yes, you do not need to store it in the DB.

Thilo
  • 257,207
  • 101
  • 511
  • 656
  • The password is not stored in this table, so by definition, the Hash can't functionally dependant on the password :-). I don't store passwords in plaintext. Of course, the Hash is of the form H(salt, password). http://en.wikipedia.org/wiki/Functional_dependency – Dragontamer5788 Jun 01 '11 at 04:31
  • In this case, the hash provides additional information about the user, and is not *entirely* dependent upon other columns. So it is 3NF. – Thilo Jun 01 '11 at 04:34
  • username+salt hash might be for verification purposes. hash column may be searched upon to find matches used for email verification, etc. if it's searched, it's a good optimization, but it's not technically 3rd Normal Form – David Chan Jun 01 '11 at 04:35
  • Your new paragraph makes sense to me. Thanks. – Dragontamer5788 Jun 02 '11 at 00:16
1

Yes, it's normal, and don't split your table

Rob
  • 415,655
  • 72
  • 787
  • 1,044
DeveloperX
  • 4,633
  • 17
  • 22
0

Please don't split the table. This table is in 3rd normal form. As far as I see, all the columns are dependent on player_id, with the caveat that salt is dependent on for example user name or player_id.

Alex Aza
  • 76,499
  • 26
  • 155
  • 134