虚位以待(AD)
虚位以待(AD)
首页 > 数据库 > Oracle数据库 > 提升Oracle用户密码安全性的策略

提升Oracle用户密码安全性的策略
类别:Oracle数据库   作者:码皇   来源:互联网   点击:

最近遇到这样的客户需求,数据库中有很多业务用户名,客户期望密码设置不要过于简单,最起码别和用户名一致或相似就好。怎么解决这个需求呢?下面小编给大家带来了提升Oracle用户密码安全性的策略,感兴趣的朋友一起看看吧

环境:Oracle 11.2.0.4

客户需求:主要背景是数据库中有很多业务用户名,且由于部分用户缺乏安全意识,甚至直接将自己的密码设置为和用户名一样,目前客户期望密码设置不要过于简单,最起码别和用户名一致或相似就好。

1.官方解决方案

实际上Oracle提供有一个非常好用的安全校验函数,来提升用户密码的复杂性。这个在之前的文章《Oracle 11g 安全加固》中的“1.8.数据库密码安全性校验函数”章节就已经有了确切的解决方案,核心内容如下:

    select limit from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_VERIFY_FUNCTION';
    prompt =============================prompt == 8.数据库密码安全性校验函数 prompt =============================prompt 执行创建安全性校验函数的脚本@?/rdbms/admin/utlpwdmg.sql select limit from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_VERIFY_FUNCTION';

2.删减版解决方案

上面这个自带的安全性校验函数对检查过于严苛,而客户目前的需求就只有一个,不允许密码和用户名完全一样或过于相似就可以了。于是乎,我就从这个脚本中找到这项需求,把其他暂时不需要的部分全部去掉。这样,就得到了如下的删减版脚本:

    RemRem $Header: rdbms/admin/utlpwdmg1.sql /st_rdbms_11.2.0/1 2013/01/31 01:34:11 skayoor Exp $RemRem utlpwdmg.sqlRemRem Copyright (c) 2006, 2013, Oracle and/or its affiliates. Rem All rights reserved. RemRem NAMERem utlpwdmg.sql - script for Default Password Resource LimitsRemRem DESCRIPTIONRem This is a script for enabling the password management featuresRem by setting the default password resource limits.RemRem NOTESRem This file contains a function for minimum checking of passwordRem complexity. This is more of a sample function that the customerRem can use to develop the function for actual complexity checks that the Rem customer wants to make on the new password.RemRem MODIFIED (MM/DD/YY)Rem skayoor 01/17/13 - Backport skayoor_bug-14671375 from mainRem asurpur 05/30/06 - fix - 5246666 beef up password complexity check Rem nireland 08/31/00 - Improve check for username=password. #1390553Rem nireland 06/28/00 - Fix null old password test. #1341892Rem asurpur 04/17/97 - Fix for bug479763Rem asurpur 12/12/96 - Changing the name of password_verify_functionRem asurpur 05/30/96 - New script for default password managementRem asurpur 05/30/96 - CreatedRem-- This script sets the default password resource parameters-- This script needs to be run to enable the password features.-- However the default resource parameters can be changed based -- on the need.-- A default password complexity function is also provided.-- This function makes the minimum complexity checks like-- the minimum length of the password, password not same as the-- username, etc. The user may enhance this function according to-- the need.-- This function must be created in SYS schema.-- connect sys/<password> as sysdba before running the scriptCREATE OR REPLACE FUNCTION verify_function_11G_WJZYY(username varchar2, password varchar2, old_password varchar2) RETURN boolean IS n boolean;
    m integer;
    differ integer;
    isdigit boolean;
    ischar boolean;
    ispunct boolean;
    db_name varchar2(40);
    digitarray varchar2(20);
    punctarray varchar2(25);
    chararray varchar2(52);
    i_char varchar2(10);
    simple_password varchar2(10);
    reverse_user varchar2(32);
    BEGIN digitarray:= '0123456789';
    chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    -- Check if the password is same as the username or username(1-100) IF NLS_LOWER(password) = NLS_LOWER(username) THEN raise_application_error(-20002, 'Password same as or similar to user');
    END IF;
    FOR i IN 1..100 LOOP i_char := to_char(i);
    if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN raise_application_error(-20005, 'Password same as or similar to user name ');
    END IF;
    END LOOP;
    -- Everything is fine;
    return TRUE ;
    RETURN(TRUE);
    END;
    /GRANT EXECUTE ON verify_function_11G_WJZYY TO PUBLIC;
    -- This script alters the default parameters for Password Management-- This means that all the users on the system have Password Management-- enabled and set to the following values unless another profile is -- created with parameter values set to different value or UNLIMITED -- is created and assigned to the user.ALTER PROFILE DEFAULT LIMITPASSWORD_LIFE_TIME 180PASSWORD_VERIFY_FUNCTION verify_function_11G_WJZYY;

