存储用户并传递单个表或单独的表 [英] store users and pass in single table or separate table

问题描述

我想为我的网站创建用户管理系统，

对安全性和性能更好。

输入1：

  table_user：user_id，user_name，user_email，user_password。 user_phone ... 
  

或

2：

  table_user：user_id，user_name，user_email ... 
 table_pass：user_id，user_password。 
 table_phone：user_id，user_phone。 
  

哪一个更好？

解决方案

理想情况下：

• 不存储所有密码（甚至加密）。存储从密码派生的哈希。


• Salt 密码，以防止彩虹攻击。


• 将散列放在单独的数据库服务器上，位于自己的防火墙和自己定义良好的API ¹ 之后。这个API只能做三件事：
  
  
  
  
  1. 对于给定的用户名，检索相应的密码哈希。
  
  
  
  
  3. 删除指定的用户名及其哈希值（以支持用户注销）。
  
  

  / li>
  

• 对盐进行同样的操作：将它们放在自己的服务器上，并放在自己的防火墙和API之后。这个API只应该做三件事：
  
  
  
  
  1. 对于给定的用户名，检索相应的盐。
  
  
  
  
  3. 删除指定的用户名及其盐（以支持用户注销）。
  
  ol>
  
  4. 哈希和盐服务器应该从世界（和彼此）截断，只能从运行Web应用程序的服务器（即PHP或ASP.NET）访问或任何...）。
  
  

当用户尝试通过输入用户名和密码登录时：

• 确保通过HTTPS完成此操作，以便输入的数据安全地访问您的网站代码。


• 调用检索密码的API


• 调用检索用户名的盐的API。


• 盐和哈希用户输入的密码，并比较

他们的性质，哈希是不可逆的 - 除了用户，没有人，甚至你，知道确切的密码。如果用户忘记密码，您不能向他们发送密码，但您可以允许他们重置密码，假设他们通过一些额外的验证（即有权访问特定的电子邮件地址和/或回答秘密）问题）。




BTW，登录是一种相对较少的操作，因此不太可能造成性能瓶颈，除非您完全忽略适当的索引。





---





¹ 实现Web服务，然后只打开该Web服务所需的端口，而不打开其他任何端口。




I want to create a user management system for my site ,

what is better for security and performance .

Type 1 :

table_user : user_id , user_name , user_email , user_password . user_phone ...

or

Type 2 :

table_user : user_id , user_name , user_email ...
table_pass : user_id , user_password .
table_phone: user_id , user_phone .

which one is better ?

解决方案

Ideally:

• Don't store passwords at all (even encrypted). Store hashes derived from passwords.
• Salt the passwords to prevent rainbow attacks.
• Put hashes on a separate database server, behind its own firewall and its own well-defined API¹. This API should do only three things:
  1. For given username, retrieve the corresponding password hash.
  2. For given username, set the new hash (to support resetting the password).
  3. Remove given username and its hash (to support user unregistration).

• Do the same for salts: put them on their own server and behind their own firewall and API. This API should do only three things:
  1. For given username, retrieve the corresponding salt.
  2. For given username, set the new salt to a random value (to support resetting the password).
  3. Remove given username and its salt (to support user unregistration).

• Both hash and salt servers should be cut-off from the world (and from each other) and only accessible from the server that runs your Web application (i.e. PHP or ASP.NET or whatever...).

When user tries to log-on by entering username and password:

• Make sure this is done through HTTPS so the entered data safely reaches your website's code.
• Call the API that retrieves the password hash for the username.
• Call the API that retrieves the salt for the username.
• Salt and hash the password entered by the user and compare it to the retrieved hash.
• If they match, user is granted the access.

By their nature, hashes are irreversible - other than the user, nobody, not even you, knows the exact password. In case the user forgets the password, you can't send the password to them, but you can allow them to reset the password assuming they pass some additional verification (i.e. have access to a particular e-mail address and/or answer a secret question).

BTW, log-on is a relatively rare operation, so it's unlikely to pose a performance bottleneck unless you completely disregard proper indexing.

---

¹ E.g. implement a Web Service, then open only the port needed for that Web Service and nothing else.

这篇关于存储用户并传递单个表或单独的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！