I'm writing an Oracle audit script where I need to find users in a database who have set their passwords to be the same as their username, e.g. dbaadmin/dbaadmin. The query would need to get the list of users in the database & then check if their passwords is same as their respective usernames.

Can anyone help me out with this query?

PS: I'm a Database newbie.

  • 1
    Which version of Oracle are you using? The hashing algorithm changed after 10g. – Colin 't Hart May 18 '15 at 9:02

What if user has login "SCOTT" and password "ScoTt"? Do you still need to find this one?

I think what you really need is to set password_verify_function in users profile. See http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authentication.htm#i1007341 for more info.


I don't think this is possible for current versions of Oracle. What I understand happens is that Oracle generates an instance-specific salt and adds this to the passwords being hashed using the SHA1 algorithm, the result of which is stored as the hashed password. See for example http://marcel.vandewaters.nl/oracle/security/password-hashes for information about this algorithm.

This means that password hashes are installation specific, and I don't think it's possible to generate the expected hash value for a given username/password combination.

What you need to do is as @elfcheg suggests to add a password_verify_function on all profiles, or to a single profile, and assign this profile to all users.


Here you can find how to generate the password hash. (This algorithm worked both on version 11.2 and 12.1 when I tried with some simple examples.) The next challenge is - as already mentioned -, to try all possible combinations of lowercase and uppercase letters in a password, if you want to cover everything.

This will not be a single query, but some PL/SQL code that would run for quite a long time depending on the length of the username.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.