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.
What is the difficulty level of this exercise?
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/plsql-exercises/package/plsql-package-exercise-5.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics