I believe that ORACLE.mg is the most common database in the market As the password complexity verification script of ORACLE, SQL also plays an important role in the security of ORACLE. Probably because of my wrong search posture, I rarely find about utlpwdmg on the Internet SQL file (maybe because it's too simple???), I will analyze the file in this simple way. My main purpose is to record my analysis and learning process here. There may be something wrong. Please give me more advice.
catalogue
1. Enter the corresponding path
3. Use sysdba to apply utlpwdmg sql
4, Modify password complexity check function verify_function_11G
preface
As a user of a database, we often have concerns about whether the database is secure, and password complexity and password policy are important components of a secure computing environment. Then this paper takes the password complexity verification script utlpwdmg of Oracle 11g database SQL as an example, this paper analyzes the contents of various parameters in the script and how to modify them to meet the equal assurance standard
1, Utlpwdmg What is SQL?
utlpwdmg.sql is the password verification script that comes with ORACLE database. Its path is generally $ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql
2, Use steps
1. Enter the corresponding path
cd $ORACLE_HOME/RDBMS/ADMIN/
If you do not enter this path, an error will be reported in the application script
SQL> @utlpwdmg.sql SP2-0310: could not open file "utlpwdmg.sql"
2. Log in to the database
sqlplus /nolog
3. Use sysdba to apply utlpwdmg sql
SQL>conn / as sysdba SQL>@utlpwdmg.sql
If the echo is as follows, the application is successful
Function created. Authorization succeeded. Configuration file changed Function created. Authorization succeeded.
4. Result query
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
You can see the password_ VERIFY_ The function field has been specified as VERIFY_FUNCTION_11G # password complexity check function.
3, Utlpwdmg SQL analysis
Open utlpwdmg SQL, we can see that some comments are made in the header of the file to explain the function of the file
"This is a script that enables the password management function by setting the default password resource limit". Of course, this is not the focus here.
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
Next, a password complexity check function verify is defined in the file_ FUNCTION_ 11g, and pre declared some variables to be used and their variable types.
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);
Next is the function body, which is the key. BEGIN starts the function body and assigns values to the variables digitarray and chararray, which are used to detect whether the password contains numbers and upper and lower case letters.
BEGIN digitarray:= '0123456789'; chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
Then we officially entered the part of password complexity detection. First, we detected the password length, which is required to be greater than or equal to 8 bits. If the password length is less than 8 bits, we will put back an error - 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;Then compare the similarity between the password and the user name. If the password is the same as the user name or the number 1-100 spliced after the user name, errors - 20002, - 20005 will be returned
-- 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;Judge whether the reverse order of password and user name is the same. If they are the same, error - 20003 will be returned
-- 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;
After comparing the similarity between the password and the server name, if the password is the same as the server name or the number 1-100 spliced after the server name, errors - 20004, - 20005 will be returned
-- 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;Compare whether the password is weak passwords such as' welcome1 ',' database1 ',' account1 'and' user1234 '. If so, an error - 20006 will be returned
-- 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;
Compare whether the password is 1-100 on oracle or oracle splicing. If it is the same, an error - 20007 will be returned
-- 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;Next is verify_ function_ The 11g function determines whether the password contains numbers and characters
Judge whether the password contains at least one number (0-9). If not, the error - 20008 will be returned
-- 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;Judge whether the password contains at least one letter (a-z, A-Z). If not, error - 20009 will be returned
-- 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>>
At this point, VERIFY_FUNCTION_11G is the end of the password complexity check function.
Judge whether the new password has at least 3 different characters from the original password. If it does not exist, an error - 20011 will be returned
-- 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;
/Give PUBLIC user verify_ The function of function executes the permission and changes the profile file of ORACLE. The maximum usage period of the configured password is 180 days, the password expiration locking time is 7 days (that is, if the password is not changed within 7 days after the usage period of 180 days), the password reuse time is UNLIMITED The maximum password reuse time is UNLIMITED (when both parameters are UNLIMITED, the password can be reused at will. When both parameters are specified values, the password can be reused only when both parameters are met. If one of the two parameters is not UNLIMITED, the password cannot be reused), the number of login failure locks is 10, the password locking time is one day, and the password complexity check function is 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;
The above is utlpwdmg A standard composition template in the SQL file. The subsequent content is similar to it. I won't repeat it here.
You can see verify_function_11G this function only makes the simplest judgment and filtering on the password complexity, which can not meet the information security baseline requirements under the current situation. The general verification framework has been given in the document. We only need to verify the password complexity verification function_ function_ 11g can make some minor modifications to make it meet the information security baseline requirements under the current situation.
4, Modify password complexity check function verify_function_11G
In order to make the password meet the requirements: it contains at least three of the four types: uppercase letters, lowercase letters, numbers and special characters. First, we should modify the variable chararray varchar2(52), divide it into upperchar varchar2(26) and lowerchar varchar2(26), and newly declare the variables punctarray varchar2(25), flagtypes and 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);
Assign values to four types of variables: digitarray, punctarray, upperchar, lowerchar and flag types
BEGIN digitarray:= '0123456789'; upperchar:= 'abcdefghijklmnopqrstuvwxyz'; lowerchar:= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; punctarray:='!"#$%&()``*+,-/:;<=>?_'; flagfortypes:=0
Next, we need to verify the password complexity function_ function_ 11g check whether it contains numbers and characters for modification
-- 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;
Thus, VERIFY_FUNCTION_11G, the password complexity check function, will detect the number of character categories of the password. If the number of characters is less than 3, an error will be reported. Next, we also need to modify the configuration of the profile file, change the login failure locking times to 5 times, and the password locking time to 10 minutes.
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;
At this point, it can initially meet the information security baseline requirements under the current situation.
You need to repeat the content in Section 2 again for utlpwdmg The above modifications will not take effect until SQL is applied
summary
That's what we're going to talk about today. This article just briefly introduces oracle's utlpwdmg The use and modification process of SQL components, and oracle also provides a large number of scripts and components. You are welcome to add them together