Have I been Pwned?

In jargon, pwn means to compromise or control, specifically another computer (server or PC), website, gateway device, or application

Multiple security breaches have made millions of passwords known to cybercriminals

Yes, possibly one of YOUR password is already known to cyber-criminals.

Add paragraph text here.

Not just passwords, recent breaches have confirmed emails, passwords, names, IP address and physical addresses have been stolen, also

• Usernames
• Dates of birth
• Genders
• Phone numbers

Just what cyber-criminals want for identify theft!

Look at numbers!!

Passwords:

• By March 2018, 501 million searchable passwords are available for download!

Add par

1. Get a password manager and learn how to use it:  1password.com, keepass.org, etc..
2. Change your passwords across the board…
3. Use this information to your advantage and:

• Show the users you’re ahead of the game
• Show the users you know what to do
• Lead the change, be the expert at work
• Don’t forget Home & Family.. They need professional advise too…

Follow this blog to show you how to download the password files and using APEX you can check if your passwords have been known to cyber-criminals..

Also with a little bit of creativity we can allow our users to check if their password has been compromised already so they can change it..

Or you could enforce a check that if its compromised then that password can’t be used in your application(s).

There are a few steps and you’ll decide what works best in your case.

–These are steps I used to load and make it available in APEX.

Use the Script to search in the operating system, posted in Github:

1. All Files are available for download at:

     https://github.com/orclapex-yyc/HaveIbeenPwned

2. Make the appropriate calls from the APEX Application

I Tested three different approaches:

1.Load data in database

2.Load data in text files on the Operating System as regular text files

3.External tables – (is not viable as its way too slow to be productive)

In my case I use Oracle XE and the amount of data exceeds the 11GB of user data available in that version.

So I decided to load just 50 million (including a non-unique index)

Response time is fantastic, I mean databases are made for that stuff!

In case you haven’t seen it this is the error when you exceed the capacity:
ORA-12953: The request exceeds the maximum allowed database size of 11 GB

Note: I used CentOS so you adjust as needed based on your OS and/or distro

Note: You’ll need 45GB of free space for this space to complete successfully.

As root:

yum install -y p7zip

mkdir /pwned

chown oracle:dba /pwned

cd /pwned

wget https://downloads.pwnedpasswords.com/passwords/pwned-passwords-ordered-2.0.txt.7z

7za e pwned-passwords-ordered-2.0.txt.7z

Connect SYS as sysdba:

@$ORACLE_HOME/rdbms/admin/catldr.sql

Using SQLWorkshop or SQLPlus or what ever you prefer:

CREATE TABLE PWNED (HASH CHAR(40), COUNTS NUMBER);

CREATE INDEX PWNED_IDX1 ON PWNED (HASH);

Content of loader1.ctl:

LOAD DATA

INFILE 'xaa.dat'

TRUNCATE

INTO TABLE pwned

(HASH terminated by ':',COUNTS )

Note: File xaa.dat is a file with only 50m rows, created using ‘split’ command in Unix. Replace with pwned-passwords-ordered-2.0.txt if you wish.

Load the data files using SQLLoader

$ sqlldr userid=schema/password control=loader1.ctl bad=loader1.bad direct=TRUE

Using SQLWorkshop or SQLPlus or what ever you prefer:

SQL> ANALYZE TABLE schema.pwned ESTIMATE STATISTICS;

For this approach to work you must enable the database server to execute Operating System commands. In Oracle XE is an undocumented feature that I will blog separately.

This is using the application provided that you load to your APEX environment and you'll see that it does the trick by running an OS Scripts via DBMS_SCHEDULER.

As root:

yum install -y p7zip

mkdir /pwned

chown oracle:dba /pwned

cd /pwned

wget https://downloads.pwnedpasswords.com/passwords/pwned-passwords-ordered-2.0.txt.7z

7za e pwned-passwords-ordered-2.0.txt.7z

As root:

yum install -y p7zip

mkdir /pwned

chown oracle:dba /pwned

cd /pwned

wget https://downloads.pwnedpasswords.com/passwords/pwned-passwords-ordered-2.0.txt.7z

7za e pwned-passwords-ordered-2.0.txt.7z

Personally I prefer this way as it doesn’t grow the database increasing the size of backups, recoveries and potentially flushing valuable cache in searching.

–This data is 100% static, seriously consider if you want it in the database

Response time is smoking fast!!

–501,636,842 rows in total

–Time to search: ~180ms

–Note: For the kind of search the data must be sorted, which it is.

It’s Demo time! - Load the application or look at the scripts.

It’s bad news that our email, password, etc. are available to cybercriminals

Be positively reactive and get a password manager and change your passwords!

For new accounts be proactive and create passwords from the password manager

Help your users and Family

–We are all in this, cybercriminals will take advantage of anyone.

With APEX I have shown you how to help your users identify if their passwords have been compromised.

Too slow to be usable in this case as it needs to scan 501million rows via OCI Layer and that will take about 4-5 minutes per search on a SSD based server.. not viable.

All available for you:
 

■All Files are available for download at:
https://github.com/orclapex-yyc/HaveIbeenPwned

■Pwned Passwords (In SHA1 HASH):
https://downloads.pwnedpasswords.com/passwords/pwned-passwords-ordered-2.0.txt.7z

■Clear text passwords:
https://github.com/danielmiessler/SecLists/blob/master/Passwords/Common-Credentials/10-million-password-list-top-1000000.txt

■Breach_compilation:
https://mega.nz/#!MuJlQRaC!Rlfonl4x33JR96m0T5N5FZh5mR3-MOdjUXEDYaUGBsE

If you have any questions please contact me and I'll be happy to help

-Gaspar