fueanta

Requirements Verification

Dec 8th, 2017
167
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- USERNAME, PASSWORD Verification in PL/SQL
  2. -- AUTHOR : Taqui
  3.  
  4. CREATE OR REPLACE FUNCTION verify_requirements(usr IN VARCHAR2, pass IN VARCHAR2) RETURN VARCHAR2 AS
  5.  
  6.   len INTEGER;
  7.  
  8.   isDigit BOOLEAN;
  9.   numDigit INTEGER;
  10.  
  11.   isPunc BOOLEAN;
  12.   numPunc INTEGER;
  13.  
  14.   isLowChar BOOLEAN;
  15.   numLowChar INTEGER;
  16.  
  17.   isUpChar BOOLEAN;
  18.   numUpChar INTEGER;
  19.  
  20.   digitArray VARCHAR2(10);
  21.   puncArray VARCHAR2(25);
  22.   lowCharArray VARCHAR2(26);
  23.   upCharArray VARCHAR2(26);
  24.  
  25.   BEGIN
  26.  
  27.     digitArray := '0123456789';
  28.     puncArray := '@!"#$%&()``*+,-/:;<=>?_';
  29.     lowCharArray := 'abcdefghijklmnopqrstuvwxyz';
  30.     upCharArray := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  31.  
  32.     -- Check for the minimum length of the username
  33.     IF LENGTH(usr) < 4 THEN
  34.       raise_application_error(-20001, 'Username cannot be less than 4 characters.');
  35.     END IF;
  36.  
  37.     -- Check if the password is same as the username
  38.     IF NLS_LOWER(pass) = NLS_LOWER(usr) THEN
  39.       raise_application_error(-20002, 'Password cannot be similar to Username.');
  40.     END IF;
  41.  
  42.     -- Check if the password is too simple. A dictionary of words may be maintained
  43.     -- and a check may be made so as not to allow the words that are too simple for
  44.     -- the password.
  45.     IF NLS_LOWER(pass) IN
  46.        ('welcome','database','account','user','password','oracle','computer','abcdefgh',
  47.         '12345') THEN
  48.       raise_application_error(-20004, 'Password is too simple.');
  49.     END IF;
  50.  
  51.     -- Check for the minimum length of the password
  52.     IF LENGTH(pass) < 10 THEN
  53.       raise_application_error(-20003, 'Password cannot be less than 10 characters.');
  54.     END IF;
  55.  
  56.     -- Check if the password contains at least one each of the following:
  57.     -- uppercase characters, lowercase characters, digits and special characters.
  58.  
  59.     -- 1. Check for the digits
  60.     isDigit := FALSE;
  61.     numDigit := 0;
  62.     len := LENGTH(pass);
  63.     FOR i IN 1..10 LOOP
  64.       FOR j IN 1..len LOOP
  65.         IF SUBSTR(pass, j, 1) = SUBSTR(digitArray, i, 1) THEN
  66.           numDigit := numDigit + 1;
  67.         END IF;
  68.         IF numDigit > 0 THEN
  69.           isDigit := TRUE;
  70.           GOTO findLowChar;
  71.         END IF;
  72.       END LOOP;
  73.     END LOOP;
  74.     IF isDigit = FALSE THEN
  75.       raise_application_error(-20005, 'Password should contain at least one digit.');
  76.     END IF;
  77.  
  78.     -- 2. Check for the lowercase characters
  79.     <<findLowChar>>
  80.     isLowChar := FALSE;
  81.     numLowChar := 0;
  82.     len := LENGTH(pass);
  83.     FOR i IN 1..LENGTH(lowCharArray) LOOP
  84.       FOR j IN 1..len LOOP
  85.         IF SUBSTR(pass, j, 1) = SUBSTR(lowCharArray, i, 1) THEN
  86.           numLowChar := numLowChar + 1;
  87.         END IF;
  88.         IF numLowChar > 0 THEN
  89.           isLowChar := TRUE;
  90.           GOTO findUpChar;
  91.         END IF;
  92.       END LOOP;
  93.     END LOOP;
  94.     IF isLowChar = FALSE THEN
  95.       raise_application_error(-20006, 'Password should contain at least one lowercase character.');
  96.     END IF;
  97.  
  98.     -- 3. Check for the UPPERCASE characters
  99.     <<findUpChar>>
  100.     isUpChar := FALSE;
  101.     numUpChar := 0;
  102.     len := LENGTH(pass);
  103.     FOR i IN 1..LENGTH(upCharArray) LOOP
  104.       FOR j IN 1..len LOOP
  105.         IF SUBSTR(pass, j, 1) = SUBSTR(upCharArray, i, 1) THEN
  106.           numUpChar := numUpChar + 1;
  107.         END IF;
  108.         IF numUpChar > 0 THEN
  109.           isUpChar := TRUE;
  110.           GOTO findPunc;
  111.         END IF;
  112.       END LOOP;
  113.     END LOOP;
  114.     IF isUpChar = FALSE THEN
  115.       raise_application_error(-20007, 'Password should contain at least one uppercase character.');
  116.     END IF;
  117.  
  118.     -- 4. Check for the punctuations
  119.     <<findPunc>>
  120.     isPunc := FALSE;
  121.     numPunc := 0;
  122.     len := LENGTH(pass);
  123.     FOR i IN 1..LENGTH(puncArray) LOOP
  124.       FOR j IN 1..len LOOP
  125.         IF SUBSTR(pass, j, 1) = SUBSTR(puncArray, i, 1) THEN
  126.           numPunc := numPunc + 1;
  127.         END IF;
  128.         IF numPunc > 0 THEN
  129.           isPunc := TRUE;
  130.           GOTO endSearch;
  131.         END IF;
  132.       END LOOP;
  133.     END LOOP;
  134.     IF isPunc = FALSE THEN
  135.       raise_application_error(-20008, 'Password should contain at least one punctuation character.');
  136.     END IF;
  137.  
  138.     <<endSearch>>
  139.  
  140.     -- Everything is fine. return valid
  141.     RETURN('VALID');
  142.  
  143.   END;
RAW Paste Data