从4个方面实战Oracle的密码操作

程序员文章站 2022-06-17 12:20:23

...

较好的实践是，Oracle的密码操作通过profile来实现，而资源则是通过资源消费组来控制，profile其实是种限制。
通过profile来控制密码的使用，大抵有四：

1）密码的历史
在这里，有两个参数：password_reuse_time和password_reuse_max，比较好的实践是，这两个参数当关联起来使用。如：password_reuse_time=30，password_reuse_max=10，

用户可以在30天以后重用该密码，要求密码必须被改变超过10次。
实验：
会话1：sys
sys@ORCL> create profile p1 limit password_reuse_time 1/1440 password_reuse_max 1;
Profile created.

sys@ORCL> alter user scott profile p1;

User altered.

sys@ORCL> alter user scott password expire;

User altered.

sys@ORCL> alter profile p1 limit password_reuse_time 5/1440 password_reuse_max 1;--5分钟后可重用该密码，但这期间必须要被改成其他密码一次

Profile altered.

sys@ORCL> alter user scott password expire;

User altered.
会话2：scott
scott@ORCL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[Oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 3 01:11:09 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

idle> conn scott/Oracle
ERROR:
ORA-28001: the password has expired

Changing password for scott
New password: --使用原密码，即Oracle
Retype new password:
ERROR:
ORA-28007: the password cannot be reused

Password unchanged
idle> conn scott/Oracle
ERROR:
ORA-28001: the password has expired

Changing password for scott
New password: --使用新密码，改成think
Retype new password:
Password changed
Connected.
会话1：sys
sys@ORCL> alter user scott password expire;

User altered.
会话2：scott
scott@ORCL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[Oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 3 01:19:04 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

idle> conn scott/think
ERROR:
ORA-28001: the password has expired

Changing password for scott
New password: --使用最早的密码，即Oracle
Retype new password:
Password changed
Connected.
scott@ORCL>

2）密码的登入校验
在这方面，也有两个参数:
failed_login_attempts:锁定前允许的最大失败登录次数
password_lock_time:锁定时间
实验：
会话1：sys
sys@ORCL> drop profile p1 cascade;

Profile dropped.

sys@ORCL> create profile p1 limit failed_login_attempts 1 password_lock_time 1/1440;--失败一次就被锁，被锁1分钟

Profile created.

sys@ORCL> alter user scott profile p1;

User altered.
会话2：scott
[Oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 3 01:42:46 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

idle> conn scott/think
ERROR:
ORA-01017: invalid username/password; logon denied

idle> conn scott/Oracle
ERROR:
ORA-28000: the account is locked

idle> conn scott/Oracle --1分钟之后
Connected.

3）密码的生命周期
同样地，这也是有两个参数：
password_life_time:密码的寿命
password_grace_time:宽限时间，特指将达到寿命前的那些时光
实验：
会话1：sys
sys@ORCL> drop profile p1 cascade;

Profile dropped.

sys@ORCL> create profile p1 limit password_life_time 2/1440 password_grace_time 2/1440;

Profile created.

sys@ORCL> alter user scott profile p1;

User altered.
会话2：scott
[Oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 3 01:56:59 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

idle> conn scott/Oracle
ERROR:
ORA-28002: the password will expire within 0 days

Connected.

4）密码的复杂性
在$Oracle_HOME/rdbms/admin/utlpwdmg.sql,有个密码函数，借此，则可控制密码复杂性
现将该函数摘入如下：
CREATE OR REPLACE FUNCTION verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);

BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;?_';

-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;

-- Check for the minimum length of the password
IF length(password) raise_application_error(-20002, 'Password length less than 4');
END IF;

-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'Oracle', 'computer', 'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;

-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 2. Check for the character
>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
-- 3. Check for the punctuation
>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;

>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);

IF abs(differ) IF length(password) m := length(password);
ELSE
m := length(old_password);
END IF;

differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;

IF differ raise_application_error(-20004, 'Password should differ by at \
least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/