Introduction/ Issue: Write the issue that you face or the issue that you want to provide a solution through this blog.
Password generation based on input parameters (number of digits, number of special characters, number of lower, number of upper)
Why we need to do / Cause of the issue: Write the details about the issue – how does it occur and what is the impact of it.
Each and every login users should have unique password.
How do we solve: Write the Query or the steps to solve the issue with your real-time example or scenarios. Note: do not mention any customer name / VPN details.
We can create one function in database and generate the password based on input parameters.
CREATE OR REPLACE function FN_PASSWORD_GENERATION(
no_of_digits in number,
no_of_special_characters in number,
no_of_lower in number,
no_of_upper in number
) return varchar2
AS
lv_password VARCHAR2(4000);
lv_digits CONSTANT VARCHAR2(10) := ‘0123456789’;
lv_lower CONSTANT VARCHAR2(26) := ‘abcdefghijklmnopqrstuvwxyz’;
lv_upper CONSTANT VARCHAR2(26) := ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’;
lv_special CONSTANT VARCHAR2(32) := ‘!”#$%&()*+-/:;<=>?_’;
BEGIN
SELECT LISTAGG(letter, NULL) WITHIN GROUP (ORDER BY DBMS_RANDOM.VALUE)
INTO lv_password
FROM (
SELECT SUBSTR(
lv_digits,
FLOOR(DBMS_RANDOM.VALUE(1, LENGTH(lv_digits) + 1)),
1
) AS letter
FROM DUAL
CONNECT BY LEVEL <= no_of_digits
UNION ALL
SELECT SUBSTR(
lv_lower,
FLOOR(DBMS_RANDOM.VALUE(1, LENGTH(lv_lower) + 1)),
1
) AS letter
FROM DUAL
CONNECT BY LEVEL <= no_of_lower
UNION ALL
SELECT SUBSTR(
lv_upper,
FLOOR(DBMS_RANDOM.VALUE(1, LENGTH(lv_upper) + 1)),
1
) AS letter
FROM DUAL
CONNECT BY LEVEL <= no_of_upper
UNION ALL
SELECT SUBSTR(
lv_special,
FLOOR(DBMS_RANDOM.VALUE(1, LENGTH(lv_special) + 1)),
1
) AS letter
FROM DUAL
CONNECT BY LEVEL <= no_of_special_characters
);
RETURN lv_password;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/
Output:
Here we are passing the count of (3 numbers, 2 special characters, 4 lower case, 5 upper case and getting the unique password.
Conclusion: How did we provide an effective solution and resolve that issue/ unique solution given etc.
We can generate the password randomly and its unique password for all the users.
