CREATE OR REPLACE FUNCTION pentest_password_verify
	( fp_username VARCHAR2
	, fp_password VARCHAR2
	, fp_old_password VARCHAR2
	)
	RETURN boolean
-------------------------------------------------------------------------------
--       Author: Simon Fletcher, Pentest Limited
-- Contributors: Andy and Howard of the Oracle Ethical Hacking Team,
--               Tim Fenby of Pentest
--      Program: Password verification function 
--               This function is designed for use with the PROFILE resource 
--               parameter PASSWORD_VERIFY_FUNCTION.  It is intended as an 
--               alternative to the Oracle supplied VERIFY_FUNCTION (created
--               by the UTLPWDMG.SQL script).
--      Release: 03 April 2008
--    $Revision: 22 $
--        $Date: 2008-04-03 10:51:55 +0100 (Thu, 03 Apr 2008) $
-------------------------------------------------------------------------------
-- Copyright 2007, Simon Fletcher, email: simon.fletcher@pentest.co.uk       --
--                                                                           --
--    This program is free software: you can redistribute it and/or modify   --
--    it under the terms of the GNU General Public License as published by   --
--    the Free Software Foundation, either version 3 of the License, or      --
--    (at your option) any later version.                                    --
--                                                                           --
--    This program is distributed in the hope that it will be useful,        --
--    but WITHOUT ANY WARRANTY; without even the implied warranty of         --
--    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          --
--    GNU General Public License for more details.                           --
--                                                                           --
--    For a copy of the GNU General Public License see                       --
--    <http://www.gnu.org/licenses/>.                                        --
-------------------------------------------------------------------------------
--    Note: Compatibile with Oracle10g and onwards
--
--   Usage: 
--	Step 1: Amend the constants below to reflect your password policy
--	Step 2: Create the function in your SYS schema
--	Step 3: Specify the function name in your database profile
--              e.g. 
--                 CREATE PROFILE secure LIMIT
--                 FAILED_LOGIN_ATTEMPTS 5
--                 PASSWORD_LIFE_TIME 30
--                 PASSWORD_GRACE_TIME 7
--                 PASSWORD_REUSE_TIME UNLIMITED 
--                 PASSWORD_REUSE_MAX UNLIMITED 
--                 PASSWORD_LOCK_TIME 1/24
--                 PASSWORD_VERIFY_FUNCTION pentest_password_verify;
--	Step 4: Assign the profile to your users 
--              e.g.
--                 ALTER USER system PROFILE secure;
-------------------------------------------------------------------------------
IS
	-- Types
	TYPE char_idx_tabtype IS TABLE OF BINARY_INTEGER INDEX BY VARCHAR2(1);
	TYPE password_tabtype IS TABLE OF VARCHAR2(30);

	-- Exceptions
        contains_forbidden_word   EXCEPTION;
        contains_rev_username     EXCEPTION;
        contains_username         EXCEPTION;
        not_diverse               EXCEPTION;
        not_enough_change         EXCEPTION;
        similar_to_old_password   EXCEPTION;
        similar_to_forbidden_word EXCEPTION;
        similar_to_username       EXCEPTION;
        too_few_alphas            EXCEPTION;
        too_few_lowers            EXCEPTION;
        too_few_numerics          EXCEPTION;
        too_few_punct             EXCEPTION;
        too_few_spec_punct        EXCEPTION;
        too_few_std_punct         EXCEPTION;
        too_few_uppers            EXCEPTION;
        too_many_dup_characters   EXCEPTION;
        too_short                 EXCEPTION;

	-- Enforce password to not contain the username
	c_chk_eq_username CONSTANT BOOLEAN := TRUE;

	-- Enforce password to not contain the username in reverse
	c_chk_eq_rev_username CONSTANT BOOLEAN := TRUE;

	-- Enforce to not be similar to the username
	c_chk_like_username CONSTANT BOOLEAN := TRUE;

	-- Enforce to not be similar to the previous password
	c_chk_like_old_password CONSTANT BOOLEAN := FALSE;

	-- Password minimum length
	c_min_length CONSTANT BINARY_INTEGER := 10;

	-- Enforce password to not contain a forbidden word
	c_chk_eq_forbidden CONSTANT BOOLEAN := TRUE;

	-- Enforce password to not be like a forbidden word
	c_chk_like_forbidden CONSTANT BOOLEAN := FALSE;

	-- Add dbname to forbidden word list
	c_add_dbname_to_forbidden CONSTANT BOOLEAN := TRUE;

	-- Add host name to forbidden word list
	c_add_hostname_to_forbidden CONSTANT BOOLEAN := TRUE;

	-- Add month to forbidden word list
	c_add_month_to_forbidden CONSTANT BOOLEAN := TRUE;

	-- Minimum characters from particular charactersets
	-- NOTE: c_min_lower and c_min_upper only checked with case sensitive passwords
	c_min_alpha          CONSTANT BINARY_INTEGER := 1;
	c_min_lower          CONSTANT BINARY_INTEGER := 1;
	c_min_upper          CONSTANT BINARY_INTEGER := 1;
	c_min_numeric        CONSTANT BINARY_INTEGER := 1;
	c_min_punct          CONSTANT BINARY_INTEGER := 1;
	c_min_standard_punct CONSTANT BINARY_INTEGER := 0;
	c_min_special_punct  CONSTANT BINARY_INTEGER := 0;

	-- Maximum number of times a single character can be used within the password
	-- NOTE: This check is case insensitive
	c_max_char_duplication CONSTANT BINARY_INTEGER := 6;

	-- Minimum number of different characters used
	-- NOTE: This check is case insensitive
	c_min_char_diversity CONSTANT BINARY_INTEGER := 5;

	-- Minimum number of differing characters from previous password
	-- NOTE: This check is case insensitive
	c_min_diff_from_prev CONSTANT BINARY_INTEGER := 4;

	-- Forbidden passwords
	-- NOTE: Customise this list for your organisation
	v_password_tab password_tabtype := password_tabtype
		( 'WELCOME'
		, 'PASSWORD'
		, 'ORACLE'
		, 'DATABASE'
		, 'LETMEIN'
		, 'FORGOTTEN'
		, 'MANAGER'
		, 'CHANGE_ON_INSTALL'
		, 'ABCDEF'
		, 'QWERTY'
		, '123456'
		, 'PENTEST'
		);

	-- Working variables
	v_alpha_count     BINARY_INTEGER := 0;
	v_case_sensitive  BOOLEAN;
	v_char_idx_tab    char_idx_tabtype;
	v_charidx01       VARCHAR2(1);
	v_differ_count    BINARY_INTEGER := 0;
	v_idx01           BINARY_INTEGER := 0;
	v_lower_count     BINARY_INTEGER := 0;
	v_numeric_count   BINARY_INTEGER := 0;
	v_punct_count     BINARY_INTEGER := 0;
	v_reverse         VARCHAR2(32767);
	v_specpunct_count BINARY_INTEGER := 0;
	v_stdpunct_count  BINARY_INTEGER := 0;
	v_upper_count     BINARY_INTEGER := 0;

	-- Function to retrieve the v_sec_case_sensitive_logon initialisation parameter
	-- Applies to releases Oracle11g onwards
	FUNCTION f_sec_case_sensitive_logon
		RETURN boolean	
	IS
		v_sec_case_sensitive_logon sys.v_$parameter.value%TYPE;
	BEGIN
		-- Query initialisation parameters for sec_case_sensitive_logon 
		SELECT value
		INTO   v_sec_case_sensitive_logon
		FROM   sys.v_$parameter
		WHERE  name = 'sec_case_sensitive_logon';

		IF v_sec_case_sensitive_logon = 'TRUE'
		THEN
			RETURN TRUE;
		ELSE
			RETURN FALSE;
		END IF;
	EXCEPTION
		WHEN NO_DATA_FOUND
		THEN
			RETURN FALSE;
		WHEN OTHERS
		THEN
			raise_application_error
				( -20000
				, 'f_sec_case_sensitive_logon: '||SQLERRM
				, TRUE);
	END f_sec_case_sensitive_logon;	

	-- Function to retrieve the dbname from v_$database
	FUNCTION f_dbname
		RETURN VARCHAR2	
	IS
		v_dbname sys.v_$database.name%TYPE;
	BEGIN
		-- Query v_$database for database name 
		SELECT name
		INTO   v_dbname
		FROM   sys.v_$database;

		RETURN v_dbname;
	EXCEPTION
		WHEN OTHERS
		THEN
			raise_application_error
				( -20000
				, 'f_dbname: '||SQLERRM
				, TRUE);
	END f_dbname;	

	-- Function to retrieve the hostname from v_$instance
	FUNCTION f_hostname
		RETURN VARCHAR2	
	IS
		v_hostname sys.v_$instance.host_name%TYPE;
	BEGIN
		-- Query v_$instance for host name 
		SELECT host_name
		INTO   v_hostname
		FROM   sys.v_$instance;

		RETURN v_hostname;
	EXCEPTION
		WHEN OTHERS
		THEN
			raise_application_error
				( -20000
				, 'f_hostname: '||SQLERRM
				, TRUE);
	END f_hostname;	

