by After creation of a new 12c database, the SYS and SYSTEM accounts are listed in DBA_USERS_WITH_DEFPWD even though the accounts were created with non-default passwords. Setting the same passwords again with ALTER USER correctly recognises that the accounts do not have default passwords.
I found this sys/system accounts in “DBA_USERS_WITH_DEFPWD” view during external auditing of our databases.
|
1 2 3 4 |
CDB$ROOT> SELECT COUNT(*) FROM DBA_USERS_WITH_DEFPWD WHERE USERNAME in('SYS','SYSTEM'); COUNT(*) ---------- 2 |
As i knew that passwords aren’t default ones. I looked further at user$ views and found ASTATUS 16 for both account.
|
1 2 3 4 5 |
CDB$ROOT> select astatus, password from user$ where name in('SYS', ‘SYSTEM’); Name ASTATUS PASSWORD ---------- ---------------------------------------- SYS 16 E83D$$$$$$$$.... SYSTEM 16 OPL0E$$$.... |
What that astatus value is??? From MOS Doc ID 2183493.1, I could able to find out that ASTATUS =16 means default password.
16 – user is having default password (this bit from user$.astatus is what gets checked for DBA_USERS_WITH_DEFPWD view)
8 – account is locked
1 – password is expired
Further analysis of “DBA_USERS_WITH_DEFPWD” view…
This view does not look at the default password as nowhere column PWD_VERIFIER is used in the select statement. Rather view is looking at ASTATUS values that resolve to 16 with the BITAND function.
|
1 2 3 4 5 |
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DBA_USERS_WITH_DEFPWD" ("USERNAME", "PRODUCT") AS SELECT DISTINCT u.name, dp.product FROM SYS.user$ u, SYS.default_pwd$ dp WHERE (u.type# = 1) AND (u.name = dp.user_name) AND (bitand(u.astatus, 16) = 16) AND dp.pv_type >= 0; |
According to me, Oracle is confused between “default” with “original” password.
I “Reset” the password to the same as it was and now USER$.ASTATUS is 0. The user no longer shows up in DBA_USERS_WITH_DEFPWD.
|
1 2 3 4 5 6 7 8 |
CDB$ROOT> ALTER USER SYS IDENTIFIED BY password; User altered. CDB$ROOT> ALTER USER SYSTEM IDENTIFIED BY password; User altered. CDB$ROOT> SELECT COUNT(*) FROM DBA_USERS_WITH_DEFPWD WHERE USERNAME in('SYS','SYSTEM'); COUNT(*) ---------- 0 |
Later, I found Doc ID 2173962.1 which explains exactly, what I thought the problem is???
In 11g, DBA_USERS_WITH_DEFPWD used a rainbow table of pre-built hashes to tell if an account was using a default password.
In 12c, it’s switched to a much stronger, salted hash and no longer check for default hashes with the rainbow table. Instead, the check is done at user create/password change time and if the password is set to a default value that sets a flag that triggers the entry in DBA_USERS_WITH_DEFPWD.
SYS and SYSTEM have that flag set a database create time, and the flag doesn’t clear until the first password change AFTER the database is created. So it may or may not be a default password, but it if it isn’t default, it’s one that hasn’t been changed since the database was created and therefore should be changed
Hello everyone,
Here I found a good explanation for resetting and reusing Oracle account password
http://dbpilot.net/2018/01/15/resetting-an-oracle-account-password
Awesome, thank you!!! Also great explanation as to why.