Automatically Protecting PII Data Columns with Transparent Data Encryption


Oracle’s Database Security Assessment Tool (DBSAT), now contains a nifty little feature called the Discoverer. According the their documentation, “The Discoverer executes SQL queries and collects data from the system to be assessed, based on the settings specified in the configuration files. It does this primarily by querying database dictionary views. The collected data is then used to generate a Database Sensitive Data Assessment Report in HTML and CSV formats.” In other words, Discoverer runs a series of queries against your data dictionary using several preset (but configurable) regular search expressions, looking for columns that are likely to contain Personally Identifying Information (PII) like social security numbers, addresses, personal names, phone numbers, credit card, financial and health data, etc. and generates a very nice report that summarizes the findings.

That’s great if all you need is a report, but what if you have a lot of potential PII data and you need to enforce some additional safeguards on it? How do I make this information actionable, and better yet how do I automate that action as much as possible?

It turns out this isn’t too complicated if I write a PL/SQL procedure to execute the same searches as the DBSAT Discoverer. The procedure (and related configuration tables) should be owned by a schema which has – at a minimum – the SELECT ANY DICTIONARY privilege. This will allow it to generate the DDL script to encrypt columns for use by another user with higher privileges. If you wish the procedure to also execute the DDL automatically, then the schema should also have the “ALTER ANY TABLE” privilege.

All code described in this post is available for download here.

The first step is to convert the search parameters into something that I can use programatically. The search criteria for Discoverer are contained in a configuration file named “sensitive_en.ini”. The documentation includes instructions on how to modify the parameters for your own needs when running DBSAT; I took it a step further and loaded the search parameters into a table. My table definition looks like this:

create table pii_sensitive_types
(
  sensitive_type_name  varchar2(30 char)        not null,
  col_name_pattern     varchar2(256 char)       not null,
  col_comment_pattern  varchar2(256 char)       not null,
  sensitive_category   varchar2(30 char),
  enforced             varchar2(1 char)         default 'Y'
);

comment on table pii_sensitive_types is 'This table contains sensitive types and their related regular expressions and categories';
comment on column pii_sensitive_types.sensitive_type_name is 'Name of the Sensitive Type';
comment on column pii_sensitive_types.col_name_pattern is 'REGEX used to search Column Names';
comment on column pii_sensitive_types.col_comment_pattern is 'REGEX used to search Column Comments';
comment on column pii_sensitive_types.sensitive_category is 'Sensitive Category to which the sensitive type belongs to';

create unique index pii_sensitive_types_pk on pii_sensitive_types
(sensitive_type_name);

alter table pii_sensitive_types add (
  constraint pii_sensitive_types_pk
  primary key
  (sensitive_type_name)
  using index pii_sensitive_types_pk
  enable validate);

There are 75 default search criteria included in DBSAT. Like me, you probably don’t need all of them, but I put them all into the search table as examples so that you can compare the results to those of DBSAT.

