Another massive annoyance with natural keys - privacy. If your table's primary key contains personal information, that PII now infects every other table that holds a foreign key to that table.
I have not seen clear guidelines about whether an organization's surrogate keys for persons are considered PII. (And this ambiguity has frustrated me for some time as I am unclear whether to take an aggressive or conservative view on labelling PII where I work.) When I have read the guidelines, it seems ambiguous but on balance I think it disagrees with your implied claim (that PII is not present in a table that uses a surrogate foreign key to a person/user.)
The definition of PII per NIST https://nvlpubs.nist.gov/nistpubs/legacy/sp/nistspecialpubli... is:
"Any information about an individual maintained by an agency, including (1) any information that can be used to distinguish or trace an individual‘s identity, such as name, social security number, date and place of birth, mother‘s maiden name, or biometric records; and (2) any other information that is linked or linkable to an individual, such as medical, educational, financial, and employment information."
A surrogate key associated with a person is arguably "any other information that is linked or linkable to an individual" meaning that all tables containing the surrogate key remain PII and remain "infected". It's true the surrogate key only allows linkability within the context of the data ecosystem in which it resides, but such distinctions (of "internal" to the system vs "external from the system") are not made in the language of the definition. Additionally, from a pure risk and PII disclosure impact, usually the whole database gets dumped, not just the "non-person" tables. If you have financial/medical transactions in table "A" and a personal numeric ID linking data to a person table "B", both tables contain PII, right?
From a privacy standpoint, if you can SQL JOIN the data to trace the person involved either within your system or even data reasonably obtainable outside your system, (or if an attacker can), it's PII.
Intranet IP addresses are called "linked PII" in section 3.2.2 of the above NIST guidelines for example, and NIST does define some related terms that would seem to apply to surrogate keys like: * Distinguishable Information: Information that can be used to identify an individual. * Linkable Information: Information about or related to an individual for which there is a possibility of logical association with other information about the individual. * Linked Information: Information about or related to an individual that is logically associated with other information about the individual.
As a data engineer, the above interpretation means PII is in a zillion tables and labeling a table with a boolean indicator yes/no isn't that helpful. But as a policy person, NIST seems to be recommending gauging PII more at the system (not table) level and with a PII Confidentiality Impact Level of low/medium/high that takes into account the context and overall risk and that seems sensible. From a data cataloging standpoint (gauging what's "infected" to use your term), I think it's probably helpful to identify particular transactional tables or personal table as having "high" PII disclosure impact vs "low"; the presence of "infection" from a virus ("PII") is mostly irrelevant in a sufficiently large system where viruses/some PII is inevitable but what matters is the severity/impact. A zillion tables will be "low" (e.g. if most tables have audit column saying which user last changed a particular record) but certain transactional or user tables may be "high" and should be recognized as such and the focus of any risk discussions with the business or legal or breach notifications to customers or what have you.
Going back to your original point, I don't think the choice of natural vs surrogate key impacts the PII risk of the system or even its individual tables. I would slightly concede that a surrogate key (which in general I am in favor of) would make it easier to reduce a particular individual's PII from a system by concentrating it in one or a small number of tables with names, etc. which might be helpful for enabling GDPR right to be forgotten or something. But the degree of that PII elimination from a system by blanking out or archiving a particular user/person record is not necessarily reducing the PII for them to 0 at least definitionally unless the transactional records themselves are also removed as the AOL 2006 search data scandal demonstrated (where a woman identified solely by a surrogate key was able to be identified from her search term transactions alone.) (Legally there would appear to be carveouts around transactional deletion for some financial transaction records and backups, but IANAL...)
I've worked on systems in the biotech space where certain tables and certain columns in our dataset were considered PHI (personal health information), and others weren't. An auto-incrementing user ID does not qualify as PHI under HIPAA. Whereas a person's name or email address most certainly does (since, the very fact that a particular person had received lab testing at a particular lab, is itself considered PHI.)
Those PHI bits of data were scrubbed from the dataset that most regular employees had access to. Casually allowing all employees to have access to PHI would not have been compliant with the law.
The restrictions on what can even be logged (when there's a system error, for example) was very much controlled. "User 12345 experienced <some exception>" was perfectly fine (and greatly aided operational investigation, and customer support.)
One of the first 17 things might be a surrogate key ("account number") in one's system but if you look through the others, the rest are things like name, SSN, biometrics, IP#s, etc, which are definitely not surrogate keys.
The "OR" language makes the statistical expertise (and "principles" of privacy) irrelevant if you avoid the 18 things; that avoidance forms a "safe harbor" of sorts so you don't have to do any heavy thinking/lifting.
The 18th ("(R)") element of what is considered PHI does seem to refer to surrogate keys but in a manner which creates a clear carve-out/safe-harbor for them not being PHI. That 18th form of PHI is "Any other unique identifying number, characteristic, or code, except as permitted by paragraph (c) of this section;"
But that paragraph (c) section seems indicate identifiers such as surrogate integer/guid keys kept within a system, as long as they are 1) not derived from an individual's information (ie like integer or UUID surrogate keys) and 2) which are maintained solely in the system are not considered as element-18-"R"-PHI:
"(c) Implementation specifications: Re-identification. A covered entity may assign a code or other means of record identification to allow information de-identified under this section to be re-identified by the covered entity, provided that: (1) Derivation. The code or other means of record identification is not derived from or related to information about the individual and is not otherwise capable of being translated so as to identify the individual; and (2) Security. The covered entity does not use or disclose the code or other means of record identification for any other purpose, and does not disclose the mechanism for re- identification."
By my reading, a surrogate key maintained within a system is thus clearly not PHI under HIPAA. I never looked at the details of HIPAA until today since it hasn't applied much to my data and have been focused more on PII/GDPR. I appreciate you describing the context of your remarks.
Surely SSN is a surrogate key? They are not naturally derived. The early ones were serial (i.e. an auto-incrementing field) and more recent ones are randomly generated (i.e. a UUID).
SSNs are created within the organization. Maybe not within your organization, but nobody is talking about you. They are a surrogate key.
If you use data as a key, then that is a natural key, if you invent a value to use as an identifier, that is an artificial or surrogate key.
If an API provides you an ID for a record, that is data. If you use it as a key, that is also a natural key in your system.
It may not be your surrogate key, but it is someone's!
But under HIPAA, even your own employees need to have a specific, documented justification for accessing customer PHI. If they can do their job just as well by only accessing a non-PHI dataset, you're required by law to only allow them access to a scrubbed, non-PHI dataset.
> The controller shall implement appropriate technical and organisational measures for ensuring that, by default, only personal data which are necessary for each specific purpose of the processing are processed. That obligation applies to the amount of personal data collected, the extent of their processing, the period of their storage and their accessibility. In particular, such measures shall ensure that by default personal data are not made accessible without the individual's intervention to an indefinite number of natural persons.