Skip to Main Content
  • Questions

  • How and when is the hash updated in spare4 from sys.user$?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, André.

Asked: March 15, 2022 - 9:31 am UTC

Last updated: February 24, 2023 - 6:03 pm UTC

Version: 19.14.0.0

Viewed 10K+ times! This question is

You Asked

Hi,

I read the following question here:

https://asktom.oracle.com/pls/apex/asktom.search?tag=is-changing-passwords-via-alter-username-identified-by-values-hash-a-security-risk

and learned, that a
ALTER USER foo IDENTIFIED BY "<somePwd>";

updated the hash in sys.user$ every time, even if has not changed.

So I did the following (as SYS) and really don't understand, why my hash doesn't change:

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 11 10:37:54 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> select spare4 from user$ where name = 'SYS';

SPARE4
--------------------------------------------------------------------------------
S:13ABFAC25DBA6CCEE32D8208A6D9FB4DC91C34E59C88917941D0067E927F;T:CA24A4C3788DECC
xXx..........................................................................xXx
31C4DA835D5848903BA5CF79B0499E4DC3A9BA10997D4546FEF21E0F3212AE7D1


SQL> alter user sys identified by "foo_BaR_42!13";

User altered.

SQL> select spare4 from user$ where name = 'SYS';

SPARE4
--------------------------------------------------------------------------------
S:13ABFAC25DBA6CCEE32D8208A6D9FB4DC91C34E59C88917941D0067E927F;T:CA24A4C3788DECC
xXx..........................................................................xXx
31C4DA835D5848903BA5CF79B0499E4DC3A9BA10997D4546FEF21E0F3212AE7D1


SQL>


Of course the hash has been modified for this example, but it is identical after the the ALTER USER command.

Am I missing something here?

Thanks and regards - André

and Connor said...

SYS is special. Normal accounts will exhibit the behaviour you're expecting

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

SPARE4
----------------------------------------------------------------------------------------------------------------------------------
S:65E407AAD6CAC5D2BAE38E6A9EBF914C35282D0B5A159C3DB6A3465EDA99;T:F606AA3EB2473036F61588AE8DFB198364C2B1DFCB12A566EB235D90E3448A10D
23B5D8983AA42C4FF3A93DD3F9AAB7D63632FDF26313C933056DA95412859513F7D20AA6DCB785363A648677CB8163A


SQL> alter user scott identified by qweqwe;

User altered.

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

SPARE4
----------------------------------------------------------------------------------------------------------------------------------
S:339770D2E0C3DFCFAD70DC52D1F0600FF6F2DEB6291B2BF8FA78249DC313;T:4FBF09E7AEFAA62DF1B67479E349D1FA7B440FF7BBB7DB24B6CE46CFAFFB6779A
630BADAA0F8634D2E25B91088A15D53ABB32DE020DAA40759E76AAB1ACF27B270590ACA133744ABEC4DA353E71AC761


Rating

  (1 rating)

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

Comments

Where is the actual updated hash for the user 'SYS', can I found it from a table?

T3LS, February 24, 2023 - 10:35 am UTC

So where is the actual updated hash for the user 'SYS', can I found it from a table like SYS.USER$?
Chris Saxon
February 24, 2023 - 6:03 pm UTC

The queries above are against SYS.USER$ !

As Connor says, SYS is special. You can't see it's hash.
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.