Find out the list of users with default passwords in Oracle

From 11g we can quickly identify users with default passwords by querying the  data dictionary view: DBA_USERS_WITH_DEFPWD. This view will list out the users who are currently having the default passwords.

SQL> select username from dba_users_with_defpwd ;

USERNAME
------------------------------
DIP
MDSYS
CTXSYS
OLAPSYS
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA
WMSYS


14 rows selected.

The above list is the users who are currently having the default passwords in the database.

SQL> show user
USER is "SYS"
Now change the password for scott and query the above view that won't show the scott user in the list as you changed the scott user default password.

SQL> alter user scott identified by tiger1;
 
User altered.

Now if you check the view:

SQL> select * from dba_users_with_defpwd;

USERNAME
------------------------------
DIP
MDSYS
CTXSYS
OLAPSYS
OUTLN
EXFSYS
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA

WMSYS

13 rows selected.

To achieve the security compliance for the database, change the password for the above users and then expire the password then very next time when you are connecting to the user that will ask the new password.

SQL> select 'alter user '||username||' identified by Welcome678 account password expire;'from dba_users_with_defpwd where username not in ('SYS','SYSTEM','DBSNMP');

'ALTERUSER'||USERNAME||'IDENTIFIEDBYRESOURCE09ACCOUNTLOCKPASSWORDEXPIRE;'
---------------------------------------------------------------------------
alter user MDSYS identified by Welcome678 account password expire;
alter user OLAPSYS identified by Welcome678 account password expire;
alter user LBACSYS identified by Welcome678 account password expire;
alter user ORDSYS identified by Welcome678 account password expire;
alter user WMSYS identified by Welcome678 account password expire;
alter user XDB identified by Welcome678 account password expire;
alter user SI_INFORMTN_SCHEMA identified by Welcome678 account password expire;
alter user CTXSYS identified by Welcome678 account password expire;
alter user MDDATA identified by Welcome678 account password expire;
alter user ORDPLUGINS identified by Welcome678 account password expire;
alter user DIP identified by Welcome678 account password expire;
alter user EXFSYS identified by Welcome678 account password expire;
alter user OUTLN identified by Welcome678 account password expire;


13 rows selected.

Once you execute the above sql statements then we can say currently no users in the database is using default password.

SQL> select username from dba_users_with_defpwd ;

no rows selected.

Happy Learning :)