Oracle數(shù)據(jù)庫(kù)密碼復(fù)雜度校驗(yàn)?zāi)_本utlpwdmg.sql詳細(xì)解析

更新時(shí)間：2024年04月28日 09:31:24 作者：weixin_43900811

我們都知道密碼策略加固的參數(shù)一般包括密碼長(zhǎng)度、復(fù)雜度檢測(cè)、最大最小使用時(shí)間、過期警報(bào)時(shí)間、最大登錄失敗次數(shù)以及鎖定時(shí)間等設(shè)置,下面這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫(kù)密碼復(fù)雜度校驗(yàn)?zāi)_本utlpwdmg.sql詳細(xì)解析的相關(guān)資料,需要的朋友可以參考下

前言

ORACLE數(shù)據(jù)庫(kù)系統(tǒng)可以說是當(dāng)今市面上最常見的數(shù)據(jù)庫(kù)系統(tǒng)了，相信各位在工作和學(xué)習(xí)的過程中經(jīng)常能接觸到ORACLE數(shù)據(jù)庫(kù)，那么utlpwdmg.sql作為ORACLE自帶的密碼復(fù)雜度校驗(yàn)?zāi)_本來說，對(duì)ORACLE的安全也舉足輕重。可能由于我搜索姿勢(shì)不對(duì)，在網(wǎng)上很少找到關(guān)于utlpwdmg.sql這個(gè)文件的文章（可能因?yàn)樘?jiǎn)單了？？？），在下便在此淺顯的分析下該文件。本人主要目的是在此記錄一下自己分析和學(xué)習(xí)的過程，可能會(huì)有不對(duì)的地方，也請(qǐng)各位大佬多多指點(diǎn)。

作為一個(gè)使用數(shù)據(jù)庫(kù)的人，我們經(jīng)常會(huì)對(duì)數(shù)據(jù)庫(kù)是否安全產(chǎn)生顧慮，而密碼復(fù)雜度和密碼策略則是安全計(jì)算環(huán)境的重要組成部分。那么本文就以O(shè)RACLE11g數(shù)據(jù)庫(kù)的密碼復(fù)雜度校驗(yàn)?zāi)_本utlpwdmg.sql為例，分析該腳本中各項(xiàng)參數(shù)的內(nèi)容，以及如何修改使其符合等保標(biāo)準(zhǔn)

一、utlpwdmg.sql是什么？

utlpwdmg.sql是ORACLE數(shù)據(jù)庫(kù)自帶的密碼校驗(yàn)?zāi)_本。其路徑一般為$ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql

二、使用步驟

1.進(jìn)入對(duì)應(yīng)路徑

cd $ORACLE_HOME/RDBMS/ADMIN/

如不進(jìn)入該路徑，則會(huì)在應(yīng)用腳本時(shí)報(bào)錯(cuò)

SQL> @utlpwdmg.sql
SP2-0310: 無法打開文件 "utlpwdmg.sql"

2.登錄數(shù)據(jù)庫(kù)

sqlplus /nolog

3.使用sysdba應(yīng)用utlpwdmg.sql

SQL>conn / as sysdba
SQL>@utlpwdmg.sql

如回顯如下則說明應(yīng)用成功

函數(shù)已創(chuàng)建。


授權(quán)成功。


配置文件已更改


函數(shù)已創(chuàng)建。


授權(quán)成功。

4.結(jié)果查詢

SQL>  select * from dba_profiles where profile='DEFAULT';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED
DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION_11G
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7

可以看到 PASSWORD_VERIFY_FUNCTION 字段已經(jīng)被指定為了 VERIFY_FUNCTION_11G 密碼復(fù)雜度校驗(yàn)函數(shù)。

三、utlpwdmg.sql分析

打開utlpwdmg.sql，我們可以看到在文件頭部做了一些注釋，說明了該文件的作用，表明

“這是一個(gè)通過設(shè)置默認(rèn)密碼資源限制來啟用密碼管理功能的腳本”，當(dāng)然這里不是重點(diǎn)。

Rem
Rem $Header: rdbms/admin/utlpwdmg.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

接著，文件中定義了一個(gè)密碼復(fù)雜度校驗(yàn)函數(shù) VERIFY_FUNCTION_11G ，并預(yù)先聲明了一些將要使用的變量及其變量類型。

CREATE OR REPLACE FUNCTION verify_function_11G
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS 
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   db_name varchar2(40);
   digitarray varchar2(20);
   chararray varchar2(52);
   i_char varchar2(10);
   simple_password varchar2(10);
   reverse_user varchar2(32);

