[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

duplicate passwords for oracle accounts

Posted on 2014-02-18
13
Medium Priority
?
464 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +2
13 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 501 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 23

Assisted Solution

by:Steve Wales
Steve Wales earned 501 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 498 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 23

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

656 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