11g新特性-dba_users安全性的一些增强

1.dba_user表的password(除了GLOBAL和EXTERNAL的密码)不再保存密码。

查询10g的dba_user表

SQL> select username,password from dba_users;

USERNAME                  PASSWORD
------------------------- ------------------------------
SYS                       4CCF4A082AD3F312
SYSTEM                    34F99ED804364129
GP                        BA5BE92FE6F36B67
REPADMIN                  915C93F34954F5F8
HYY                       A7AEE33D853BFBC8
OUTLN                     4A3BA55E08595C81
MGMT_VIEW                 05CB73340B23910C
MDSYS                     72979A94BAD2AF80
ORDSYS                    7EFA02EC7EA6B86F
EXFSYS                    66F4EF5650C20355
DMSYS                     BFBA5A553FD9E28A
DBSNMP                    E066D214D5421CCC
WMSYS                     7C9BA362F8314299
CTXSYS                    71E687F036AD56E5
ANONYMOUS                 anonymous
SYSMAN                    447B729161192C24
XDB                       88D8364765FCE6AF
ORDPLUGINS                88A2B2C183431F00
SI_INFORMTN_SCHEMA        84B8CBCA4D477FA3
OLAPSYS                   4AC23CC3B15E2208
SCOTT                     F894844C34402B67
ORACLE_OCM                5A2E026A9157958C
TSMSYS                    3DF26A8B17D0F29F
MDDATA                    DF02A496267DEE66
DIP                       CE4A36B8E06CA59C

  

查询11g的dba_user表

SQL> select username,password from dba_users;

USERNAME                  PASSWORD
------------------------- ----------
SYSTEM
SYS
MGMT_VIEW
DBSNMP
SYSMAN
ISC
TEST
LZQ
SCOTT
SURE
SS
OUTLN
OLAPSYS
SI_INFORMTN_SCHEMA
OWBSYS
ORDPLUGINS
XDB
ANONYMOUS
CTXSYS
ORDDATA
OWBSYS_AUDIT
APEX_030200
APPQOSSYS
WMSYS
EXFSYS
ORDSYS
MDSYS
FLOWS_FILES
SPATIAL_WFS_ADMIN_USR
SPATIAL_CSW_ADMIN_USR
APEX_PUBLIC_USER
DIP
MDDATA
XS$NULL
ORACLE_OCM

35 rows selected.

发现11g中password列值为空了。

这是因为虽然密码是经过加密存储的,但是从信息安全的角度来看,暴露的信息越多越不安全,所以即使是加密后的密码泄漏,也可能造成很严重的安全问题。所以作为11g的一个新特性,在dba_users的password列不再保存密码了。

那么问题就来了,如果我要做用户的迁移怎么办?呵呵,alter user identified by values依然可用。我们可以直接去查询sys.user$这张表。

SQL> select name,password from sys.user$ where name in (select username from dba_users);

NAME                      PASSWORD
------------------------- -------------------------
SYSTEM                    34F99ED804364129
SYS                       4CCF4A082AD3F312
MGMT_VIEW                 9F4137A5B1A2E1AC
DBSNMP                    10D93CA858E0F50D
SYSMAN                    4D4568914D47DF1D
ISC                       373F527DC0CFAE98
TEST                      7A0F2B316C212D67
LZQ                       8B8622D6B295E0E1
SCOTT                     F894844C34402B67
SURE                      7CF462527540A5B0
SS                        6C38D7B24909EB18
OUTLN                     4A3BA55E08595C81
OLAPSYS                   4AC23CC3B15E2208
SI_INFORMTN_SCHEMA        84B8CBCA4D477FA3
OWBSYS                    610A3C38F301776F
ORDPLUGINS                88A2B2C183431F00
XDB                       88D8364765FCE6AF
ANONYMOUS                 anonymous
CTXSYS                    71E687F036AD56E5
ORDDATA                   A93EC937FCD1DC2A
OWBSYS_AUDIT              FD8C3D14F6B60015
APEX_030200               6B653304BCFBC89D
APPQOSSYS                 519D632B7EE7F63A
WMSYS                     7C9BA362F8314299
EXFSYS                    33C758A8E388DEE5
ORDSYS                    7EFA02EC7EA6B86F
MDSYS                     72979A94BAD2AF80
FLOWS_FILES               738F2D4D10BF7DE2
SPATIAL_WFS_ADMIN_USR     7117215D6BEE6E82
SPATIAL_CSW_ADMIN_USR     1B290858DD14107E
APEX_PUBLIC_USER          E943E8C4CB4A6A9B
DIP                       CE4A36B8E06CA59C
MDDATA                    DF02A496267DEE66
XS$NULL                   DC4FCC8CB69A6733
ORACLE_OCM                5A2E026A9157958C

