Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> What is a "default" Password?, DBA_USERS_WITH_DEFPWD is incorrectly documented (and used)
SithLord
post May 11 2017, 06:47 PM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 11-May 17
Member No.: 51,975



SQL> SELECT COMMENTS FROM DBA_TAB_COMMENTS WHERE TABLE_NAME='DBA_USERS_WITH_DEFPWD';

COMMENTS
----------------------------------------------------------------------------------------------------
Users that are still using their default passwords


This view is often used to determine whether the users created with the database (like SYS and SYSTEM) still have their "default" passwords.

For example, the CIS Benchmark for Oracle 12c has this:

1.2 Ensure All Default Passwords Are Changed (Scored)

Description:
The Oracle installation has a view called DBA_USERS_WITH_DEFPWD, which keeps a list of all
database users making use of default passwords.

Rationale:
Default passwords should be considered "well known" to attackers. Consequently, if
default passwords remain in place any attacker with access to the database then has the
ability to authenticate as the user with that default password. When default passwords are
altered, this circumstance is mitigated.

Audit:
To assess this recommendation, execute the following SQL statement.
SELECT USERNAME
FROM DBA_USERS_WITH_DEFPWD
WHERE USERNAME NOT LIKE '%XS$NULL%';
The assessment fails if results are returned.



The problem I see is that this view does not really examine whether the password is a default password.

If we define "default" as one of the passwords listed in DEFAULT_PWD$, then the view is definitely NOT looking at what is default, because it does not look at that list (in column PWD_VERIFIER).

Rather, the view is, most importantly, looking at USER$.ASTATUS.
Specifically, it's looking for ASTATUS values that resolve to 16 with the BITAND function.

Here's the DDL for the view:

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;



Values of 16, 24 or 25 will give a BITAND of 16.

But, what do the various ASTATUS values actually mean?

The ASTATUS mapping table is no help.
It maxes out at 10:

SQL> select * from USER_ASTATUS_MAP ORDER BY 1;

STATUS# STATUS
---------- --------------------------------
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
8 LOCKED
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED

9 rows selected.



The only documentation I could find on MOS is this brief and not very helpful note:

How to Interpret the ASTATUS Column in user$ . astatus = 25 in user$ what does it mean? (Doc ID 2183493.1)

The astatus column gets is value derived as

25 = 16 + 8 + 1

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



It seems to me that Oracle is confusing "default" with "original".

To me, "default", in a security sense, should mean one of the passwords listed in DEFAULT_PWD$.PWD_VERIFIER.

Whereas "original" means that the password has not changed since the user was created.
A password can be both "default" AND "original".
And a password can be "original" but NOT "default".
It can also be "default" and NOT "original".

For SYS and SYSTEM, "original" more specifically means that the password has not changed since the user was created with the database.

Let me demonstrate:

Here I have user SYS, in my 12.1.0.2 database.
When I created this database, using the CREATE DATABASE command, I specified a custom password for SYS, using clause:
USER SYS IDENTIFIED BY <custom_password>

Now, here's how this custom, "original", password appears to the views and tables in question:

--User SYS has USER$.ASTATUS=16, and DOES show up in DBA_USERS_WITH_DEFPWD.

SQL> select astatus, password from user$ where name='SYS';

ASTATUS PASSWORD
---------- ----------------------------------------
16 818C3D04B78ADB5B


SQL> SELECT COUNT(*) FROM DBA_USERS_WITH_DEFPWD WHERE USERNAME='SYS';

COUNT(*)
----------
1


-- "Alter" the password, to the same as it is.

SQL> ALTER USER SYS IDENTIFIED BY <same_as_current>;

User altered.


-- Now USER$.ASTATUS is 0, and the user no longer shows up in DBA_USERS_WITH_DEFPWD.
-- Note that the password is still the same.

SQL> select astatus, password from user$ where name='SYS';

ASTATUS PASSWORD
---------- ----------------------------------------
0 818C3D04B78ADB5B

SQL> SELECT COUNT(*) FROM DBA_USERS_WITH_DEFPWD WHERE USERNAME='SYS';

COUNT(*)
----------
0



So, I've "changed" the password to what it was, but Oracle is now saying it's a non-default password (ASTATUS=0) whereas before it was default (ASTATUS=16).
==>My interpretation is that an ASTATUS=16 actually means that the password is "original".



-- You can even change the password to one of the default passwords listed in DEFAULT_PWD$, and the user STILL won't show up in DBA_USERS_WITH_DEFPWD.

SQL> select PWD_VERIFIER from DEFAULT_PWD$ where user_name='SYS' ORDER BY 1;

PWD_VERIFIER
--------------------
089509EC42EF6C07
...
E7686462E8CD2F5E

24 rows selected.


-- Give SYS the first default password:

SQL> ALTER USER SYS IDENTIFIED BY VALUES '089509EC42EF6C07';

User altered.

SQL> select astatus, password from user$ where name='SYS';

ASTATUS PASSWORD
---------- ----------------------------------------
0 089509EC42EF6C07


-- The absence of the user in this view tells you SYS is NOT considered to be a user with a "default" password:

SQL> SELECT COUNT(*) FROM DBA_USERS_WITH_DEFPWD WHERE USERNAME='SYS';

COUNT(*)
----------
0


END.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd June 2017 - 08:52 AM