Solved

duplicate passwords for oracle accounts

Posted on 2014-02-18
13
454 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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
ID: 39868287
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
ID: 39868289
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 74

Assisted Solution

by:sdstuber
sdstuber earned 166 total points
ID: 39868291
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:pma111
ID: 39868294
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39868300
Same password, different accounts, different hash.

Select with count will not work.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39868302
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39868304
>>>  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
ID: 39868313
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 74

Expert Comment

by:sdstuber
ID: 39868360
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 74

Expert Comment

by:sdstuber
ID: 39868380
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
ID: 39868663
Then there are the DBAs who use an identical set of users and passwords on multiple hosts; yea even production and development....
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39868700
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 74

Expert Comment

by:sdstuber
ID: 39901333
A penalty grade doesn't seem warranted here.

Please explain
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create table from select - oracle 6 65
SYS password changed. Now can't log in as SYS 27 40
Row_number in SQL 6 45
Query - Duplicate dates with different activities counts 10 43
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

740 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