从4个方面实战oracle的密码操作(二)
acle --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;
www.2cto.com
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:='!"#$%&()``*+,-/:;<=> _';
www.2cto.com
-- 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) < 4 THEN
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
<> ww