接著是函數(shù)體部分，這塊兒才是重點(diǎn) ，BEGIN開始函數(shù)體，為變量digitarray、chararray賦值，用于后續(xù)檢測(cè)密碼中是否含有數(shù)字和大小寫字母。

BEGIN 
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

接著正式進(jìn)入了密碼復(fù)雜度檢測(cè)的部分，首先對(duì)密碼長(zhǎng)度進(jìn)行了檢測(cè)，要求密碼長(zhǎng)度大于等于8位，若密碼長(zhǎng)度小于8位，則放回一個(gè)錯(cuò)誤-20001

   -- Check for the minimum length of the password
   IF length(password) < 8 THEN
      raise_application_error(-20001, 'Password length less than 8');
   END IF;

接著比對(duì)了密碼和用戶名的相似度，若密碼與用戶名相同或與用戶名后拼接上1-100的數(shù)字相同，則返回錯(cuò)誤 -20002，-20005

   -- 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;

判斷密碼與用戶名的逆序是否相同，若相同則返回錯(cuò)誤 -20003

   -- Check if the password is same as the username reversed
   
   FOR i in REVERSE 1..length(username) LOOP
     reverse_user := reverse_user || substr(username, i, 1);
   END LOOP;
   IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
     raise_application_error(-20003, 'Password same as username reversed');
   END IF;

比對(duì)了密碼和服務(wù)器名的相似度，若密碼與服務(wù)器名相同或與服務(wù)器名后拼接上1-100的數(shù)字相同，則返回錯(cuò)誤 -20004，-20005

   -- Check if the password is the same as server name and or servername(1-100)
   select name into db_name from sys.v$database;
   if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
      raise_application_error(-20004, 'Password same as or similar to server name');
   END IF;
   FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to server name ');
      END IF;
    END LOOP;

比對(duì)密碼是否為‘welcome1’、‘database1’、‘account1’、‘user1234’這類弱口令，若是則返回錯(cuò)誤-20006

   -- 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 ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
      raise_application_error(-20006, 'Password too simple');
   END IF;

比對(duì)密碼是否為oracle或oracle拼接上1-100的數(shù)字，若相同則返回錯(cuò)誤 -20007

   -- Check if the password is the same as oracle (1-100)
    simple_password := 'oracle';
    FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if simple_password || i_char = NLS_LOWER(password) THEN
        raise_application_error(-20007, 'Password too simple ');
      END IF;
    END LOOP;

接下來是verify_function_11G函數(shù)判斷密碼是否含有數(shù)字和字符的部分

判斷密碼是否包含至少一個(gè)數(shù)字(0-9)，若不包含則返回錯(cuò)誤-20008

 -- Check if the password contains at least one letter, one digit
   -- 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(-20008,'Password should contain at least one \
              digit, one character');
   END IF;

判斷密碼是否包含至少一個(gè)字母(a-z，A-Z)，若不包含則返回錯(cuò)誤-20009

   -- 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 endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20009, 'Password should contain at least one \
              digit, one character');
   END IF;
   <<endsearch>>

到這一步，VERIFY_FUNCTION_11G這個(gè)密碼復(fù)雜度校驗(yàn)函數(shù)就結(jié)束了。

判斷新密碼是否與原密碼至少存在3個(gè)不同的字符，如果不存在則返回錯(cuò)誤-20011

   -- 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);

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

       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(-20011, 'Password should differ from the \
            old password by at least 3 characters');
       END IF;
     END IF;
   END IF;
   -- Everything is fine; return TRUE ;   
   RETURN(TRUE);
END;
/

賦予PUBLIC用戶verify_function的函數(shù)執(zhí)行權(quán)限，并更改ORACLE的profile文件，配置密碼的最常使用期限為180天、密碼過期鎖定時(shí)間為7天（即密碼超過180天使用期限后若7天內(nèi)不更改密碼則會(huì)將該賬戶鎖定）、密碼重用時(shí)間為不限制、密碼重用最大時(shí)間為不限制（兩個(gè)參數(shù)都為UNLIMITED時(shí)，密碼可以隨意重用，兩參數(shù)均為指定值時(shí)，必須都滿足才可以重用密碼。兩參數(shù)有其中一個(gè)不為UNLIMITED，則密碼不能重用）、登陸失敗鎖定次數(shù)為10次、密碼鎖定時(shí)間為一天、密碼復(fù)雜度校驗(yàn)函數(shù)為verify_function_11G。

