從4個方面實戰oracle的密碼操作

   

較好的實踐是，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) < 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
       <<findchar>>
       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
       <<findpunct>>
       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;
   
       <<endsearch>>
       -- 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) < 3 THEN
           IF length(password) < length(old_password) THEN
             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 < 3 THEN
             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;
    /