PL/SQL package for password generation and strength checking

PL/SQL Package: Exercise-5 with Solution

Write a PL/SQL code to create a package that includes a procedure to generate a random password and a function to check the password strength based on certain criteria.

Sample Solution:

PL/SQL Code:

CREATE OR REPLACE PACKAGE PasswordPackage AS
  PROCEDURE GenerateRandomPassword(length IN NUMBER, password OUT VARCHAR2);
  FUNCTION CheckPasswordStrength(password IN VARCHAR2) RETURN VARCHAR2;
END PasswordPackage;
/
CREATE OR REPLACE PACKAGE BODY PasswordPackage AS
  PROCEDURE GenerateRandomPassword(length IN NUMBER, password OUT VARCHAR2) IS
charset VARCHAR2(62) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
i NUMBER;
  BEGIN
    IF length <= 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Password length must be greater than zero.');
    END IF;

    FOR i IN 1..length LOOP
password := password || SUBSTR(charset, DBMS_RANDOM.VALUE(1, 62), 1);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20002, 'An error occurred while generating the random password.');
  END GenerateRandomPassword;
  FUNCTION CheckPasswordStrength(password IN VARCHAR2) RETURN VARCHAR2 IS
uppercase_regexVARCHAR2(100) := '[A-Z]';
lowercase_regexVARCHAR2(100) := '[a-z]';
digit_regexVARCHAR2(100) := '[0-9]';
strength VARCHAR2(20);
  BEGIN
    IF REGEXP_LIKE(password, uppercase_regex) AND
       REGEXP_LIKE(password, lowercase_regex) AND
       REGEXP_LIKE(password, digit_regex) AND
LENGTH(password) >= 8 THEN
strength := 'Strong';
    ELSE
strength := 'Weak';
    END IF;
    RETURN strength;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20003, 'An error occurred while checking the password strength.');
  END CheckPasswordStrength;
END PasswordPackage;
/

Sample Output:

Package created.
Package Body created.

Flowchart:

To execute the package:

DECLARE
generated_passwordVARCHAR2(20);
password_strengthVARCHAR2(20);
BEGIN
PasswordPackage.GenerateRandomPassword(10, generated_password);
  DBMS_OUTPUT.PUT_LINE('Generated Password: ' || generated_password);
password_strength := PasswordPackage.CheckPasswordStrength(generated_password);
  DBMS_OUTPUT.PUT_LINE('Password Strength: ' || password_strength);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Sample Output:

Statement processed.
Generated Password: VCDOwPxyr4
Password Strength: Strong

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that offers functionality for password generation and strength checking.

The PL/SQL package that consists of two main components the GenerateRandomPassword procedure and the CheckPasswordStrength function.

An uppercase, lowercase, and digit character set is used to generate a random password using the GenerateRandomPassword procedure and throws an exception if the length provided is less than or equal to zero.

CheckPasswordStrength checks a password's strength by making sure that it contains at least one uppercase, one lowercase, and one digit, and that it has at least eight characters.

Passwords that meet the said criteria will be rated as "Strong", while passwords that don't will be rated as "Weak".

A proper error message is provided for both the procedure and the function when exceptions occur.

Previous: Math operations package in PL/SQL.
Next: PL/SQL Package for date calculation and format conversion.