Procedure for creating a PASSWORD_VERIFY_FUNCTION in Oracle11gR2 to verify the Password Complexity

Procedure for creating a PASSWORD_VERIFY_FUNCTION in Oracle11gR2 to verify the Password Complexity

Procedure of creating a PASSWORD_VERIFY_FUNCTION for Oracle11g R2 to verify the Password Complexity

Run the test environment: Database Server: Oracle Linux 5.8 + Oracle 11g R2 Database

Related tools: PL/SQL software (connecting to the Oracle database in Linux) and SecureCRT software (remotely connecting to the Linux Server)

Detailed steps:

1. Connect to the Linux database server, switch to the Oracle Database User desktop, open the terminal, and enter the environment variable $ ORACLE_HOME directory.

Last login: Fri Dec 11 13:26:18 2015 from 192.168.1.100
[Root @ Linux host name ~] # Su-oracle
[Oracle @ Linux host name dbhome_1] $ cd $ ORACLE_HOME/rdbms/admin
[Oracle @ Linux host name admin] $

2. view the default password complexity function script provided by the Oracle11g database (/rdbms/admin/utlpwdmg. SQL file in the Oracle Installation Directory)

[Oracle @ Linux host name admin] $ cat $ ORACLE_HOME/rdbms/admin/utlpwdmg. SQL

For more information about the script, see the end of the article.

3. log on to the Oracle database and run the default password complexity function script provided by the Oracle11g database.

[Oracle @ Linux host name admin] $ sqlplus/nologSQL * Plus: Release 11.2.0.1.0 Production on Fri Dec 11 13:33:58 2015 Copyright (c) 1982,200 9, Oracle. all rights reserved. SQL> conn/as sysdbaConnected. SQL> @? /Rdbms/admin/utlpwdmg. sqlFunction created. Profile altered. Function created. SQL>

4. Create a resource file in PL/SQL and execute the following statement:

Create profile resource file name LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME 600 -- the system automatically disconnects if the device is not active for 10 hours.
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10 -- specify the number of failed logon attempts to lock the user as 10. If the number of failed logon attempts exceeds 10, the system is automatically locked.
PASSWORD_LIFE_TIME 180 -- specify the number of days a user can use the same password lock as 180 days
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME 1 -- specify the number of days a user is locked.
PASSWORD_GRACE_TIME 10 -- the number of days before the database sends a warning to log on Failure
PASSWORD_VERIFY_FUNCTION verify_function_11G

5. Test and update the User Password

-- Create a user and use a custom configuration file
Create user Username identified by password default tablespace name temporary tablespace name profile resource file name;

-- User authorization
Grant connect, resource, exp_full_database, imp_full_database to user name;

-- Update the user password as a simple string
Alter user Username identified by 123456;

-- Update the user password to a complex string
Alter user Username identified by Csdn_20151211;

6. Conclusion: The simple password cannot be updated, and the complex password is successfully updated.

Appendix: $ ORACLE_HOME/rdbms/admin/utlpwdmg. Content of the SQL script source file

