• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

passwords in tables

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
pma111
Asked:
pma111
  • 4
  • 3
  • 2
  • +2
4 Solutions
 
Geert GOracle dbaCommented:
all tables containing "PASS"

select * from dba_tab_columns
where column_name like '%PASS%'
order by owner, table_name, column_name
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
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
 
pma111Author Commented:
Sorry.... I was actually after columns (fields) called password or similar, does the above still work?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
regmigrantCommented:
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
 
Gerwin Jansen, EE MVETopic Advisor Commented:
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
 
awking00Commented:
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
 
Geert GOracle dbaCommented:
don't forget the short version: like '%PWD%'
0
 
awking00Commented:
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
 
Geert GOracle dbaCommented:
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
 
pma111Author Commented:
have you ever in your experience as DBA's found actual passwords plain text in a table?
0
 
regmigrantCommented:
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
 
Geert GOracle dbaCommented:
> found actual passwords plain text in a table
yes, off course
you'll probably get a hit ratio of 100% for that question ... :)
0
 
pma111Author Commented:
ouch!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now