我们将这个脚本,遵守之前Oracle的命名方式,将其命名为utlpwdmg1.sql,放在同样的路径下。

这样,我们执行这个脚本就可以创建这个校验函数:

3.测试验证方案

将上面的删减版脚本进行测试并验证功能是否实现:

    --执行脚本创建校验函数@?/rdbms/admin/utlpwdmg1.sql--确认执行成功select limit from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_VERIFY_FUNCTION';
    --将PASSWORD_LIFE_TIME修改为30(选做)ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 30;
    --查询dba_profiles内容select * from dba_profiles order by 1;
    --查询用户状态和过期时间select USERNAME, PASSWORD, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE from dba_users;

测试用户密码不能与用户名相同或者相似,否则会修改失败:

    --密码与用户名一样,修改失败:SYS@jyzhao1 >alter user jingyu identified by jingyu;
    alter user jingyu identified by jingyu*ERROR at line 1:ORA-28003: password verification for the specified password failedORA-20002: Password same as or similar to user--密码与用户名相似,修改失败:SYS@jyzhao1 >alter user jingyu identified by jingyu1;
    alter user jingyu identified by jingyu1*ERROR at line 1:ORA-28003: password verification for the specified password failedORA-20005: Password same as or similar to user name--密码与用户名不一致,修改成功:SYS@jyzhao1 >alter user jingyu identified by alfred;
    User altered.

4.用户最近一次的登录时间

11g默认开启了审计,从aud$表中可以查到用户最近登录的时间:

    --查询数据库时区select property_value from database_properties where property_name='DBTIMEZONE';
    --查询aud$表select MAX(to_char(a.ntimestamp#, 'YYYY-MM-DD HH24:MI:SS')) last_login, u.username from sys.aud$ a, dba_users u where a.USERID(+) = u.username and u.user_id > 90 group by u.username ORDER BY 1;

结果示例:

    SYS@jyzhao1 >select MAX(to_char(a.ntimestamp#, 'YYYY-MM-DD HH24:MI:SS')) last_login, 2 u.username 3 from sys.aud$ a, dba_users u 4 where a.USERID(+) = u.username 5 and u.user_id > 90 6 group by u.username 7 ORDER BY 1;
    LAST_LOGIN USERNAME------------------- ------------------------------2018-04-17 07:16:46 JINGYU TESTTESTTEST XS$NULLSYS@jyzhao1 >

上述查询结果LAST_LOGIN为空的用户,就是在审计中没有记录到该用户的登录信息。

总结

以上所述是小编给大家介绍的提升Oracle用户密码安全性的策略,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!

您可能感兴趣的文章:

  • Oracle数据库用户的密码过期时间如何修改为永不过期
  • Oracle 用户密码有效期的sql语句
  • 修改oracle数据库用户名及密码的方法
  • oracle忘记sys/system/scott用户密码的解决方法
  • Oracle用户密码含有特殊字符导致无法登陆解决方法
  • Oracle的默认用户密码
  • Oracle 添加用户并赋权,修改密码,解锁,删除用户的方法
相关热词搜索: oracle 用户密码安全性 oracle 用户密码