Web Database - How to store user information

Question

I'm starting to design a database to use in a web solution and would like to understand if there is any benefit to storing passwords (and the rest of the user information for that matter) in a separate table to the table with the user id.

Answer 1

The one benefit for storing passwords in a separate table, if you have a db that supports this, is you can use a security definer function to read the passwords, and this prevents sql injection or the like from reading it in other queries. Think of it as being like the /etc/shadow file on Linux. Of course this is in addition to salted hashes.

This being said I am of the opinion you should really avoid storing passwords in your database. The less of this sort of thing your app does the less you have to worry about security-wise. Outsource it to OpenID providers, etc. or if it is an intranet app, something like LDAP, AD, or the like.

Answer 2

There was a school of thought that said you should try to make user credentials hard to find in your database to make it harder for hackers to get at this information.

I think that this is ultimately wrong-minded, because once a hacker gets into your database it's not going to be much of a problem for them to unload your data and figure out what it is, regardless of how many tables you've got and how much trouble you've gone to spreading columns around.

The right place to thwart hackers is outside your firewall. You should keep your database schema as simple as possible to make it efficient and maintainable and take all of the necessary precautions to keep hackers from getting any access to your database.

One other thing you should do by way of keeping sensitive data safe is to encrypt sensitive data. Don't keep things like passwords or credit card numbers in plain text. Use strong encryption for these kinds of data elements.