DBA_USERS_WITH_DEFPWD视图的作用是显示那些用户使用的是缺省口令

SQL> set autotrace on
SQL> select * from DBA_USERS_WITH_DEFPWD;

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

USERNAME
------------------------------
DBSNMP
MDDATA
ORDPLUGINS
ORDSYS
APPQOSSYS
ORDDATA
XDB
SI_INFORMTN_SCHEMA
WMSYS

20 rows selected.

通过下面的语句也能查询出使用默认口令的用户。
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
     (

SQL> SELECT DISTINCT u.name
2      FROM SYS.user$ u, SYS.default_pwd$ dp
3     WHERE u.password=dp.pwd_verifier;

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

20 rows selected.

使用以下视图可以查询出用户的密码values
SQL> desc default_pwd$
Name               Null?    Type
------------------ -------- ---------------
USER_NAME                   VARCHAR2(128)
PWD_VERIFIER                VARCHAR2(512)
PV_TYPE                     NUMBER

SQL> set autotrace off
SQL> set linesize 200
SQL> col USER_NAME for a30
SQL> col PWD_VERIFIER for a30
SQL> select * from default_pwd$ where USER_NAME='SCOTT';

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

SQL> select NAME,password from user$ where name='SCOTT';

NAME PASSWORD
-------------------- ------------------------------
SCOTT F894844C34402B67

可以看到2个视图查询出的值是一样的

现在我修改scott用户的密码
SQL> alter user scott identified by "SCOTT";

User altered.

再次查询默认口令的用户时，scott不在此列
SQL> select * from DBA_USERS_WITH_DEFPWD;

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

USERNAME
------------------------------
ORDSYS
ORDPLUGINS
MDDATA
APPQOSSYS
XDB
ORDDATA
WMSYS
SI_INFORMTN_SCHEMA

19 rows selected.

我把scott的密码修改回来，Oracle还会认为他是默认口令的用户吗？
SQL> alter user scott identified by "TIGER";

User altered.

SQL> select * from DBA_USERS_WITH_DEFPWD;

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

USERNAME
------------------------------
DBSNMP
ORDSYS
ORDPLUGINS
MDDATA
APPQOSSYS
XDB
ORDDATA
WMSYS
SI_INFORMTN_SCHEMA

20 rows selected.
显然，只要密码一样，Oracle都认为他是默认口令的用户。

ITPub博客

默认口令用户-DBA_USERS_WITH_DEFPWD动态性能视图

最新文章