Friday, December 10, 2010

Oracle 11g new feature: new data dictionary view for listing users with default password - DBA_USERS_WITH_DEFPWD

As you know, after creation of new database, there will be some DB users whose password should be changed because of security issues.

Before 11g, we had to read documentation and check which users are in this list.

However, starting from 11g, we can easily find out that user - using DBA_USERS_WITH_DEFPWD

SQL> desc DBA_USERS_WITH_DEFPWD
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 USERNAME                                  NOT NULL VARCHAR2(30)


SQL> select username from DBA_USERS_WITH_DEFPWD;

USERNAME
------------------------------
DIP
MDSYS
XS$NULL
SPATIAL_WFS_ADMIN_USR
CTXSYS
OLAPSYS
OUTLN
OWBSYS
SPATIAL_CSW_ADMIN_USR
EXFSYS
ORACLE_OCM

USERNAME
------------------------------
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
PM
APPQOSSYS
BI
IX
ORDDATA
XDB
SI_INFORMTN_SCHEMA

USERNAME
------------------------------
WMSYS

23 rows selected.


Now, lets change SCOTT's password and check if SCOTT appears in this list again:

SQL> alter user scott identified by newpassword;

User altered.

SQL> select username from DBA_USERS_WITH_DEFPWD;

USERNAME
------------------------------
DIP
XS$NULL
MDSYS
SPATIAL_WFS_ADMIN_USR
OUTLN
CTXSYS
OLAPSYS
SPATIAL_CSW_ADMIN_USR
OWBSYS
ORACLE_OCM
EXFSYS

USERNAME
------------------------------
ORDSYS
ORDPLUGINS
MDDATA
PM
APPQOSSYS
XDB
ORDDATA
IX
BI
WMSYS
SI_INFORMTN_SCHEMA

22 rows selected.

Well, now SCOTT disappeared from this list. If we change passwortd back to default, SCOTT will appear again:

SQL> alter user scott identified by tiger;

User altered.

SQL> select username from DBA_USERS_WITH_DEFPWD;

USERNAME
------------------------------
DIP
XS$NULL
MDSYS
SPATIAL_WFS_ADMIN_USR
OUTLN
CTXSYS
OLAPSYS
SPATIAL_CSW_ADMIN_USR
OWBSYS
ORACLE_OCM
EXFSYS

USERNAME
------------------------------
SCOTT
ORDSYS
ORDPLUGINS
MDDATA
PM
APPQOSSYS
XDB
ORDDATA
IX
BI
WMSYS

USERNAME
------------------------------
SI_INFORMTN_SCHEMA

23 rows selected.

SQL>
Enjoy!

1 comment: