Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • 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 GruwezOracle 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 GruwezOracle 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 GruwezOracle 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 GruwezOracle 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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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