set define off;
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('MEDICAL_CHART_NUMBER', 'MEDICAL_?CHART', 'Medical Chart', 'Health Data', 'N');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('NEXT_OF_KIN', 'NEXT.*KIN', 'Next.*(of)?.*Kin|\bNOK\b', 'Health Data', 'N');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('JOB_TITLE', '(^JOB.*(TITLE|PROFILE|POSITION|RANK|LEVEL)$)|^POSITION|CURR.*JOB|JOB.*CURR|JB.*TITLE', 'Job (Title|Profile|Position|Rank|Level)|Current Job', 'Job Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('INCOME', '= SALARY|INCOME|WAGE|REMUNERATION|^SAL$|COMPENSATION|COMP_?RATE|ANNUAL_?RT', 'Salary|Income|Wage|Remuneration|Compensation|Annual Rate', 'Job Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('VARIABLE_INCOME', 'BONUS|REWARD|^COMP$|COMMISSION|^COMM$|^VARIABLE$|COMP_SUMMARY|TIPS$', 'Bonus|Reward|Commission|Variable|Tips', 'Job Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('STOCK', '^(?!.*(PROD|WH|SUP|MAX|MIN|IN)).*STOCK$|ST.*AWARD|SHARES.*GRANTED', '^(?!.*(Prod|Wh|Sup|Max|Min|In)).*Stock|\bRSU\b|Restricted Stock Unit|Shares', 'Job Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('HIRE_DATE', '((ADMISSION|HIRE).*(DATE|DT))|(DT|DATE).*HIRE', '(Admission|Hire).*date', 'Job Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('TERMINATION_DATE', 'TERM.*(DATE|DT)|(DATE|DT).*TERM', 'Term.*Date|Date.*Term', 'Job Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('FULL_NAME', '^(PERSON|FULL).*NAME$', '(Full|Person).*Name', 'PII', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('FIRST_NAME', '(^FNAME$)|((FIRST|GIVEN).*NAME$)', '(First|Given|Cust).*Name', 'PII', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('LAST_NAME', '(^LNAME$)|((LAST|FAMILY|SUR|PATERNAL).*NAME$)', '(Last|Family|Sur|Paternal).*Name', 'PII', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('SOCIAL_SECURITY_NUMBER', 'SOC.*SEC|^SSN$|SSID', 'Social Security Number', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('MOTHERS_NAME', '^(MOTHER.*NAME|NAME.*MOTHER)$', 'Mother.*Name|Name.*Mother', 'PII', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('FATHERS_NAME', '^(FATHER.*NAME|NAME.*FATHER)$', 'Father.*Name|Name.*Father', 'PII', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('MAIDEN_NAME', '^MAIDEN.*NAME$', 'Maiden Name', 'PII', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('MOTHERS_MAIDEN_NAME', 'MMN| ^MOTHERS?_?MAIDEN', 'Mother''?s maiden', 'PII', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('STREET', 'STREET|^ST$|AVENUE|ROAD|ALLEY|BOULEVARD|PARKWAY|PLAZA|POINT|VALLEY', 'Street.*(Address)?', 'PII - Address', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('CITY', '^CITY$', 'City', 'PII - Address', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('POSTAL_CODE', '^ZIP|POST.*CODE', '^Zip|Post.*Code', 'PII - Address', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('MAIL_STOP', '^MAIL.*STOP$', 'Mail Stop', 'PII - Address', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('STATE', '^STATE$|^ST$', 'State', 'PII - Address', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('COUNTY', 'COUNTY', 'County', 'PII - Address', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('COUNTRY', 'COUNTRY', 'Country', 'PII - Address', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('FULL_ADDRESS', '^(?!IP|MAC|EMAIL).*ADDRESS$|^ADDRESS$', '^(?!IP|MAC|EMAIL).*ADDRESS$', 'PII - Address', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('EMAIL', 'EMAIL|MAIL', 'EMAIL|MAIL', 'PII', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('PHONE', 'PHONE|^TEL|^CELL|MOBILE|((WORK|OFFICE|CONTACT).*(NUM|NO|NBR))', 'Phone|Telephone|Cellphone|Mobile N|Work N|Office N|Contact N', 'PII', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('FINGERPRINT', 'FINGER.*PRINT', 'Finger.*print', 'PII', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('PHOTO', '^(?!.*(PRODUCT|PRDT?)).*(PHOTO)', '^(?!.*(Product|Prdt?)).*(Photo)', 'PII', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('NATIONAL_IDENTIFIER', 'NATIONAL.*ID', 'National.*Id', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('NATIONAL_INSURANCE_NUMBER', 'NATIONAL.*INS|^NI.*NO', 'National.*Ins|\bNINO\b', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('SOCIAL_INSURANCE_NUMBER', 'SOC.*INS|^SIN$', 'Social Insurance Number', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('UNIQUE_POPULATION_REGISTRY_CD', 'UNIQUE.*POPULATION.*CODE|CURP|PERSONAL.*CODE|MEXICO.*CODE', 'Unique Population Registry Code|CURP', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('WORK_PERMIT', '^WORK_?PER', 'Work Permit', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('DRIVERS_LICENSE_NUMBER', '(DL.*NUM)|(DRIV.*(LIC|NUM|ID|NO|NBR))', 'Driv.*Lic', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('PASSPORT_NUMBER', '^PASSPORT.*(NUM|NO|NBR)|^PASSPORT$', 'Passport', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('VISA_NUMBER', '^VISA|^CONTROL_?N|^CASE_?N', 'Visa Number|Control Number|Case Number', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('TAX_IDENTIFICATION_NUMBER', 'TAX.*(ID|NO|NUM|NBR)|^TIN$|^ITIN$|^ATIN$|^EIN$|^PTIN$', 'Tax(payer)? Id', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('NATIONAL_TAXPAYER_IDENTIFIER', 'NATIONAL_?TAX(PAYER)?', 'National Taxpayer', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('VOTER_IDENTIFICATION_NUMBER', '(ELECTORAL|VOTER).*(ID|NO|NUM|NBR)', '(Electoral|Voter).*(ID|No|Num|Nbr)', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('EMP_ID', '^EMP.*(ID|NO|NUM|NBR)', 'Employee (ID|NO|NUM|NBR)', 'Job Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('STUDENT_IDENTIFICATION_NUMBER', '^STUD.*(ID|NO|NUM|NBR)', 'Student.*(ID|No|Num|Nbr)', 'PII - IDs', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('USERID', '^HANDLE|^USER|^HASHTAG$|^LOGIN|USER_?NAME|SCREEN_?NAME', 'HANDLE|USER|HASHTAG|LOGIN|USER_?NAME|SCREEN_?NAME|Twitter|Tweet', 'PII - IT Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('PASSWORD', '^PWD$|PSWD|PASSW(OR)?D|^CREDENTIAL$|^REG.*PASS$|^REG.*PWD$|^OLD_?PWD$|^OLD_?PASS$|^NEW_?PWD$|^NEW_?PASS$', 'PWD|PSWD|Passw(or)?d|Credential', 'PII - IT Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('IP_ADDRESS', '^IP$|_IP_?ADD', 'IP.*ADD', 'PII - IT Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('MAC_ADDRESS', '^MAC$|MAC_?ADD', 'MAC.*ADD', 'PII - IT Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('COOKIE', 'COOKIE', 'Cookie', 'PII - IT Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('LOCATION', '^LOCATION|GPS|^SDO|^ST_GEOM|^XY|^COORD|DATUM$', 'Location|GPS|Spatial|Geom|XY|Coord|Geo.*Spatial', 'PII - IT Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('IMEI', '^IMEI|MOBILE.*ID', 'IMEI', 'PII - IT Data', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('GENDER', '^SEX|_SEX$|^GENDER', 'Sex|Gender', 'PII-Linked', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('AGE', '^AGE$', '\bAge\b', 'PII-Linked', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('BIRTHDATE', 'DOB|BIRTH.*DATE|DATE.*BIRTH', '\bDOB\b|Birth.*Date|Date.*Birth', 'PII-Linked - Birth Details', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('BIRTHPLACE', 'BIRTH.*PLACE|PLACE.*BIRTH', 'Birth.*place|Place.*birth', 'PII-Linked - Birth Details', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('NATIONALITY', 'NATIONALITY|HOME.*COUNTRY|^ORIGIN$', 'Nationality|Home.*Country|\bOrigin\b', 'PII-Linked', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('COUNTRY_OF_CITIZENSHIP', '^CITIZENSHIP|COUNTRY.*CITIZENSHIP', 'Citizenship|Country.Citizenship', 'PII-Linked', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('RACE', '^RACE|ETHNICITY', 'Race|Ethnicity', 'PII-Linked', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('RELIGION', 'RELIGION|CHURCH', 'Religion|Church', 'PII-Linked', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('MARITAL_STATUS', 'MARRIED|MARITAL.*STATUS', 'Married|Marital status', 'PII-Linked', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('POLITICAL_PARTY', '^POLITIC(AL)?', 'Political Party|Political Opinion', 'PII-Linked', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('TRADE_UNION_MEMBERSHIP', 'TRADE.*UNION|UNION.*NAME', 'Trade.*Union|Union.*Name', 'PII-Linked', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('SEXUAL_PREFERENCE', '^SEX_?PREF|SEX_?P', 'Sexual Pref', 'PII-Linked', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('CRIMINAL', '^CRIMINAL|^CRIME|^CONVICTED$|DRUG_TEST', 'Criminal|Crime|Convicted|Drug Test', 'PII-Linked', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('CREDIT_CARD_NUMBER', '^(?!.*(DATE|DT|ISSUE)).*(CREDIT|DEBIT).*CARD|^PAN$|PAYMENT_ACCT|^CCN?$|CC_CARD|CCI_N', '(Credit|Debit|Payment) Card', 'Financial Data - PCI', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('CARD_SECURITY_PIN', 'PIN$', '\bPIN\b', 'Financial Data - PCI', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('CARD_SECURITY_CODE', 'SEC.*CODE|CC.*CODE', 'Security Code', 'Financial Data - PCI', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('CARD_EXPIRATION_DATE', 'EXP.*DATE|DATE.*EXP', 'Exp.*Data|Date.*Exp', 'Financial Data - PCI', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('BANK_ACCOUNT_NUMBER', 'BANK.*ACC|CHECKING|SAVINGS', 'Bank Account|Checking|Savings', 'Financial Data - Banking', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('BANK_ROUTING_NUMBER', '(ROUTING|TRANSIT).*(NO|NUM|NBR)', 'Bank Routing|(Routing|Transit) Number', 'Financial Data - Banking', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('IBAN', 'IBAN', 'IBAN', 'Financial Data - Banking', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('BANK_NAME', '^BANK', 'Bank Name', 'Financial Data - Banking', 'Y');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('HEIGHT', '^(?!HTML|BITMAP|APPLET|CHART|ICON|IMAGE|IMG|SCREEN|DIALOG|DISPLAY|PAGE|UNIT|HEADER|FOOTER|CEILING|WINDOW|MIN_SIZE'||CHR(13)||CHR(10)||'|MAX_SIZE|THUMBNAIL).*HEIGHT$|^HEIGHT$|^HT$', '^(?!HTML|BITMAP|APPLET|CHART|ICON|IMAGE|IMG|SCREEN|DIALOG|DISPLAY|PAGE|UNIT|HEADER|FOOTER|CEILING|WINDOW|MIN_S'||CHR(13)||CHR(10)||'IZE|MAX_SIZE|THUMBNAIL).*Height', 'Health Data', 'N');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('WEIGHT', '(PATIENT_?|PERSON_?).*WEIGHT|^WEIGHT$|^WT$', '(PATIENT_?|PERSON_?).*WEIGHT', 'Health Data', 'N');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('HEALTH_INSURANCE_NUMBER', '((INSURANCE|PATIENT).*(NO|NUM|ID|NBR))|PAYER', 'Health Insurance N', 'Health Data', 'N');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('MEDICAL_INFORMATION', 'MEDICAL.*(INFO|DETAILS|CONDITION)', 'Medical (Info|Detail|Cond)', 'Health Data', 'N');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('DISABILITY', 'DISABILITY|HANDICAP|PROF_DISEASE|CHALLENGED', 'Disability|Handicap|Professional Disease|Challenged', 'Health Data', 'N');
Insert into PII_SENSITIVE_TYPES
   (SENSITIVE_TYPE_NAME, COL_NAME_PATTERN, COL_COMMENT_PATTERN, SENSITIVE_CATEGORY, ENFORCED)
 Values
   ('GENETIC', '^RNA$|^DNA$|GENETIC', 'Genetic|\bDNA\b|\bRNA\b|Ribonucleic|Deoxyribonucleic', 'Health Data', 'N');
COMMIT;

One other thing that DBSAT allows me to do is to exclude specific schemas, tables, and columns from my scan. In DBSAT this is handled through a second configuration file. In my example, we’re going to create another table for exclusions. Exclusions allow me to fine tune my scan to eliminate false positives. The exclusion table definition looks like this:

create table pii_excluded_cols
(
  owner       varchar2(30 char)                 not null,
  table_name  varchar2(30 char)                 not null,
  column_name varchar2(30 char)                 not null,
  excluded_by varchar2(50 char) default user    not null,
  excluded_on date              default sysdate not null,
  comments    varchar2(4000 char)
);

Now that I have search and exclusion criteria, I need to do something with them. In my example, I would like to generate a script that can be used to encrypt each identified column using Oracle’s Transparent Data Encryption (TDE). Note that TDE requires that you have the Advanced Security Option (ASO) licensed for your deployment. The following procedure executes a search against the data dictionary, using search criteria identified as being enforced, and excluding columns that I identify as false positives:

create or replace procedure scan_schema 
    (p_schema in varchar2 default '%', 
     p_mode in varchar2 default 'view', 
     p_pii_category in varchar2 default '%')
as
    -- declare local variables
    l_schema varchar2(30);
    l_mode varchar2(10);
    l_sql varchar2(1000);

    l_pii_search pii_sensitive_types%rowtype;
    l_col_info dba_col_comments%rowtype;

    -- cursor pii_search returns all search criteria
    cursor pii_search 
        (c_pii_category in varchar2)
    is
    select sensitive_type_name,
           col_name_pattern,
           col_comment_pattern,
           sensitive_category,
           enforced
      from pii_sensitive_types
     where lower(sensitive_category) like lower(c_pii_category)
       and lower(enforced)='y'
     order by sensitive_category, sensitive_type_name;

    -- cursor pii_columns returns all columns with potential PII data
    cursor pii_columns 
        (c_schema in varchar2, 
         c_col_name_pattern in varchar2, 
         c_col_comment_pattern in varchar2)
    is
    select c.owner,
           c.table_name,
           c.column_name,
           c.comments 
      from dba_col_comments c
      join dba_objects o on (o.object_type='TABLE' and o.owner = c.owner and o.object_name = c.table_name)
     where c.owner=l_schema 
       and ( regexp_like(c.column_name,c_col_name_pattern) or regexp_like(c.comments,c_col_comment_pattern)) 
       and c.table_name||'.'||c.column_name not in 
           (select e.table_name||'.'||e.column_name 
              from dba_encrypted_columns e 
             where e.owner=c_schema
             union
            select x.table_name||'.'||x.column_name
              from pii_excluded_cols x
             where x.owner=c_schema)
             order by c.owner, c.table_name, c.column_name;

begin
    -- Make user-supplied inputs case-insensitive
    l_mode   := lower(p_mode);
    l_schema := upper(p_schema);

    -- Enable script output
    dbms_output.enable;

    -- Get the list of search criteria for the requested PII type
    open pii_search (p_pii_category);

    -- For each criteria, search the data dictionary for matches
    loop
        fetch pii_search into l_pii_search;
        exit when pii_search%notfound;

        -- Print the current search criteria type
        dbms_output.put_line(l_pii_search.sensitive_category||'::'||l_pii_search.sensitive_type_name||':');

        begin
            -- Get the list of matching columns for the current search criteria
            open pii_columns (l_schema, l_pii_search.col_name_pattern, l_pii_search.col_comment_pattern);

            loop
                fetch pii_columns into l_col_info;
                exit when pii_columns%notfound;

                -- For each matching column build a SQL statement to encrypt the data using TDE.
                -- The DDL statements uses the "no salt" option by default in the event that the 
                -- affected column is included in an existing index
                l_sql := 'alter table '||
                         l_col_info.owner||'.'||l_col_info.table_name||
                         ' modify('||l_col_info.column_name||
                         ' encrypt using ''AES256'' no salt)';

                -- Print the generated SQL statement
                dbms_output.put_line(' '||l_sql);

                -- If the user has specified the "encrypt" option, execute the SQL statement
                if l_mode = 'encrypt' then
                    execute immediate l_sql;
                end if;
            end loop;

            close pii_columns;
        end;
    end loop;

    close pii_search;
end scan_schema;

The procedure can now be executed against any schema in my database. It can generate a script of DDL commands that I can save, review, and run at a later time, or it can execute the DDL automatically as a part of the scan. My procedure could easily be expanded to include a number of other actions that might be desirable to protect PII data, such as create generic Virtual Private Database (VPD) or Fine Grained Auditing (FGA) policies, and generate a log or an e-mail of any action taken. It could even be used to generate an automated report  – after a regularly scheduled product release, for instance – to scan the database for any new potential PII columns, take default action as deemed appropriate for a specific system, and alert the DBA to the change.

Logo Design by Logo Open Stock

One thought on “Automatically Protecting PII Data Columns with Transparent Data Encryption

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.