authen.public

Parameters

Name	Type	Mode
prop_id	integer	IN
min_length	integer	OUT
max_length	integer	OUT
min_lower	integer	OUT
min_upper	integer	OUT
min_number	integer	OUT
min_symbol	integer	OUT
description	text	OUT
regex	text	OUT

Definition

declare
    smin_length text;
    smax_length text;
    smin_lower text;
    smin_upper text;
    smin_number text;
    smin_symbol text;
    CRLF text = E'\n';
BEGIN
    description := 'Your password must meet the policy requirements below'||CRLF;

/* get password policy */
    smin_length := FN_GET_CONFIG(prop_id, 'PASSWORD_MIN_LENGTH' , '@SYSTEM');
    smax_length := FN_GET_CONFIG(prop_id, 'PASSWORD_MAX_LENGTH' , '@SYSTEM');
    smin_lower  := FN_GET_CONFIG(prop_id, 'PASSWORD_MIN_LOWER'  , '@SYSTEM');
    smin_upper  := FN_GET_CONFIG(prop_id, 'PASSWORD_MIN_UPPER'  , '@SYSTEM');
    smin_number := FN_GET_CONFIG(prop_id, 'PASSWORD_MIN_NUMERIC', '@SYSTEM');
    smin_symbol := FN_GET_CONFIG(prop_id, 'PASSWORD_MIN_SYMBOL' , '@SYSTEM');

min_length := smin_length::int;
    max_length := smax_length::int;
    min_lower  := smin_lower::int;
    min_upper  := smin_upper::int;
    min_number := smin_number::int;
    min_symbol := smin_symbol::int;

/* regular expression for pasword policy */
    regex := '(?=(.*\d){'||smin_number||'})(?=(.*[a-z]){'||smin_lower||'})(?=(.*[A-Z]){'||smin_upper||'})(?=(.*[!@#$%]){'||smin_symbol||'})';

/* password policy description */
    if max_length > 0 then
       description := description ||CRLF|| format(' * Must between %s and %s characters long', smin_length, smax_length);
    else
       description := description ||CRLF||' * Must be at least ' ||smin_length|| 'characters long';
    end if;
   
    if min_number > 0 then
       description := description ||CRLF||' * Must contain at least ' ||smin_number|| ' numeric character (0-9)';
    end if;
    if min_upper > 0 then
       description := description ||CRLF||' * Must contain at least ' ||smin_upper || ' uppercase character (A-Z)';
    end if;
    if min_lower > 0 then
       description := description ||CRLF||' * Must contain at least ' ||smin_lower || ' lowercase character (a-z)';
    end if;
    if min_symbol > 0 then
       description := description ||CRLF||' * Must contain at least ' ||smin_symbol|| ' special character (e.g. ! @ # $ %)';
    end if;
END;

sp_get_password_policy

Parameters

Definition