Generate random password in Oracle

Generate a random password in Oracle. In my application, I need to generate a random password for this account when the user registers for the first time. The generated password must meet certain requirements.

Generate a random password in Oracle. In my application, I need to generate a random password for this account when the user registers for the first time. The generated password must meet certain requirements.

In my application, I need to generate a random password for this account when the user registers for the first time. The generated password must meet certain requirements. These requirements must be configured by the system administrator.

We provide the following password-related rules that can be used in combination:

1-The upper case letter is required.
2-LOWERCASE letter LOWERCASE ====> abbreviation [L]
3-digit NUMBER ====> abbreviated [N]
4-any character can be any character ====> [A]

5-The character NON-ALPHANUMERIC character must have non-letters and numbers ====> abbreviation [S]

Therefore, I want to return a random password as required by creating a dynamic function "RANDOM_PASSWORD.

The system administrator only needs to pass the required password rules and returns the corresponding random password.

For example, the requirements are as follows:

The first character must be in uppercase ====> U
The second character must be in lower case ======> L
The third character must be a number ======> N
The fourth character is random ====>
The fifth character must be a non-letter or number =====> S
The sixth character must be a number ======> N

Then you can use the "ULNASN" parameter to obtain the random password.

The password generated is consistent with the length of the passed parameter. In this example, the password length is 6.

You can use the following method to call this function:

RANDOM_PASSWORD ('ulnasn ');

The source code of this function is as follows:

Create or replace function RANDOM_PASSWORD (IN_TEMPLATE IN VARCHAR2)
RETURN VARCHAR2 IS
LC $ CRITERIA VARCHAR2 (1 );
LC $ PASSWORD VARCHAR2 (500 );
LC $ PATTERN VARCHAR2 (500 );
LN $ indx number;
BEGIN
/* 1-Character shocould be UPPERCASE ====> Abbreviation [U]
2-Character shoshould be LOWERCASE ===> Abbreviation [L]
3-Character shocould be NUMBER ====> Abbreviation [N]
4-Character shoshould be any character ====> Abbreviation [A]
5-Character shocould be NON-ALPHANUMERIC character ====> Abbreviation [S] */
LC $ CRITERIA: = '';
LC $ PASSWORD: = '';
For I IN 1 .. LENGTH (IN_TEMPLATE) LOOP
LC $ CRITERIA: = SUBSTR (IN_TEMPLATE, I, 1 );
If upper (LC $ CRITERIA) = 'U' THEN
LC $ PATTERN: = Q' [ABCDEFGHIJKLMNOPQRSTUVWXYZ] ';
Elsif upper (LC $ CRITERIA) = 'l' THEN
LC $ PATTERN: = Q' [abcdefghijklmnopqrstuvwxyz] ';
Elsif upper (LC $ CRITERIA) = 'n' THEN
LC $ PATTERN: = Q' [0123456789] ';
Elsif upper (LC $ CRITERIA) = 'A' THEN
LC $ PATTERN: = Q' [0123456789 ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz] ';
Elsif upper (LC $ CRITERIA) ='s 'then
LC $ PATTERN: = Q '[~! @ # $ % ^ & * () _ +-} {| ":;?., <> []/\] ';
ELSE
LC $ PATTERN: = Q' [ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789] ';
End if;
LN $ INDX: = TRUNC (LENGTH (LC $ PATTERN) * DBMS_RANDOM.VALUE) + 1;
LC $ PASSWORD: = LC $ PASSWORD | SUBSTR (LC $ PATTERN, LN $ INDX, 1 );
End loop;
Return lc $ PASSWORD;
END RANDOM_PASSWORD;

Usage:

SELECT RANDOM_PASSWORD ('ulnasn') from dual;

Here I will return the random password: Pq51 {0

Of course, the running results may be different because they are random.

Original English, OSCHINA Original translation