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.

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.

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.

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.

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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s