GRANT EXECUTE ON verify_function_11G 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_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;

以上就是 utlpwdmg.sql文件里面的一個(gè)標(biāo)準(zhǔn)組成模板，后續(xù)內(nèi)容與其大同小異，在此不做贅述。

可以看到verify_function_11G該函數(shù)僅對(duì)密碼復(fù)雜度做了最簡(jiǎn)單的判斷和過濾，完全不能符合當(dāng)前情況下的信息安全基線要求。文件中已經(jīng)給出了大體的校驗(yàn)框架，我們僅僅需要對(duì)密碼復(fù)雜度校驗(yàn)函數(shù)verify_function_11G做一些小小的修改就可以使其符合當(dāng)前情況下的信息安全基線要求。

四、修改密碼復(fù)雜度校驗(yàn)函數(shù)verify_function_11G

為了使得密碼符合要求：至少包含大寫字母、小寫字母、數(shù)字、特殊字符四種其中的三種，首先我們應(yīng)修改變量chararray varchar2(52)，將其分割成upperchar varchar2(26)和lowerchar varchar2(26),并新聲明變量punctarray varchar2(25)、flagfortypes integer

CREATE OR REPLACE FUNCTION verify_function_11G
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS 
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   db_name varchar2(40);
   digitarray varchar2(20);
   --chararray varchar2(56);
   punctarray varchar2(25);
   upperchar varchar2(26);
   lowerchar varchar2(26);
   flagfortypes  integer;
   i_char varchar2(10);
   simple_password varchar2(10);
   reverse_user varchar2(32);

分別為四類變量賦值 digitarray punctarray upperchar lowerchar和標(biāo)志 flagfortypes

BEGIN 
   digitarray:= '0123456789';
   upperchar:= 'abcdefghijklmnopqrstuvwxyz';
   lowerchar:= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray:='!"#$%&()``*+,-/:;<=>?_'; 
   flagfortypes:=0

接下來，我們需要對(duì)密碼復(fù)雜度函數(shù)verify_function_11G的檢查是否含有數(shù)字和字符部分進(jìn)行修改

   -- Check if the password contains 3 types of the listed array:integer,upperchar,lowerchar,punct

   -- 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;
            flag=flag+1;
            GOTO findupper;
         END IF;
      END LOOP;
   END LOOP;

   -- 2. Check for the uppercharacter
   <<findupper>>
   ischar:=FALSE;
   FOR i IN 1..length(upperchar) LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(upperchar,i,1) THEN
            ischar:=TRUE;
            flag=flag+1;
            GOTO findlower;
         END IF;
      END LOOP;
   END LOOP;

   -- 3. Check for the lowercharacter
   <<findlower>>
   ischar:=FALSE;
   FOR i IN 1..length(lowerchar) LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(lowerchar,i,1) THEN
            ischar:=TRUE;
            flag=flag+1;
            GOTO findpunct;
         END IF;
      END LOOP;
   END LOOP;

   -- 4. Check for the punctarray
   <<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;
            flag=flag+1;
            GOTO checktypes;
         END IF;
      END LOOP;
   END LOOP;

   -- 5. Check for the types
   <<checktypes>>
   IF flag <3 THEN
              raise_application_error(-20008, 'Password should contain at least 3 types of the listed array:integer,upperchar,lowerchar,punct');
   END IF;

這樣，VERIFY_FUNCTION_11G這個(gè)密碼復(fù)雜度校驗(yàn)函數(shù)就會(huì)對(duì)密碼進(jìn)行字符類別數(shù)量檢測(cè)，如果字符數(shù)量小于3，則會(huì)報(bào)錯(cuò)。接下來我們還需要修改profile文件的配置將登陸失敗鎖定次數(shù)改為5次、密碼鎖定時(shí)間為10分鐘。

GRANT EXECUTE ON verify_function_11G 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_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1/144
PASSWORD_VERIFY_FUNCTION verify_function_11G;

到此，已經(jīng)可以初步符合當(dāng)前情況下的信息安全基線要求。

需要再次重復(fù)第二節(jié)中的內(nèi)容對(duì)utlpwdmg.sql進(jìn)行應(yīng)用，以上的修改才會(huì)生效

總結(jié)

到此這篇關(guān)于Oracle數(shù)據(jù)庫(kù)密碼復(fù)雜度校驗(yàn)?zāi)_本utlpwdmg.sql的文章就介紹到這了,更多相關(guān)密碼復(fù)雜度校驗(yàn)?zāi)_本utlpwdmg.sql內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: