用户帐户表、盐和哈希的第三范式
我了解盐、哈希值和所有密码的好东西的重要性。我的问题涉及关系数据库理论。
我对第三范式的理解是，每个元素都必须提供关于密钥、整个密钥的事实，并且除了密钥之外什么都没有（所以帮助我科德。感谢维基百科）。所以我正在检查我的一些表格，然后发现了这一点。
-- 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;
问题：这张表是第三范式吗？我的理解是......“哈希”取决于player_id和盐。 IE：哈希-> （用户名，盐）。
我只是看不出拆分这张桌子有什么真正的好处。但我担心可能存在更新异常或者我看不到的情况。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这很奇怪。
通常，哈希值源自盐和密码。
在这种情况下，哈希确实提供了有关特定用户的附加且重要的信息，因为密码本身不存储在任何地方。如果您同时存储了哈希值和密码，则哈希值在功能上将取决于密码和盐（可能还包括用户名）的组合。因此，同时存储哈希值和密码将违反 3NF 和使用哈希值的全部目的。
如果没有额外输入密码（未存储在任何地方），则不可能从数据库中的任何其他信息计算哈希值。否则，哈希值将毫无用处。既然如此，哈希列在功能上不依赖于数据库中的任何其他数据，并且该表符合 3NF。
如果您的哈希与密码无关，即可以从其他列计算出来，那么，是的，您不需要将其存储在数据库中。
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.
是的，这是正常的，不要拆分你的桌子
Yes, it's normal, and don't split your table
请不要拆桌子。该表采用第三范式。据我所知，所有列都依赖于player_id，但需要注意的是salt依赖于例如用户名或player_id。
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.