Use one-to-one to ensure security

database-design

I was reading a book about database design, and I saw this information related to one-to-one relationship:

One-to-one relationships are sometimes also used as part of a security structure. A single entity may be broken into two entities. One will contain publicly viewable content and the second private content. For example, an employee’s information might be broken into two entities.

The first one contains nonsensitive content such as the employee’s name, department, business phone number, and position title. The second table contains sensitive material such as the employee’s social security number, home address, home phone, and salary information. There is a one-to-one relationship between the tables. Each employee has exactly one related record in the private table.

So, my question is: Is it recommended to use this approach for security matters?

Best Answer

Yes and no.

If the main concern is to restrict what is visible to different users, a view will be sufficient.

create schema sensitive_data;
create schema non_sensitive_data;

create view non_sensitive_data.employee as select id, name, department, phone, title from hidden.employee;
create view sensitive_data.employee as select id, salary, ssn from hidden.employee;

Privileged users will be given rights on sensitive_data, non-privileged users on non_sensitive_data. In RDBMSes with support for updatable views the users will access the employee table by means of the view -- and they can only select, update, or delete columns exposed on the view. They don't need access to the hidden schema at all, and can't access the underlying table directly.

However, if there is legal requirement to for example "delete all sensitive employee records as soon as the employee resigns" but you would like to retain the "neutral" employee details because the employee is a foreign key in many entities, then a two-table design might make sense.

Related Question