Oracle 11g默认用户密码增强-default_pwd$

昨天看到老杨写的 11g新增默认用户密码监测，也顺便看了一下这个新特性。

Oracle 11g中增加了一个新的视图DBA_USERS_WITH_DEFPWD用于显示那些具有缺省口令的用户：

SQL> desc DBA_USERS_WITH_DEFPWD
 名称                                      是否为空? 类型
 ----------------------------------------- -------- --------------
 USERNAME                                  NOT NULL VARCHAR2(30)
SQL> select * from dba_users_with_defpwd;

USERNAME
------------------------------
DIP
MDSYS
XS$NULL
SPATIAL_WFS_ADMIN_USR
OUTLN
OLAPSYS
SPATIAL_CSW_ADMIN_USR
OWBSYS
ORACLE_OCM
EXFSYS
SCOTT
DBSNMP
ORDSYS
ORDPLUGINS
MDDATA
APPQOSSYS
ORDDATA
XDB
WMSYS
SI_INFORMTN_SCHEMA

已选择20行。

这里显示的20个用户，其账户都是锁定的，所以大可不必担心。

如果进一步的研究一下，这个视图的底层表是DEFAULT_PWD$：

SQL> set autotrace trace explain
SQL> select * from dba_users_with_defpwd;

执行计划
----------------------------------------------------------
Plan hash value: 180429094

---------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                  | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                       |     6 |   102 |    10  (10)| 00:00:01 |
|   1 |  VIEW                           | DBA_USERS_WITH_DEFPWD |     6 |   102 |    10  (10)| 00:00:01 |
|   2 |   HASH UNIQUE                   |                       |     6 |   312 |    10  (10)| 00:00:01 |
|   3 |    CONCATENATION                |                       |       |       |            |       |
|   4 |     NESTED LOOPS                |                       |       |       |            |       |
|   5 |      NESTED LOOPS               |                       |     2 |   104 |     4   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL         | USER$                 |     2 |    54 |     2   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | I_DEFAULT_PWD         |     1 |       |     0   (0)| 00:00:01 |
|*  8 |      TABLE ACCESS BY INDEX ROWID| DEFAULT_PWD$          |     1 |    25 |     1   (0)| 00:00:01 |
|   9 |     NESTED LOOPS                |                       |    50 |  2600 |     5   (0)| 00:00:01 |
|* 10 |      TABLE ACCESS FULL          | USER$                 |     1 |    27 |     2   (0)| 00:00:01 |
|* 11 |      TABLE ACCESS FULL          | DEFAULT_PWD$          |   775 | 19375 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("U"."PASSWORD" IS NOT NULL AND "U"."TYPE#"=1)
   7 - access("U"."NAME"="DP"."USER_NAME" AND "U"."PASSWORD"="DP"."PWD_VERIFIER")
   8 - filter("DP"."PV_TYPE"=0)
  10 - filter("U"."TYPE#"=1 AND BITAND("U"."ASTATUS",16)=16)
  11 - filter(LNNVL("U"."PASSWORD"="DP"."PWD_VERIFIER") OR LNNVL("U"."NAME"="DP"."USER_NAME") OR
              LNNVL("DP"."PV_TYPE"=0) OR LNNVL("U"."PASSWORD" IS NOT NULL))

这个新的底层表记录了缺省用户的缺省口令：

SQL> desc default_pwd$
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -----------
 USER_NAME                                          VARCHAR2(128)
 PWD_VERIFIER                                       VARCHAR2(512)
 PV_TYPE                                            NUMBER
SQL> select * from default_pwd$ where user_name='SCOTT';

USER_NAME  PWD_VERIFIER                      PV_TYPE
---------- ------------------------------ ----------
SCOTT      F894844C34402B67                        0

有了这个表之后，视图通过 u.password = dp.pwd_verifier比对就可以发现那些缺省口令了：

SQL> select text from dba_views where view_name='DBA_USERS_WITH_DEFPWD';

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)

这个关于缺省口令的改进告知我们：缺省口令是极其危险的！

-The End-