RemRem $Header: utlpwdmg.sql 02-aug-2006.08:18:05 asurpur Exp $RemRem utlpwdmg.sqlRemRem Copyright (c) 2006, Oracle. All rights reserved.RemRem NAMERem utlpwdmg.sql - script for Default Password Resource LimitsRemRem DESCRIPTIONRem This is a script for enabling the password management featuresRem by setting the default password resource limits.RemRem NOTESRem This file contains a function for minimum checking of passwordRem complexity. This is more of a sample function that the customerRem can use to develop the function for actual complexity checks that theRem customer wants to make on the new password.RemRem MODIFIED (MM/DD/YY)Rem asurpur 05/30/06 - fix - 5246666 beef up password complexity checkRem nireland 08/31/00 - Improve check for username=password. #1390553Rem nireland 06/28/00 - Fix null old password test. #1341892Rem asurpur 04/17/97 - Fix for bug479763Rem asurpur 12/12/96 - Changing the name of password_verify_functionRem asurpur 05/30/96 - New script for default password managementRem asurpur 05/30/96 - CreatedRem-- 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/ as sysdba before running the scriptCREATE OR REPLACE FUNCTION verify_function_11G(username varchar2,password varchar2,old_password varchar2)RETURN boolean ISn boolean;m integer;differ integer;isdigit boolean;ischar boolean;ispunct boolean;db_name varchar2(40);digitarray varchar2(20);punctarray varchar2(25);chararray varchar2(52);i_char varchar2(10);simple_password varchar2(10);reverse_user varchar2(32);BEGINdigitarray:= '0123456789';chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';-- Check for the minimum length of the passwordIF length(password) < 8 THENraise_application_error(-20001, 'Password length less than 8');END IF;-- Check if the password is same as the username or username(1-100)IF NLS_LOWER(password) = NLS_LOWER(username) THENraise_application_error(-20002, 'Password same as or similar to user');END IF;FOR i IN 1..100 LOOPi_char := to_char(i);if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THENraise_application_error(-20005, 'Password same as or similar to user name ');END IF;END LOOP;-- Check if the password is same as the username reversedFOR i in REVERSE 1..length(username) LOOPreverse_user := reverse_user || substr(username, i, 1);END LOOP;IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THENraise_application_error(-20003, 'Password same as username reversed');END IF;-- 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) THENraise_application_error(-20004, 'Password same as or similar to server name');END IF;FOR i IN 1..100 LOOPi_char := to_char(i);if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THENraise_application_error(-20005, 'Password same as or similar to server name ');END IF;END LOOP;-- 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') THENraise_application_error(-20006, 'Password too simple');END IF;-- Check if the password is the same as oracle (1-100)simple_password := 'oracle';FOR i IN 1..100 LOOPi_char := to_char(i);if simple_password || i_char = NLS_LOWER(password) THENraise_application_error(-20007, 'Password too simple ');END IF;END LOOP;-- Check if the password contains at least one letter, one digit-- 1. Check for the digitisdigit:=FALSE;m := length(password);FOR i IN 1..10 LOOPFOR j IN 1..m LOOPIF substr(password,j,1) = substr(digitarray,i,1) THENisdigit:=TRUE;GOTO findchar;END IF;END LOOP;END LOOP;IF isdigit = FALSE THENraise_application_error(-20008, 'Password must contain at least one digit, one character');END IF;-- 2. Check for the character<>ischar:=FALSE;FOR i IN 1..length(chararray) LOOPFOR j IN 1..m LOOPIF substr(password,j,1) = substr(chararray,i,1) THENischar:=TRUE;GOTO endsearch;END IF;END LOOP;END LOOP;IF ischar = FALSE THENraise_application_error(-20009, 'Password must contain at least one \digit, and one character');END IF;<>-- Check if the password differs from the previous password by at least-- 3 lettersIF old_password IS NOT NULL THENdiffer := length(old_password) - length(password);differ := abs(differ);IF differ < 3 THENIF length(password) < length(old_password) THENm := length(password);ELSEm := length(old_password);END IF;FOR i IN 1..m LOOPIF substr(password,i,1) != substr(old_password,i,1) THENdiffer := differ + 1;END IF;END LOOP;IF differ < 3 THENraise_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;/-- 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 LIMITPASSWORD_LIFE_TIME 180PASSWORD_GRACE_TIME 7PASSWORD_REUSE_TIME UNLIMITEDPASSWORD_REUSE_MAX UNLIMITEDFAILED_LOGIN_ATTEMPTS 10PASSWORD_LOCK_TIME 1PASSWORD_VERIFY_FUNCTION verify_function_11G;-- Below is the older version of the script-- 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/ as sysdba before running the scriptCREATE OR REPLACE FUNCTION verify_function(username varchar2,password varchar2,old_password varchar2)RETURN boolean ISn boolean;m integer;differ integer;isdigit boolean;ischar boolean;ispunct boolean;digitarray varchar2(20);punctarray varchar2(25);chararray varchar2(52);BEGINdigitarray:= '0123456789';chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';punctarray:='!"#$%&()``*+,-/:;<=>?_';-- Check if the password is same as the usernameIF NLS_LOWER(password) = NLS_LOWER(username) THENraise_application_error(-20001, 'Password same as or similar to user');END IF;-- Check for the minimum length of the passwordIF length(password) < 4 THENraise_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') THENraise_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 digitisdigit:=FALSE;m := length(password);FOR i IN 1..10 LOOPFOR j IN 1..m LOOPIF substr(password,j,1) = substr(digitarray,i,1) THENisdigit:=TRUE;GOTO findchar;END IF;END LOOP;END LOOP;IF isdigit = FALSE THENraise_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) LOOPFOR j IN 1..m LOOPIF substr(password,j,1) = substr(chararray,i,1) THENischar:=TRUE;GOTO findpunct;END IF;END LOOP;END LOOP;IF ischar = FALSE THENraise_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) LOOPFOR j IN 1..m LOOPIF substr(password,j,1) = substr(punctarray,i,1) THENispunct:=TRUE;GOTO endsearch;END IF;END LOOP;END LOOP;IF ispunct = FALSE THENraise_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 lettersIF old_password IS NOT NULL THENdiffer := length(old_password) - length(password);IF abs(differ) < 3 THENIF length(password) < length(old_password) THENm := length(password);ELSEm := length(old_password);END IF;differ := abs(differ);FOR i IN 1..m LOOPIF substr(password,i,1) != substr(old_password,i,1) THENdiffer := differ + 1;END IF;END LOOP;IF differ < 3 THENraise_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;/-- 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.-- Enable this if you want older version of the Password Profile parameters-- ALTER PROFILE DEFAULT LIMIT-- PASSWORD_LIFE_TIME 60-- PASSWORD_GRACE_TIME 10-- PASSWORD_REUSE_TIME 1800-- PASSWORD_REUSE_MAX UNLIMITED-- FAILED_LOGIN_ATTEMPTS 3-- PASSWORD_LOCK_TIME 1/1440-- PASSWORD_VERIFY_FUNCTION verify_function;