Checking if a password is valid using SQL

May 11th, 2009 by Pete

A question was posted on the Oak table mailing list some time back asking if its possible to validate a users password from within the database without creating a session. One of the replies suggested looking at my PL/SQL based password cracker. I didn't have the time to go further with it at the time as I was very busy. Then two or three of weeks ago someone else posted a question to my Oracle security forum "Verify oracle username and password using sql" that is very similar to the oaktable question.

So last weekend I spent 15 minutes extracting the code from the PL/SQL cracker and making it into an installable function. I have created a function called "testpwd" and its available in a file called testpwd.sql and also available via my Oracle security tools page. The function is easy to use. Simply create a user, grant CREATE SESSION, CREATE PROCEDURE and as SYS GRANT SELECT ON SYS.USER$ to the user and install the function. Then test it. The set up is shown here:


SQL> grant create session, create procedure to testpwd identified by testpwd;

Grant succeeded.

SQL> connect sys/oracle1 as sysdba
Connected.
SQL> grant select on sys.user$ to testpwd;

Grant succeeded.

SQL> connect testpwd/testpwd
Connected.
SQL> @testpwd

Function created.

SQL>

Then its simple to test the function. Imagine you want to verify that SCOTT's password is TIGER - which it is in this database then the function simply returns "Y" and then if a wrong password, in this case BLOB is passed the function returns "N". This means that the function can be used to verify passwords. Here is the sample test:


SQL> select testpwd('SCOTT','TIGER') from dual;

TESTPWD('SCOTT','TIGER')
---------------------------------------------------------------

Y

SQL> select testpwd('SCOTT','BLOB') from dual;

TESTPWD('SCOTT','BLOB')
---------------------------------------------------------------

N

SQL>

We can now do what is possible inside the password verification function but outside that function. If you change passwords and want to check whether the same password is reused in a password verification function this is done because both the old and new passwords are available to test. This is not the case anywhere else and sometimes it is desirable to be able to verify old and new passwords.

Hopefully this function is useful?

There has been 6 Comments posted on this article

May 11th, 2009 at 04:46 pm

Pete Finnigan says:

Dear mr. Finnigan

Great function, I really enjoy your posts.

You might want to include a few calls to the upper function before checking the username/password OR include a comment in the code, that input should be in upper case as the following test shows.

Best wishes,
Kennie Nybo Pontoppidan

SQL> create user pete identified by pete;

User created.

SQL> select testpwd('pete', 'pete' ) from dual;

TESTPWD('PETE','PETE')
---------------------------------------------------

N

SQL> select testpwd('PETE', 'pete' ) from dual;

TESTPWD('PETE','PETE')
---------------------------------------------------

N

SQL> select testpwd('pete', 'PETE' ) from dual;

TESTPWD('PETE','PETE')
---------------------------------------------------

N

SQL> select testpwd('PETE', 'PETE' ) from dual;

TESTPWD('PETE','PETE')
---------------------------------------------------

Y

May 11th, 2009 at 06:25 pm

Pete Finnigan says:

Thanks very much for your comment. Very good catch, i should have checked for that before i published it.

I have updated the code and added calls to UPPER and published it again in the same location.

Thanks again

Kind regards

Pete

May 11th, 2009 at 08:56 pm

Pete Finnigan says:

Hi Pete

Not because I'm normally the Evil Tester From Hell, but I don't think your program works with mixed case passwords. Have a look at the example below.

Best wishes,
Kennie

SQL> create user pete2 identified by "Pete2"
2 ;

User created.

SQL> select testpwd('PETE', 'PETE2' ) from dual;

TESTPWD('PETE','PETE2')
-------------------------------------------------------------------------------

N

SQL> select testpwd('PETE', 'Pete2' ) from dual;

TESTPWD('PETE','PETE2')
-------------------------------------------------------------------------------

N

SQL> select testpwd('pete', 'PETE2' ) from dual;

TESTPWD('PETE','PETE2')
-------------------------------------------------------------------------------

N

SQL> select testpwd('pete', 'pete2' ) from dual;

TESTPWD('PETE','PETE2')
-------------------------------------------------------------------------------

N

May 12th, 2009 at 08:21 am

Pete Finnigan says:

Hi Kennie,

Thanks for your comment. Here is my test of the same:

SQL> connect system/oracle1
Connected.
SQL> create user pete2 identified by pete2;

User created.

SQL> connect testpwd/testpwd
Connected.
SQL> select testpwd('PETE2','PETE2') from dual;

TESTPWD('PETE2','PETE2')
-----------------------------------------------------------------------

Y

SQL> select testpwd('PETE2','pete2') from dual;

TESTPWD('PETE2','PETE2')
-----------------------------------------------------------------------

Y

SQL> select testpwd('pete2','PETE2') from dual;

TESTPWD('PETE2','PETE2')
-----------------------------------------------------------------------

Y

SQL> select testpwd('pete2','pete2') from dual;

TESTPWD('PETE2','PETE2')
-----------------------------------------------------------------------

Y

SQL>

The difference is that i used "PETE2" as the username BUT you used "PETE" but the user you create at the top is PETE2. The function was working correctly for you because the password for PETE is not PETE2.

cheers

Pete

May 19th, 2009 at 11:24 am

Pete Finnigan says:

Hi Pete,
the function that returns the password hash value should be called hash or similar, not crack. Nomen est omen. The cracking is in the logic that uses the hash function to compare the hashed value of an input string to the oracle stored password hash. i.e.: oracle stored password hash=hash(input)

input can be:
. username
. reverse(username)
. username1..100
. replace(username,'O','0')
. replace(username,'I','1')
. replace(username,'E','3')
. candidate password
. candidate password1..99
...add your own rules

to get a long list of candidate passwords I read from an external table pointing to a large wordlist, then store them in a plsql table. This worked for me even when the list contained millions of words (eg from red database).

this is a very powerful and flexible combination, and if you are able to collect all password hashes company wide you can do company wide reporting!

Kind regards,
Andre

May 21st, 2009 at 09:57 am

Pete Finnigan says:

Hi Andre,

Thanks for your comment. I was aware of the naming as i copied and modified the function from my pl/sql based cracker script. The naming is strictly wrong as you suggest and I will ammend it next time I make an update. Paul rang me the other evening to suggest an ammendment and when i add that i will change the name.

I was aware of all of the above input modes and indeed many more that i use myself in audits. Thanks for making them available for people to use.

Kind regards

Pete

Pete Finnigan's Oracle Security Weblog

Checking if a password is valid using SQL

May 2009

Archives

Syndication