Solved

passwords in tables

Posted on 2014-02-14
13
360 Views
Last Modified: 2014-03-02
is there a SQL query you could run over an oracle database to list all tables with fields that begin with "password" or "pass"?
0
Comment
Question by:pma111
  • 4
  • 3
  • 2
  • +2
13 Comments
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 125 total points
Comment Utility
all tables containing "PASS"

select * from dba_tab_columns
where column_name like '%PASS%'
order by owner, table_name, column_name
0
 
LVL 37

Assisted Solution

by:Gerwin Jansen
Gerwin Jansen earned 125 total points
Comment Utility
Minor correction:
select distinct(table_name) from dba_tab_columns
where column_name like 'PASS%'
order by table_name;

Open in new window

(asked for tables and begin with)
0
 
LVL 3

Author Comment

by:pma111
Comment Utility
Sorry.... I was actually after columns (fields) called password or similar, does the above still work?
0
 
LVL 19

Assisted Solution

by:regmigrant
regmigrant earned 125 total points
Comment Utility
yes that's what it does;  dba_tab_columns is a table containing all the columns in the database

%PASS% will find all column names containing 'PASS'
PASS% will find those starting with 'PASS'

If you want the column names as well as the table names use:-

select distinct(table_name), column_name from dba_tab_columns
where column_name like 'PASS%'
order by table_name;
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
Comment Utility
Just the columns:
select distinct(column_name) from dba_tab_columns
where column_name like 'PASS%'
order by column_name;

Open in new window

But what is it  you really need? There are lots of variations on the query above, start with select * (in the first comment), selecting colums you've asked for, etc.
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 125 total points
Comment Utility
Assuming you are truly looking for password columns, you might want to expand your like phrase to LIKE 'PASSW%' since just PASS would also include columns like PASSOVER, PASSTHROUGH, PASSIVE, etc.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
don't forget the short version: like '%PWD%'
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
I agree PWD should be included.
WHERE column_name like 'PWD%' OR column_name like 'PASSW%'
You could also use regular expressions
WHERE regexp_like(column_name,'(^PWD|^PASSW)')
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
and all the translations ... in the languages being used in the database
not all columns have an english name

don't ask for the chinese translation of password, as that's one of the languages i don't speak ... yet
0
 
LVL 3

Author Comment

by:pma111
Comment Utility
have you ever in your experience as DBA's found actual passwords plain text in a table?
0
 
LVL 19

Expert Comment

by:regmigrant
Comment Utility
I have a current issue with a high profile project where the passwords are all stored in a table called User_passwords, with a list of user names and a column "password" in plain text. A brief play highlighted that most of them also used the same password for their domain and mail logins - so yes it definitely happens
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
> found actual passwords plain text in a table
yes, off course
you'll probably get a hit ratio of 100% for that question ... :)
0
 
LVL 3

Author Comment

by:pma111
Comment Utility
ouch!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Join SQL view with APEX item as the join condition 4 39
Mysql not caching queries 4 45
null value 14 57
dates - loop 12 39
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now