Solved

passwords in tables

Posted on 2014-02-14
13
385 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
13 Comments
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 125 total points
ID: 39858604
all tables containing "PASS"

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

Assisted Solution

by:Gerwin Jansen, EE MVE
Gerwin Jansen, EE MVE earned 125 total points
ID: 39858742
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
ID: 39858747
Sorry.... I was actually after columns (fields) called password or similar, does the above still work?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Assisted Solution

by:regmigrant
regmigrant earned 125 total points
ID: 39858778
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 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 39858792
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 32

Assisted Solution

by:awking00
awking00 earned 125 total points
ID: 39859508
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
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39859612
don't forget the short version: like '%PWD%'
0
 
LVL 32

Expert Comment

by:awking00
ID: 39859641
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 37

Expert Comment

by:Geert Gruwez
ID: 39864207
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
ID: 39864263
have you ever in your experience as DBA's found actual passwords plain text in a table?
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39864368
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 37

Expert Comment

by:Geert Gruwez
ID: 39864378
> 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
ID: 39864937
ouch!
0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Display SQL maintenance plan SQL Code 3 48
Deny sending to more than 2 recipients in Exchange 2016 2 51
What is needed to become a DBA? 7 56
When are cursors useful? 8 62
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

710 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