Solved

passwords in tables

Posted on 2014-02-14
13
367 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 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 37

Assisted Solution

by:Gerwin Jansen
Gerwin Jansen 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
 
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 37

Expert Comment

by:Gerwin Jansen
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DATE Column Space 11 65
Web Service from a stored procdure oracle 10 49
join 2 views with 5 conditions 3 46
Exchange 2010 - Best practice MDB Data size 8 58
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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 video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

863 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

18 Experts available now in Live!

Get 1:1 Help Now