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.

As i knew that passwords aren’t default ones. I looked further at user$ views and found ASTATUS 16 for both account.

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.

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.

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


Leave a Reply