CODEWITHSUNDEEP
mysqlsqlsql-serverdatabasedatabase-design
Ata
Ata

Reputation: 12544

store users and pass in single table or separate table

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 ?

Upvotes: 15

Views: 11366

Answers (4)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

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 API1. 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 server.
  • 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.

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

Upvotes: 26

Fahim Parkar
Fahim Parkar

Reputation: 31637

I will go with option 1.

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

So, I would go with option 1.

For tel. number, add field as landline_number, mobile_number, alternate_number as adding field in table won't make that much difference then adding table for the field.

And yes, as per Steve comment, store password using secure hashing mechanism.

So what option are you going to choose?

Upvotes: 4

Neville Kuyt
Neville Kuyt

Reputation: 29629

Firstly, as @Steve comments, you should store passwords using a secure hashing mechanism - storing plain text passwords is irresponsible - it means that anyone who can hack into your system knows user passwords which they may have re-used on other sites.

Secondly, there is no inherent security or performance benefit in either design - from a security point of view, you have to assume that an attacker who can get access to your database can run queries, and it would be trivially easy to retrieve data in both schemes. From a performance point of view, the cost of the joins in option 2 is unlikely to matter if you have primary/foreign key indices.

If you have requirements to re-set passwords after a certain period, and you need to store password history to prevent people re-using passwords (this is a feature Windows supports, for instance), you need to go have a "UserPassword" table, with valid_from and valid_until columns.

Upvotes: 2

kpater87
kpater87

Reputation: 1270

It depends. If you would like to keep password history and if user can have many telephone numbers then you create additional tables for passwords and phones. In other case one table is enough.

Upvotes: 2

Related Questions