环境:Oracle 11.2.0.4
客户需求:主要背景是数据库中有很多业务用户名,且由于部分用户缺乏安全意识,甚至直接将自己的密码设置为和用户名一样,目前客户期望密码设置不要过于简单,最起码别和用户名一致或相似就好。
1.官方解决方案
实际上Oracle提供有一个非常好用的安全校验函数,来提升用户密码的复杂性。这个在之前的文章《Oracle 11g 安全加固》中的“1.8.数据库密码安全性校验函数”章节就已经有了确切的解决方案,核心内容如下:
1
2
3
4
5
6
7
|
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.删减版解决方案
上面这个自带的安全性校验函数对检查过于严苛,而客户目前的需求就只有一个,不允许密码和用户名完全一样或过于相似就可以了。于是乎,我就从这个脚本中找到这项需求,把其他暂时不需要的部分全部去掉。这样,就得到了如下的删减版脚本:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
Rem
Rem $Header: rdbms/admin/utlpwdmg1.sql /st_rdbms_11.2.0/1 2013/01/31 01:34:11 skayoor Exp $
Rem
Rem utlpwdmg.sql
Rem
Rem Copyright (c) 2006, 2013, Oracle and / or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem utlpwdmg.sql - script for Default Password Resource Limits
Rem
Rem DESCRIPTION
Rem This is a script for enabling the password management features
Rem by setting the default password resource limits.
Rem
Rem NOTES
Rem This file contains a function for minimum checking of password
Rem complexity. This is more of a sample function that the customer
Rem can use to develop the function for actual complexity checks that the
Rem customer wants to make on the new password .
Rem
Rem MODIFIED (MM/DD/YY)
Rem skayoor 01/17/13 - Backport skayoor_bug-14671375 from main
Rem asurpur 05/30/06 - fix - 5246666 beef up password complexity check
Rem nireland 08/31/00 - Improve check for username= password . #1390553
Rem nireland 06/28/00 - Fix null old password test. #1341892
Rem asurpur 04/17/97 - Fix for bug479763
Rem asurpur 12/12/96 - Changing the name of password_verify_function
Rem asurpur 05/30/96 - New script for default password management
Rem asurpur 05/30/96 - Created
Rem
-- 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 script
CREATE 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 LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_VERIFY_FUNCTION verify_function_11G_WJZYY;
|
我们将这个脚本,遵守之前Oracle的命名方式,将其命名为utlpwdmg1.sql,放在同样的路径下。
这样,我们执行这个脚本就可以创建这个校验函数:
3.测试验证方案
将上面的删减版脚本进行测试并验证功能是否实现:
1
2
3
4
5
6
7
8
9
10
|
--执行脚本创建校验函数
@?/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;
|
测试用户密码不能与用户名相同或者相似,否则会修改失败:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
--密码与用户名一样,修改失败:
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 failed
ORA-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 failed
ORA-20005: Password same as or similar to user name
--密码与用户名不一致,修改成功:
SYS@jyzhao1 > alter user jingyu identified by alfred;
User altered.
|
4.用户最近一次的登录时间
11g默认开启了审计,从aud$表中可以查到用户最近登录的时间:
1
2
3
4
5
6
7
8
9
10
|
--查询数据库时区
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;
|
结果示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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$ NULL
SYS@jyzhao1 >
|
上述查询结果LAST_LOGIN为空的用户,就是在审计中没有记录到该用户的登录信息。
总结
以上所述是小编给大家介绍的提升Oracle用户密码安全性的策略,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持!
原文链接:https://www.cnblogs.com/jyzhao/p/8866442.html