I want a function that will generate random password. I will give function four parameter as how many number that password will contain, how many number of upper case that password will contain, how, many number of lower case that password will contain and how many special characters that password will contain and the return password will be mixture of those input parameters.
create or replace function(
number_of_digits in number,
no_of_special_characters in number,
no_of_lower in number,
no_of_upper in number
) return varchar2
as
password
CodePudding user response：
You can use the function dbms_random.string('P', length). The P parameter, according to Oracle's documentation prints all printable characters.
Here's an example:
select replace(dbms_random.string('P', 10), ' ', 'x') str from dual;
I used replace in this example, to eliminate the possibility of a space, since they're hard for users to comprehend at times.
CodePudding user response：
You can use:
create function generate_password(
no_of_digits in number,
no_of_special_characters in number,
no_of_lower in number,
no_of_upper in number
) return varchar2
AS
password VARCHAR2(4000);
digits CONSTANT VARCHAR2(10) := '0123456789';
lower CONSTANT VARCHAR2(26) := 'abcdefghijklmnopqrstuvwxyz';
upper CONSTANT VARCHAR2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
special CONSTANT VARCHAR2(32) := '!"£$%^&*()-_= {}[]<>,.\|/?;:''@#';
BEGIN
SELECT LISTAGG(letter, NULL) WITHIN GROUP (ORDER BY DBMS_RANDOM.VALUE)
INTO password
FROM (
SELECT SUBSTR(
digits,
FLOOR(DBMS_RANDOM.VALUE(1, LENGTH(digits) 1)),
1
) AS letter
FROM DUAL
CONNECT BY LEVEL <= no_of_digits
UNION ALL
SELECT SUBSTR(
lower,
FLOOR(DBMS_RANDOM.VALUE(1, LENGTH(lower) 1)),
1
) AS letter
FROM DUAL
CONNECT BY LEVEL <= no_of_lower
UNION ALL
SELECT SUBSTR(
upper,
FLOOR(DBMS_RANDOM.VALUE(1, LENGTH(upper) 1)),
1
) AS letter
FROM DUAL
CONNECT BY LEVEL <= no_of_upper
UNION ALL
SELECT SUBSTR(
special,
FLOOR(DBMS_RANDOM.VALUE(1, LENGTH(special) 1)),
1
) AS letter
FROM DUAL
CONNECT BY LEVEL <= no_of_special_characters
);
RETURN password;
END;
/
Then:
SELECT generate_password(5,3,4,4)
FROM DUAL
CONNECT BY LEVEL <= 5;
May randomly output:
GENERATE_PASSWORD(5,3,4,4) 1>9zq$Qw
- Related
- Links：
- CodePudding