首页 > 代码库 > 11g新特性-dba_users安全性的一些增强

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

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

查询10g的dba_user表

SQL> select username,password from dba_users;USERNAME                  PASSWORD------------------------- ------------------------------SYS                       4CCF4A082AD3F312SYSTEM                    34F99ED804364129GP                        BA5BE92FE6F36B67REPADMIN                  915C93F34954F5F8HYY                       A7AEE33D853BFBC8OUTLN                     4A3BA55E08595C81MGMT_VIEW                 05CB73340B23910CMDSYS                     72979A94BAD2AF80ORDSYS                    7EFA02EC7EA6B86FEXFSYS                    66F4EF5650C20355DMSYS                     BFBA5A553FD9E28ADBSNMP                    E066D214D5421CCCWMSYS                     7C9BA362F8314299CTXSYS                    71E687F036AD56E5ANONYMOUS                 anonymousSYSMAN                    447B729161192C24XDB                       88D8364765FCE6AFORDPLUGINS                88A2B2C183431F00SI_INFORMTN_SCHEMA        84B8CBCA4D477FA3OLAPSYS                   4AC23CC3B15E2208SCOTT                     F894844C34402B67ORACLE_OCM                5A2E026A9157958CTSMSYS                    3DF26A8B17D0F29FMDDATA                    DF02A496267DEE66DIP                       CE4A36B8E06CA59C

  

查询11g的dba_user表

SQL> select username,password from dba_users;USERNAME                  PASSWORD------------------------- ----------SYSTEMSYSMGMT_VIEWDBSNMPSYSMANISCTESTLZQSCOTTSURESSOUTLNOLAPSYSSI_INFORMTN_SCHEMAOWBSYSORDPLUGINSXDBANONYMOUSCTXSYSORDDATAOWBSYS_AUDITAPEX_030200APPQOSSYSWMSYSEXFSYSORDSYSMDSYSFLOWS_FILESSPATIAL_WFS_ADMIN_USRSPATIAL_CSW_ADMIN_USRAPEX_PUBLIC_USERDIPMDDATAXS$NULLORACLE_OCM35 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                    34F99ED804364129SYS                       4CCF4A082AD3F312MGMT_VIEW                 9F4137A5B1A2E1ACDBSNMP                    10D93CA858E0F50DSYSMAN                    4D4568914D47DF1DISC                       373F527DC0CFAE98TEST                      7A0F2B316C212D67LZQ                       8B8622D6B295E0E1SCOTT                     F894844C34402B67SURE                      7CF462527540A5B0SS                        6C38D7B24909EB18OUTLN                     4A3BA55E08595C81OLAPSYS                   4AC23CC3B15E2208SI_INFORMTN_SCHEMA        84B8CBCA4D477FA3OWBSYS                    610A3C38F301776FORDPLUGINS                88A2B2C183431F00XDB                       88D8364765FCE6AFANONYMOUS                 anonymousCTXSYS                    71E687F036AD56E5ORDDATA                   A93EC937FCD1DC2AOWBSYS_AUDIT              FD8C3D14F6B60015APEX_030200               6B653304BCFBC89DAPPQOSSYS                 519D632B7EE7F63AWMSYS                     7C9BA362F8314299EXFSYS                    33C758A8E388DEE5ORDSYS                    7EFA02EC7EA6B86FMDSYS                     72979A94BAD2AF80FLOWS_FILES               738F2D4D10BF7DE2SPATIAL_WFS_ADMIN_USR     7117215D6BEE6E82SPATIAL_CSW_ADMIN_USR     1B290858DD14107EAPEX_PUBLIC_USER          E943E8C4CB4A6A9BDIP                       CE4A36B8E06CA59CMDDATA                    DF02A496267DEE66XS$NULL                   DC4FCC8CB69A6733ORACLE_OCM                5A2E026A9157958C35 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;

 

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