Here is an Oracle 10 database, which does not have a DBA_USERS_WITH_DEFPWD view:
ORACLE 10 > sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Aug 11 18:03:27 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> desc dba_users_with_defpwd
ERROR:
ORA-04043: object dba_users_with_defpwd does not exist
SQL>
And here is an Oracle 11 database, which does:
ORACLE 11 > sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 11 18:31:20 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc dba_users_with_defpwd
Name Null? Type
----------------------- -------- ----------------
USERNAME NOT NULL VARCHAR2(30)
SQL> select password from sys.user$
2 where name = 'SYSTEM';
PASSWORD
------------------------------
D4DF7931AB130E37
SQL>
So, although I have not checked, it looks to me as if this view was new in Oracle 11. The SELECT statement at the end shows that the PASSWORD column in SYS.USER$ has the old Oracle 10 encrypted value.
The dba_users_with_defpwd view shows users with default passwords:
SQL> alter user system identified by manager
2 /
User altered.
SQL> select * from dba_users_with_defpwd
2 /
USERNAME
------------------------------
SYSTEM
XS$NULL
2 rows selected.
SQL> alter user system identified by highly_secret
2 /
User altered.
SQL> select * from dba_users_with_defpwd
2 /
USERNAME
------------------------------
XS$NULL
1 row selected.
SQL>
It even shows ordinary users:
SQL> create user scott identified by tiger
2 /
User created.
SQL> select * from dba_users_with_defpwd
2 /
USERNAME
------------------------------
XS$NULL
SCOTT
2 rows selected.
SQL> alter user scott identified by lion
2 /
User altered.
SQL> select * from dba_users_with_defpwd
2 /
USERNAME
------------------------------
XS$NULL
1 row selected.
SQL>
This is the text of the view. The comparison is done on the old Oracle 10 encrypted value:
SQL> select text from dba_views
2 where view_name = 'DBA_USERS_WITH_DEFPWD'
3 /
TEXT
-------------------------------------------------
SELECT DISTINCT u.name
FROM SYS.user$ u, SYS.default_pwd$ dp
WHERE
(u.type# = 1
AND bitand(u.astatus, 16) = 16
) OR
(u.type# = 1
AND u.password = dp.pwd_verifier
AND u.name = dp.user_name
AND dp.pv_type = 0)
1 row selected.
SQL>
So both upper and lower case versions of the default passwords
will be detected:
SQL> alter user system identified by manager
2 /
User altered.
SQL> select * from dba_users_with_defpwd
2 /
USERNAME
------------------------------
SYSTEM
XS$NULL
2 rows selected.
SQL>
