6

Should I store credentials (like email, hashed password) in a separate table than the user's profile information (bio, gender, etc)?

My main concern is that I'll be sending other user's information (that doesn't include credentials) to the front-end for the user to see.

Improve this question
1
  • 1
    One additional aspect not mentioned in the answers: If you store it in a separate table, you can apply table level access permissions for different users. If all queries not needing the sensitive information use a database user that cant even read it, you mitigate the effect of a SQL injection vulnerability.
    – Anders
    Nov 16, 2022 at 9:20

2 Answers 2

Reset to default
4

There are usually other more important factors for data security, but main reason to consider storing credentials in a separate table (to other profile info) is to reduce the chances that you accidentally expose these fields in a way that the identity critical information can be accessed or modified through the same CRUD mechanisms that you use to expose the other profile information.

  • Splitting a table like this is one way to protect against over-post attacks, or to mitigate the chances of users or developers executing SELET * which could leave the critical data in a vulnerable memory space or worse, being transferred in plain text over HTTP.

  • SQL Injection should be mitigated through the use of an ORM or at least by using parameterized queries, do not split the table to prevent SQL Injection.

If you are using code generation tools or ORMs you might find it easier to isolate or remove a whole table from the schema instead of masking or removing individual fields, it you remove the critical information from the business model schema then you will need to manually code a pathway that allows you to update/verify password hashes or change assigned user identity.

My main concern is that I'll be sending other user's information

Good data storage practises are not the same as good data access practises. Storing data across different tables doesn't immediately protect you much if your API still allows for navigation to related records.

To decide if you should store this data in a separate table requires some understanding and analysis of your data access strategy. If your current strategy is for the UI to SELECT * FROM TableX and manage most of the CRUD operations from the UI directly, then splitting this data across multiple tables is a better default strategy than doing nothing at all, but on its own this really just ignoring your security responsibility.

There are other strategies that can be used but their implementation and effectiveness will depend greatly on the data access code or pattern you choose to implement.

Improve this answer
2

There are two more reasons why it would be a good idea to have the two sets of info stored separately:

  1. Semantically, a user account and a user profile are different; a user account represents an entity that can use the system to which the account refers to. As such, a user account usually includes the username, password, last login, number of failed login attempts etc. On the other hand, the user profile refers to the person that created the account (lets keep it simple and exclude legal entities, e.g. companies, from being users). As such, a user profile usually includes the full name, address, telephone etc. You can have everything in the same table, but given that they represent two different things it is a good practice to put them in separate tables
  2. Having a user account and profile stored separately facilitates legal obligations like those imposed by EU GDPR; the user account does not (or should not) identify the person that it represents (with the notable exception of the username, which is usually the person's email address or telephone number, but there are approaches for this too) so there's no need to add extra layers of protection (e.g. encryption) to the relevant data. The user profile, though, contains personal data (or personally identifiable information) that require an extra level of protection (e.g. encryption) to account for privacy threating situations (e.g. data breach or insider threats). It's easier to encrypt the data of a whole table (in case that the two - account and profile - are stored separately, you just encrypt the profile table) than to have to selectively encrypt table columns. It's also less resource consuming to have to encrypt only the necessary data (profile) rather than data that you don't have to (account)
Improve this answer

You must log in to answer this question.