用户详细信息表中的用户身份验证详细信息还是单独的？
我有一个用户表，其中包含所有用户详细信息，如姓名、电子邮件、密码哈希、性别、城市、教育程度等......总共大约 45 列。
问题是：我应该将其标准化为两个表，一个用于用户身份验证，即 user_id、email、password_hash、password_salt。
第二个表用于保存用户详细信息，或者可以将两者都放在一个表中吗？我不能过多地标准化用户详细信息，因为这是一个关键系统，性能非常重要，因此需要保持较低的连接数。
我只是担心用于登录身份验证的用户密码/电子邮件是否应该放在单独的表中以获得更好的安全性？
I have a user table which has all the user details like name,email, password hash, gender, city, education, etc... about 45ish columns in total.
The question is: Should i normalize this into two tables one for the user authentication, that is user_id, email, password_hash, password_salt.
And second table to hold user details or is it ok to have both in 1 table? I cant normalize the user details too much because this is a critical system and performance is very important so need to keep joins low.
I am just concerned if user password/email used for login authentication should be in a separate table for better security or not?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
Symfony 的 sfGuard 使用两个表：一个用于身份验证数据，另一个用于用户详细信息。这看起来相当明智 - 因为这将两组不同的数据彼此分开，并且用户身份验证表很窄，因此它更适合内存（和各种缓存）并且不需要使用（更广泛的）用户详细信息进行操作每当需要身份验证时。
至于安全性，我并没有真正看到一个表与两个表之间的差异，除了备份存储要求之外。
与往常一样，衡量并比较这在您的特定情况下的表现。
Symfony's sfGuard uses two tables: one for authentication data, other for user details. That looks rather sensible - as this separates two different sets of data from each other, and the user auth table is narrow, thus it fits better into memory (and various caches) and doesn't need to operate with the (wider) user details whenever authentication is needed.
As for security, I don't really see a difference in one table vs. two tables, except maybe in backup storage requirements.
As always, measure and compare how this stacks up in your specific situation.
两张表本质上不会比一张表提供额外的安全性，除非这些表以非常不同的方式实现。例如，它们被分区到不同的硬件，并且密码表具有本机磁盘加密或类似的东西。即便如此，我们仍在谈论不同形式的“安全”。两张表仍然处于活动状态并正在使用中，一张表只是在静态时进行加密。 （可能还有其他示例，但这就是我现在能想到的全部。）
就我个人而言，我倾向于使用两个表，不是从安全角度出发，而是从关注点分离的角度出发。一是身份验证，一是用户配置文件。在绝大多数情况下，它们不需要分开，但我更喜欢将它们分开，以防万一需要将它们分开的逻辑更改，因为它们在逻辑上不是同一件事。
Two tables wouldn't inherently provide additional security over one table unless those tables are implemented in very different ways. Say, for example, they're partitioned off to different hardware and the password table has native disk encryption or something like that. And even then we're talking about different forms of "security." Both tables are still live and in use, one would just be encrypted at rest. (There may be other examples, but that's all I can think of right now.)
Personally, I tend to use two tables not from a security point of view but rather from a separation of concerns point of view. One is authentication, one is the user profile. In the vast majority of cases they don't need to be separate, but I prefer to keep them separate just in case a logic change is required that needs them to be separate, since they are logically not the same thing.