BEGIN
	-- Set case sensitivity based on initialisation parameter
	v_case_sensitive := f_sec_case_sensitive_logon;

	-- Enforce to not contain the username
	IF c_chk_eq_username
	AND instr(nls_lower(fp_password), nls_lower(fp_username)) > 0
	THEN
		RAISE contains_username;
	END IF;

	-- Enforce to not contain the reverse of the username
	IF c_chk_eq_rev_username
	THEN
		v_reverse := NULL;
		<<reverse_loop>>
		FOR v_idx01 IN REVERSE 1..length(fp_username) 
		LOOP
			v_reverse := v_reverse||substr(fp_username, v_idx01, 1);
		END LOOP reverse_loop;

		IF instr(nls_lower(fp_password), nls_lower(v_reverse)) > 0
		THEN
			RAISE contains_rev_username;
		END IF;	
	END IF;	

	-- Enforce to not be similar to the username
	IF c_chk_like_username
	AND soundex( fp_password ) = soundex( fp_username )
	THEN
		RAISE similar_to_username;
	END IF;

	-- Check the length of the password
	IF length(fp_password) < c_min_length
	THEN
		RAISE too_short;
	END IF;

	-- Enforce to not be similar to old password
	IF c_chk_like_old_password
	AND fp_old_password IS NOT NULL
	AND soundex( fp_password ) = soundex( fp_old_password )
	THEN
		RAISE similar_to_old_password;
	END IF;

	-- Enforce forbidden words rules
	IF c_chk_eq_forbidden OR c_chk_like_forbidden
	THEN

		-- Add database name to list of forbidden words
		IF c_add_dbname_to_forbidden
		THEN
			v_password_tab.EXTEND;
			v_password_tab(v_password_tab.LAST) := f_dbname;
			
		END IF;

		-- Add host name to list of forbidden words
		IF c_add_hostname_to_forbidden
		THEN
			v_password_tab.EXTEND;
			v_password_tab(v_password_tab.LAST) := f_hostname;
			
		END IF;

		-- Add current month to list of forbidden words
		IF c_add_month_to_forbidden
		THEN
			v_password_tab.EXTEND;
			v_password_tab(v_password_tab.LAST) := rtrim( to_char( sysdate, 'MONTH' ) );
		END IF;

		<<weakpw_loop>>
		FOR v_idx in v_password_tab.FIRST .. v_password_tab.LAST
		LOOP
			-- Enforce password to not contain a forbidden word
			IF c_chk_eq_forbidden
			AND instr(nls_lower(fp_password), nls_lower(v_password_tab(v_idx))) > 0
			THEN
				RAISE contains_forbidden_word;
			END IF;

			-- Enforce password to not be like a forbidden word
			IF c_chk_like_forbidden
			AND soundex(fp_password) = soundex(v_password_tab(v_idx))
			THEN
				RAISE similar_to_forbidden_word;
			END IF;

		END LOOP weakpw_loop;
	END IF;

	<<character_loop>>
	FOR v_idx01 IN 1..length(fp_password) 
	LOOP
		-- Compare passwords for differences 
		IF fp_old_password IS NOT NULL
		THEN
			IF nls_lower(substr(fp_password,v_idx01,1)) != nls_lower(substr(fp_old_password,v_idx01,1)) 
			OR substr(fp_old_password,v_idx01,1) IS NULL 
			THEN
				v_differ_count := v_differ_count + 1;
			END IF;
		END IF;

		-- Build table of counts per character (case insensitive) 
		IF v_char_idx_tab.EXISTS(nls_upper(substr(fp_password,v_idx01,1))) 
		THEN
			v_char_idx_tab(nls_upper(substr(fp_password,v_idx01,1)))
				:= v_char_idx_tab(nls_upper(substr(fp_password,v_idx01,1))) + 1;
		ELSE
			v_char_idx_tab(nls_upper(substr(fp_password,v_idx01,1))) := 1;
		END IF;

                CASE
			-- Count numeric characters 
			WHEN regexp_like( substr(fp_password,v_idx01,1), '^[[:digit:]]$' )
			THEN
				v_numeric_count := v_numeric_count + 1;

			-- Count alphabetic characters 
			WHEN regexp_like( substr(fp_password,v_idx01,1), '^[[:alpha:]]$' ) 
			THEN
				v_alpha_count := v_alpha_count + 1;

				CASE
					-- Count uppercase characters 
					WHEN regexp_like( substr(fp_password,v_idx01,1), '^[[:upper:]]$' ) 
					THEN
						v_upper_count := v_upper_count + 1;

					-- Count lowercase characters 
					WHEN regexp_like( substr(fp_password,v_idx01,1), '^[[:lower:]]$' ) 
					THEN
						v_lower_count := v_lower_count + 1;
				END CASE;

			-- Count standard oracle punctuation characters 
			WHEN regexp_like( substr(fp_password,v_idx01,1), '^[_,#,$]$' ) 
			THEN
				v_stdpunct_count := v_stdpunct_count + 1;

			-- Count other characters
			ELSE
				v_specpunct_count := v_specpunct_count + 1;
		END CASE;

	END LOOP character_loop;

	-- Enforce minimum number of different characters from previous password 
	IF fp_old_password IS NOT NULL
	THEN
		IF length(fp_old_password) > length(fp_password)
		THEN 
	           v_differ_count := v_differ_count + (length(fp_old_password) - length(fp_password));
		END IF;
		IF v_differ_count < c_min_diff_from_prev
		THEN
			RAISE not_enough_change;
		END IF;
	END IF;

	-- Enforce minimum number of alphabetic characters 
	IF v_alpha_count < c_min_alpha
	THEN
		RAISE too_few_alphas;
	END IF;

	IF v_case_sensitive
	THEN 
		-- Enforce minimum number of uppercase characters 
		IF v_upper_count < c_min_upper
		THEN
			RAISE too_few_uppers;
		END IF;

		-- Enforce minimum number of lowercase characters 
		IF v_lower_count < c_min_lower
		THEN
			RAISE too_few_lowers;
		END IF;
	END IF;

	-- Enforce minimum number of numeric characters
	IF v_numeric_count < c_min_numeric
	THEN
		RAISE too_few_numerics;
	END IF;

	-- Enforce minimum number of punctuation characters 
	v_punct_count := v_stdpunct_count + v_specpunct_count;
	IF v_punct_count < c_min_punct
	THEN
		RAISE too_few_punct;
	END IF;

	-- Enforce minimum number of standard punctuation characters 
	IF v_stdpunct_count < c_min_standard_punct
	THEN
		RAISE too_few_std_punct;
	END IF;

	-- Enforce minimum number of special punctuation characters 
	IF v_specpunct_count < c_min_special_punct
	THEN
		RAISE too_few_spec_punct;
	END IF;

	-- Enforce minimum number of different characters within password 
	IF v_char_idx_tab.COUNT < c_min_char_diversity
	THEN
		RAISE not_diverse;
	END IF;

	-- Enforce maximum occurrences of the same character within password 
	IF c_max_char_duplication > 1
	THEN
		v_charidx01 := v_char_idx_tab.FIRST;
		<<duplication_loop>>
		WHILE v_charidx01 IS NOT NULL
		LOOP
			IF v_char_idx_tab(v_charidx01) > c_max_char_duplication
			THEN
				RAISE too_many_dup_characters;
			END IF;
			v_charidx01 := v_char_idx_tab.NEXT(v_charidx01) ;
		END LOOP duplication_loop;
	END IF;

	-- Password passes verification
	RETURN(TRUE);

