Skip to Main Content
  • Questions

  • Is changing passwords via alter <username> identified by values '<hash>'; a security risk?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kris.

Asked: March 10, 2017 - 3:07 pm UTC

Last updated: October 21, 2021 - 1:41 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked 

create user testuser1 identified by "DummyPass1";
create user testuser2 identified by "DummyPass1";

select name, spare4 from sys.user$ where name in ('TESTUSER1','TESTUSER2');


For the above test case, the password hashes are different between testuser1 and testuser2, which I would assume is because of how Oracle 12c is salting the password hash, thus improving security in-case the hashes are stolen.

We use public proxy database links between certain databases that are directly dependent on each other. As a courtesy to our programmer teams, when a programmer changes his password, the DBA team can replicate the password hash to the other database via: alter user <usercode> identified by values '<hash>'; This is so that the proxy database links still all function as intended.

Since we are replicating the hash, instead of letting Oracle generate the hash, are we introducing any security holes? I'm specifically referring to external threats: if the hashes themselves are stolen as opposed to the obvious internal security risk of "allowing the DBA team to copy password hashes to other users".

and Connor said...

You may recall older versions of Oracle where you could query the PASSWORD column on DBA_USERS and see the hash value there.

Nowadays you see this:

SQL> select password from dba_users;

PASSWORD
----------------------------------------------


and there's a reason for that. Even just having the hashes is a risk, because if you have the hashes, and you know the hashing *algorithm* then you can easily brute force crack the passwords.

So treat the dissemination of hashes with a similar caution to that of the passwords themselves.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Kris Johnston, March 13, 2017 - 2:37 pm UTC

Thank you for your response:

9i: Stores the password hash in the password column in dba_users.

10g: Stores the password hash in the password column in sys.user$. However, the passwords are case-insensitive. In addition, the hash is the same for the same password.

11g: Stored the password hash in the spare4 column in sys.user$. The password is case-sensitive, and the hash is different for the same password.

You can see this difference via:

alter user testuser1 identified by "DummyPass1";
select spare4 from sys.user$ where name = 'TESTUSER1';

alter user testuser1 identified by "DummyPass1";
select spare4 from sys.user$ where name = 'TESTUSER1';

alter user testuser1 identified by "DummyPass1";
select spare4 from sys.user$ where name = 'TESTUSER1';

Consider the following in 11g or 12c:

1) alter user identified by "<password>";
2) alter user identified by values "";

With #1, Oracle generates the hash, and generates a different hash even if the password is exactly the same.

With #2, since we are copying the hash, the hash is the same for the same password.

If we use #2, since the hash is the same for the same password, is that a potential concern there? Or should we always let Oracle generate the hash (via #1) even if the password is the same?
Connor McDonald
March 14, 2017 - 3:33 am UTC

Not sure what you mean by the #2

SQL> alter user TOM identified by values "";
alter user TOM identified by values ""
                                       *
ERROR at line 1:
ORA-01741: illegal zero-length identifier


But back to my base point - you can use the hash to reset a password to its previous value etc. But I would not *distribute* them on a basic that "its not a password, so its secure to pass them around where someone might store it in a file"... because hashes can be brute force attacked.

I agree, but what about public proxy database links?

Kris Johnston, March 16, 2017 - 4:39 pm UTC

My apologies, for #2, I meant: 
alter user identified by values '<hash>';


I completely agree that we should not be distributing the password hashes or making them visible to anyone that doesn't have sysdba privileges (we don't do either).

However, what we are currently doing is:

testuser1 says: "Hey, I just changed my password on DB1. Since we have public proxy database links from DB1 to DB2 and DB3, can you make my passwords the same among these databases?"

Me:

--I substituted the real hash for "12345" below for simplicity as well as security

sqlplus /@DB1 as sysdba

SQL> select spare4 from sys.user$ where name = 'TESTUSER1';

SPARE4
-------------
12345

SQL> exit;

sqlplus /@db2
SQL> alter user testuser1 identified by values '12345';

User altered.

SQL> exit;

sqlplus /@db3
SQL> alter user testuser1 identified by values '12345';

User altered.

SQL> exit;



Should we be doing the above?

Or should we inform testuser1 that if they want their password changed on DB2 and DB3, they'll have to log into each database and change the password themselves so that the password hash is correctly salted by Oracle?

Does that make sense?
Connor McDonald
March 19, 2017 - 5:24 am UTC

Yes that should be fine. We (currently) do not use the database name as part of the salt etc, because obviously we need to be able to preserve passwords for things like cloning etc.

Of course, "identified by values" is not in the documentation, so we reserve the right to remove it, change it, break it, etc etc....That's the risk you take.

Thank you

Kris Johnston, March 20, 2017 - 2:37 pm UTC

Thank you Connor,

Long time reader, first time poster, so I apologize for not making my question clear enough, initially.

Keep up the great work. :)
Connor McDonald
March 22, 2017 - 1:37 am UTC

Thanks for the feedback :-)

Security risk or not

Helen Sallee, July 18, 2017 - 12:34 pm UTC

I saw Connor's response "Yes that should be fine. We (currently) do not use the database name as part of the salt etc, because obviously we need to be able to preserve passwords for things like cloning etc.

Of course, "identified by values" is not in the documentation, so we reserve the right to remove it, change it, break it, etc etc....That's the risk you take. " So I take that to mean that we could safely use "identified by values" process to sync user passwords across databases at least until oracle changes that behavior. Thank you for confirming that this feature is undocumented.
Chris Saxon
July 18, 2017 - 1:13 pm UTC

As Connor says, this is an undocumented feature. So it's "use at your own risk". I'm not sure I'd take that to mean you could "safely" do this.

Oracle19c and identified by values

Ravi P, February 17, 2021 - 8:44 pm UTC

Regarding Connor's comment 'Of course, "identified by values" is not in the documentation, so we reserve the right to remove it, change it, break it, etc etc....That's the risk you take.'

Background: we have migrated a Oracle 19c SE2 database from Windows x86-64 to OEL on OVM x86-64.

Problem: Post the restore we found the known Schema passwords do not work on the target system.

Workaround:was to reset the Schema passwords post-migration using regular 'ALTER USER IDENTIFIED BY <password>'. There were no errors during the RMAN backup conversion to Linux at Source and restore to Linux target and application functionality passed the salt test.

However, if we try to reset the passwords by using IDENTIFIED BY VALUES <spare4>, extracted from the source database, it does not work. Only a manual reset at the target database works.

FYI: I have an Oracle SR going on for this issue but was wondering if this functionality has changed from Oracle 19c database.
Connor McDonald
February 23, 2021 - 6:04 am UTC

Problem: Post the restore we found the known Schema passwords do not work on the target system.


That seems like a bug to me (potentially in the cross-platform space). Did you use DataPump or cross-platform tablespace conversion ?

alter user username identified by values 'hash' works in Oracle 19c

Alex S, October 15, 2021 - 5:23 pm UTC

alter user username identified by values 'hash';
works in Oracle 19c but not in ADW -
Executing as ADMIN and get - ORA-01031: insufficient privileges

Connor McDonald
October 21, 2021 - 1:41 am UTC

Correct. No getting around that.

If you need to run something as a particular user, a better option is proxy connection rather than hacking their password temporarily.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.