Solved

duplicate passwords for oracle accounts

Posted on 2014-02-18
13
443 Views
Last Modified: 2014-03-03
could you identify oracle accounts with the same password based on the hash value alone (without cracking it). i.e if 2 accounts have the same hash value, does that ultimately mean they have the same password? Would there be a query for 10g and 11g to identify accounts with a duplicate hash value (and presumably duplicate password).
0
Comment
Question by:pma111
  • 7
  • 2
  • 2
  • +2
13 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
Comment Utility
Hashed passwords are different so the query doesn't mean anything

Do a simple test?  Create two users using the same password and look in DBA_USERS at the generated hashes.
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 167 total points
Comment Utility
I did a quick test:


SYS@dev> create user fred identified by password1;  

User created.

SYS@dev> create user barney identified by password1;

User created.

SYS@dev> select name, spare4 from sys.user$ where name in ('FRED','BARNEY');

NAME
------------------------------
SPARE4
------------------------------------------------------------------------------------------------------------------------------------
BARNEY
S:2A0B33A8EB455312B33E39C8E4E8939D0A8AAFDA8E433DC72E5A0BC772A9

FRED
S:608FBA3B1DDB73CFBE7CED807DBAD560CA6A9DAC5FBFC14AA0AED6DFA9E3

Open in new window


Based on the above, I'd have to say that different usernames with the same passwords are going to generate different hash keys for the password.

The above was tested in 11gR2.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 166 total points
Comment Utility
If you are using 10g or lower style passwords then it's possible to detect patterns
but only in contrived circumstances


myuser/password
my/userpassword
myuserpass/word

all hash to the same value in 10g and lower
as you can see there is a pattern, but the passwords are still unique


in 11g and higher passwords are give a unique salt and don't duplicate hashes when generated by the system

In 11g databases, you can look at dba_users to determine which password types you are using.



For more information...


http://www.experts-exchange.com/Database/Oracle/A_855-How-Oracle-Stores-Passwords.html
0
 
LVL 3

Author Comment

by:pma111
Comment Utility
dont have an oracle database to test it on. not sure if you are saying in your reply "the same password could generate a different hash".. I would have thought you could do a count like clause on the hash column and if >1 the users have the same password.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Same password, different accounts, different hash.

Select with count will not work.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
I would have thought you could do a count like clause on the hash column and if >1 the users have the same password.


No, hashes are unique to the user/password combination in 10g  (subject to hashing size limits of course)

System generated hashes are unique in 11g even over time (again, subject to hashing size limits)
For example,  set an 11g password, check the hash, then set the same user to the same password and you'll get another distinct hash.
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 73

Expert Comment

by:sdstuber
Comment Utility
>>>  different usernames with the same passwords

as noted above,  in 11g, even for the same user and the same password, the hash will change over time.  The method for generating an 11g salt hasn't been published that I know of, but is, evidently, based on time.

Once generated though, it's easy to extract,  the salt is the last 20 characters (hex display of 10 bytes)  of the spare4 string.
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
If you want to test such things, you can download Oracle XE for free from http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html to play around with these kinds of features.

You can learn a lot by having a system that you can freely muck around in :)
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
So, in 11g, since the username  is NOT part of the hash, yes, if two users have the exact same hash then they have the same password.

Note, this is exceedingly unlikely without contrivance by someone with elevated privileges.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
For an example of what I mean by "contrivance by someone with elevated privileges".

C:\>sqlplus system@mydb

SQL> create user testuser identified by pa55w0rd;

User created.

SQL> select spare4 from sys.user$ where name like 'TESTUSER%';

SPARE4
--------------------------------------------------------------------------------
S:DA4F778E02246E9A59099D476E54F6A9BF07C34153EBB07E2FAE8905CA22

SQL> create user testuser2 identified by values 'S:DA4F778E02246E9A59099D476E54F6A9BF07C34153EBB07E2FAE8905CA22';

User created.

SQL> grant create session to testuser;

Grant succeeded.

SQL> grant create session to testuser2;

Grant succeeded.

SQL> connect testuser/pa55w0rd@mydb
Connected.
SQL> connect testuser2/pa55w0rd@mydb
Connected.
SQL> exit

Open in new window


I used the previously generated 11g hash as the value to assign to the new second user.
In 10g this would not work because the username is part of the hash.

But, as noted above,  if you simply assign the password directly to both users, they will each get their own hash.

So, in general, no, you can't use count of hash to find duplicates; but if you do find them, it would be interesting in 11g, because it probably means your dba has been messing around.
0
 
LVL 23

Expert Comment

by:David
Comment Utility
Then there are the DBAs who use an identical set of users and passwords on multiple hosts; yea even production and development....
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Checking for copied passwords across systems can query dba_users/sys.user$ on 10g regardless of whether they were set by copying the hash or by assigning the same password and letting the system generate the hashes.

On 11g, if each system has its own system generated hash, then you won't get duplicate hashes even for the same user/same password.

If the 11g hashes are copied with "identified by values" as shown above, then, just like 10g hashes you'll see duplicates.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
A penalty grade doesn't seem warranted here.

Please explain
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.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 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

17 Experts available now in Live!

Get 1:1 Help Now