Requirements Verification

-- USERNAME, PASSWORD Verification in PL/SQL
-- AUTHOR : Taqui

CREATE OR REPLACE FUNCTION verify_requirements(usr IN VARCHAR2, pass IN VARCHAR2) RETURN VARCHAR2 AS

  len INTEGER;

  isDigit BOOLEAN;
  numDigit INTEGER;

  isPunc BOOLEAN;
  numPunc INTEGER;

  isLowChar BOOLEAN;
  numLowChar INTEGER;

  isUpChar BOOLEAN;
  numUpChar INTEGER;

  digitArray VARCHAR2(10);
  puncArray VARCHAR2(25);
  lowCharArray VARCHAR2(26);
  upCharArray VARCHAR2(26);

  BEGIN

    digitArray := '0123456789';
    puncArray := '@!"#$%&()``*+,-/:;<=>?_';
    lowCharArray := 'abcdefghijklmnopqrstuvwxyz';
    upCharArray := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    -- Check for the minimum length of the username
    IF LENGTH(usr) < 4 THEN
      raise_application_error(-20001, 'Username cannot be less than 4 characters.');
    END IF;

    -- Check if the password is same as the username
    IF NLS_LOWER(pass) = NLS_LOWER(usr) THEN
      raise_application_error(-20002, 'Password cannot be similar to Username.');
    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(pass) IN
       ('welcome','database','account','user','password','oracle','computer','abcdefgh',
        '12345') THEN
      raise_application_error(-20004, 'Password is too simple.');
    END IF;

    -- Check for the minimum length of the password
    IF LENGTH(pass) < 10 THEN
      raise_application_error(-20003, 'Password cannot be less than 10 characters.');
    END IF;

    -- Check if the password contains at least one each of the following:
    -- uppercase characters, lowercase characters, digits and special characters.

    -- 1. Check for the digits
    isDigit := FALSE;
    numDigit := 0;
    len := LENGTH(pass);
    FOR i IN 1..10 LOOP
      FOR j IN 1..len LOOP
        IF SUBSTR(pass, j, 1) = SUBSTR(digitArray, i, 1) THEN
          numDigit := numDigit + 1;
        END IF;
        IF numDigit > 0 THEN
          isDigit := TRUE;
          GOTO findLowChar;
        END IF;
      END LOOP;
    END LOOP;
    IF isDigit = FALSE THEN
      raise_application_error(-20005, 'Password should contain at least one digit.');
    END IF;

    -- 2. Check for the lowercase characters
    <<findLowChar>>
    isLowChar := FALSE;
    numLowChar := 0;
    len := LENGTH(pass);
    FOR i IN 1..LENGTH(lowCharArray) LOOP
      FOR j IN 1..len LOOP
        IF SUBSTR(pass, j, 1) = SUBSTR(lowCharArray, i, 1) THEN
          numLowChar := numLowChar + 1;
        END IF;
        IF numLowChar > 0 THEN
          isLowChar := TRUE;
          GOTO findUpChar;
        END IF;
      END LOOP;
    END LOOP;
    IF isLowChar = FALSE THEN
      raise_application_error(-20006, 'Password should contain at least one lowercase character.');
    END IF;

    -- 3. Check for the UPPERCASE characters
    <<findUpChar>>
    isUpChar := FALSE;
    numUpChar := 0;
    len := LENGTH(pass);
    FOR i IN 1..LENGTH(upCharArray) LOOP
      FOR j IN 1..len LOOP
        IF SUBSTR(pass, j, 1) = SUBSTR(upCharArray, i, 1) THEN
          numUpChar := numUpChar + 1;
        END IF;
        IF numUpChar > 0 THEN
          isUpChar := TRUE;
          GOTO findPunc;
        END IF;
      END LOOP;
    END LOOP;
    IF isUpChar = FALSE THEN
      raise_application_error(-20007, 'Password should contain at least one uppercase character.');
    END IF;

    -- 4. Check for the punctuations
    <<findPunc>>
    isPunc := FALSE;
    numPunc := 0;
    len := LENGTH(pass);
    FOR i IN 1..LENGTH(puncArray) LOOP
      FOR j IN 1..len LOOP
        IF SUBSTR(pass, j, 1) = SUBSTR(puncArray, i, 1) THEN
          numPunc := numPunc + 1;
        END IF;
        IF numPunc > 0 THEN
          isPunc := TRUE;
          GOTO endSearch;
        END IF;
      END LOOP;
    END LOOP;
    IF isPunc = FALSE THEN
      raise_application_error(-20008, 'Password should contain at least one punctuation character.');
    END IF;

    <<endSearch>>

    -- Everything is fine. return valid
    RETURN('VALID');

  END;