35 rows selected.

事实上dba_users就是结合user$等相关基表创建的视图。从dba_uses视图的相关语句可以看出在11g中当用户被创建为全局或外部认证时password才有加密值,而10g中dba_users的password列完全取值与user$的password列值相关SQL

10g:

10g:
select u.name, u.user#, u.password,
       m.status,
       decode(u.astatus, 4, u.ltime,
                         5, u.ltime,
                         6, u.ltime,
                         8, u.ltime,
                         9, u.ltime,
                         10, u.ltime, to_date(NULL)),
       decode(u.astatus,
              1, u.exptime,
              2, u.exptime,
              5, u.exptime,
              6, u.exptime,
              9, u.exptime,
              10, u.exptime,
              decode(u.ptime, ‘‘, to_date(NULL),
                decode(pr.limit#, 2147483647, to_date(NULL),
                 decode(pr.limit#, 0,
                   decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
                     dp.limit#/86400),
                   u.ptime + pr.limit#/86400)))),
       dts.name, tts.name, u.ctime, p.name,
       nvl(cgm.consumer_group, DEFAULT_CONSUMER_GROUP),
       u.ext_username
       from sys.user$ u left outer join sys.resource_group_mapping$ cgm
            on (cgm.attribute = ORACLE_USER and cgm.status = ACTIVE and
                cgm.value = u.name),
            sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
            sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
       where u.datats# = dts.ts#
       and u.resource$ = p.profile#
       and u.tempts# = tts.ts#
       and u.astatus = m.status#
       and u.type# = 1
       and u.resource$ = pr.profile#
       and dp.profile# = 0
       and dp.type#=1
       and dp.resource#=1
       and pr.type# = 1
       and pr.resource# = 1

11g:

11g:
select u.name, u.user#,
       decode(u.password, GLOBAL, u.password,
                          EXTERNAL, u.password,
                          NULL),
       m.status,
       decode(u.astatus, 4, u.ltime,
                         5, u.ltime,
                         6, u.ltime,
                         8, u.ltime,
                         9, u.ltime,
                         10, u.ltime, to_date(NULL)),
       decode(u.astatus,
              1, u.exptime,
              2, u.exptime,
              5, u.exptime,
              6, u.exptime,
              9, u.exptime,
              10, u.exptime,
              decode(u.ptime, ‘‘, to_date(NULL),
                decode(pr.limit#, 2147483647, to_date(NULL),
                 decode(pr.limit#, 0,
                   decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
                     dp.limit#/86400),
                   u.ptime + pr.limit#/86400)))),
       dts.name, tts.name, u.ctime, p.name,
       nvl(cgm.consumer_group, DEFAULT_CONSUMER_GROUP),
       u.ext_username,
       decode(length(u.password),16,10G ,NULL)||NVL2(u.spare4, 11G  ,NULL),
       decode(bitand(u.spare1, 16),
              16, Y,
                  N),
       decode(u.password, GLOBAL,   GLOBAL,
                          EXTERNAL, EXTERNAL,
                          PASSWORD)
       from sys.user$ u left outer join sys.resource_group_mapping$ cgm
            on (cgm.attribute = ORACLE_USER and cgm.status = ACTIVE and
                cgm.value = u.name),
            sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
            sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
       where u.datats# = dts.ts#
       and u.resource$ = p.profile#
       and u.tempts# = tts.ts#
       and u.astatus = m.status#
       and u.type# = 1
       and u.resource$ = pr.profile#
       and dp.profile# = 0
       and dp.type#=1
       and dp.resource#=1
       and pr.type# = 1
       and pr.resource# = 1

 

 

2.密码区分大小写
可以通过初始化参数sec_case_sensitive_logon来控制密码是否大小写敏感,默认TRUE

3.密码复杂性检查
通过执行以下脚本生成密码复杂性检查函数verify_function_11G
@$ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql

然后设置profile使用该函数来检查密码即可
ALTER PROFILE default PASSWORD_VERIFY_FUNCTION verify_function_11G;

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。