EXCEPTION
	WHEN contains_username
	THEN
		raise_application_error
			( -20001
			, 'Password contains the username.'
			, FALSE );
	WHEN contains_rev_username
	THEN
		raise_application_error
			( -20002
			, 'Password contains the username in reverse.'
			, FALSE );
	WHEN similar_to_username
	THEN
		raise_application_error
			( -20003
			, 'Password too similar to username.'
			, FALSE );
	WHEN too_short
	THEN
		raise_application_error
			( -20004
			, 'Password length less than '||c_min_length||'.'
			, FALSE );
	WHEN similar_to_old_password
	THEN
		raise_application_error
			( -20005
			, 'Password too similar to old password.'
			, FALSE );
	WHEN contains_forbidden_word
	THEN
		raise_application_error
			( -20006
			, 'Password contains a forbidden word.'
			, FALSE );
	WHEN similar_to_forbidden_word
	THEN
		raise_application_error
			( -20007
			, 'Password is similar to a forbidden word.'
			, FALSE );
	WHEN not_enough_change
	THEN
		raise_application_error
			( -20008
			, 'Password fails to differ from previous by at least '||c_min_diff_from_prev||' characters.'
			, FALSE );
	WHEN too_few_alphas
	THEN
		raise_application_error
			( -20009
			, 'Password contains less than '||c_min_alpha||' alphabetic characters.'
			, FALSE );
	WHEN too_few_uppers
	THEN
		raise_application_error
			( -20010
			, 'Password contains less than '||c_min_upper||' uppercase characters.'
			, FALSE );
	WHEN too_few_lowers
	THEN
		raise_application_error
			( -20011
			, 'Password contains less than '||c_min_lower||' lowercase characters.'
			, FALSE );
	WHEN too_few_numerics
	THEN
		raise_application_error
			( -20012
			, 'Password contains less than '||c_min_numeric||' numeric characters.'
			, FALSE );
	WHEN too_few_punct
	THEN
		raise_application_error
			( -20013
			, 'Password contains less than '||c_min_punct||' punctuation characters.'
			, FALSE );
	WHEN too_few_std_punct
	THEN
		raise_application_error
			( -20014
			, 'Password contains less than '||c_min_standard_punct||' of the following characters ''_'', ''#'', ''$''.'
			, FALSE );
	WHEN too_few_spec_punct
	THEN
		raise_application_error
			( -20015
			, 'Password contains less than '||c_min_special_punct||' special characters i.e. not alphanumeric or ''_'', ''#'', ''$''.'
			, FALSE );
	WHEN not_diverse
	THEN
		raise_application_error
			( -20016
			, 'Password contains less than '||c_min_char_diversity||' distinct characters.'
			, FALSE );
	WHEN too_many_dup_characters
	THEN
		raise_application_error
			( -20017
			, 'Password contains more than '||c_max_char_duplication||' occurrences of the same character.'
			, FALSE );
	WHEN OTHERS
	THEN
		raise_application_error
			( -20000
			, 'pentest_password_verify: Unexpected error: '||SQLERRM
			, TRUE);
END;
/