Store users and transfer them to a single table or a separate table

Question

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

which 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?

Answer 1

Perfectly:

• Do not store passwords at all (even encrypted). Store hashes derived from passwords.
• Salt passwords to prevent rainbow attacks .
• Put the hashes on a separate database server, for your own firewall and your own well-defined API ¹ . This API should perform only three functions:
  • For this username, get the corresponding password hash.
  • For this username, set a new hash (to support password reset).
  • Delete this username and hash (to support deregistration).
• Do the same for salts: put them on your server and for your own firewall and API. This API should perform only three functions:
  • Find the appropriate salt for this username.
  • For this username, set the new salt to a random value (to support password reset).
  • Delete the user name and its salt (to support deregistration).
• Both hash and salt servers should be disconnected from the world (and from each other) and accessible only from the server on which your web application is running (for example, PHP or ASP.NET or something else ...).

When a user tries to log in by entering a username and password:

• Make sure that this is done via HTTPS, so the entered data safely reaches your site 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, enter the user password and compare it with the received hash.
• If they match, the user is granted access.

By its nature, hashes are irreversible - except for the user, no one, not even you, knows the exact password. If the user has forgotten the password, you cannot send them a password, but you can allow them to reset the password provided that they pass some additional verification (i.e. they have access to a specific email address and / or answer a secret question) .

BTW, logging in is a relatively rare operation, so this is unlikely to create a performance bottleneck if you do not completely ignore the correct indexing.

---

¹ for example. implement a web service, and then open only the port required for this web service, and nothing more.

Answer 2

I will go with option 1.

Think that there are user lakhs. Thus, in order to get user data, you have to deal with n tables instead of 1 table, which obviously add LOAD to the server, and finally you will have BAD PERFORMANCE.

So, I would go with option 1.

For tel. number, add a field like landline_number, mobile_number, alternate_number, as adding a field to a table will not make that much difference, and then add a table for the field.

And yes, according to Steve's comments, save the password using a secure hash mechanism.

So which option are you going to choose?

Answer 3

It depends. If you want to keep the password history, and if the user can have many phone numbers, you will create additional tables for passwords and phones. Otherwise, one table is enough.

Answer 4

First, as @Steve comments, you must store passwords using a secure hash mechanism. Saving passwords with clear text is irresponsible - this means that anyone who can crack your system knows the passwords of users that they can reuse on other sites,

Secondly, there are no security or performance advantages in any design - from a security point of view, you should assume that an attacker who can access your database can run queries, and it would be trivially easy to get data in both schemes. In terms of performance, the cost of joins in option 2 is hardly relevant if you have primary / foreign key indices.

If you have requirements for re-typing passwords after a certain period, and you need to keep a password history so that people cannot reuse passwords (this, for example, Windows support), you need to have a "UserPassword", with valid_from and valid_unit columns.