Database naming of columns with PII and sensitive information

Question

I am working with database tables that contains PII and sensitive information. Some of the data is PII and company sensitive information. The design document may not always be available to the developer especially when the data is being exposed by a view outside of the database (Oracle database link) to another program.

Is there good naming conventions for letting the developers know the column contains PII or sensitive information?

Answer 1

Column names? Weak.

Use a table of PII data that contains all of the PII attributes. Keep the PII tables separate from other tables with non-PII.

A one-to-one join between the non-PII and PII isn't all that costly. And it provides clear, obvious use of the PII table and PII columns.

Answer 2

I agree with S.Lott but I'll be more explicit. If the data are sensitive then it's even MORE important that column names be chosen to mean what they actually contain, that datatypes be actually appropriate for the contents; and the constraints actually match the problem domain.

Then, on top of that, move this stuff to another table, where they can be appropriately encrypted, stored in the correct location, etc.

Answer 3

I would have separate views (eg table PERSON with PERSON_BASIC having no PII columns and PERSON_PII with the PII columns). That way, if it is later decided that a column is sensitive (eg Date of Birth), then you can easily recreate the views to remove the column from the basic view rather than some massive data restructuring exercise which you would get with separate tables.

Also, the optimizer is getting better at correlation between columns on the same table (and you'd expect that to improve over time). Once you start joining non-PII tables to PII tables, you've just made it more complicated.

If you do go for separate tables, and think they'll need to be joined often, look into clusters so that the records for the same person are on the same block.

Consider using a role secured by a password or through a package to control access to the PII view/columns.

CONTEXT can do this too. They are forced to call the package to set the context before they see the columns. The view could be

SELECT name, date_of_birth, 
       case when SYS_CONTEXT('SEC','xxx') ='ALLOW' THEN ssn END
from ...