Personally Identifiable Data (PID)

[tekpr00]

Hello All,
As part of my database security tasks, I am interested looking into my databases to list any columns and corresponding tables that might contains personal data such as name, address, phone number, DOB (Date of Birth). Basically looking into the databases to see where data such as personal names, phone numbers and DOB might have been stored.

Here is my attempt:

1)select owner, table_name, column_name from dba_tab_columns
2)where column_name in ('%NAME%','%ADDRESS%','%PHONE%','%DOB%','%PHN%'); --this did not work
3) where column_name LIKE '%NAME%' -- this worked but needs to be expanded with more criteria options. Maybe JOIN?
4) and owner not in ('SYS', 'SYSTEM').

I was wondering if you could help with line 3) to include more options.

 

[fredericofonseca]

3 - where column_name like '%xxx% or column name like '%yyy%' and so on for all keyworkds you can think of.

But be aware that depending on who designed the tables the names may not have full descriptive names and make your search quite hard. For example if you were dealing with a database from PMS (Insurance software) their field names are nothing but descriptive - e.g. they are all named D0001, D0002, D0003 and so on. This is an extreme case.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[tekpr00]

The above suggestion did not work.
It gave too many results and did not even exclude owners such as SYS, SYSTEM and WMSYS.

 

[carp]

To expand a little on Frederico's response, enclose the conditions on line three in parentheses:

where (column_name like '%xxx% OR column name like '%yyy%' OR ... )

Your filter on line 4 should exclude the undesired schemas.