Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Personally Identifiable Data (PID)

Personally Identifiable Data (PID)

Personally Identifiable Data (PID)

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.

RE: Personally Identifiable Data (PID)

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.


Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Personally Identifiable Data (PID)

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

RE: Personally Identifiable Data (PID)

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close