02 August, 2012

Generate Random Password in Oracle


In my application I need to generate password for users first time after registration.
The generated password must has some criteria which system administrator will configure it.
1- Character should be UPPERCASE                              =====> Abbreviation [U]
2- Character should be LOWERCASE               =====> Abbreviation [L]
3- Character should be NUMBER                  =====> Abbreviation [N]
4- Character should be any character                      =====> Abbreviation [A]
5- Character should be NON-ALPHANUMERIC character =====> Abbreviation [S]

So I thought to create dynamic function "RANDOM_PASSWORD" to return random password regarding to previous criteria.
The the system administrator will pass criteria per every character in password text to function and it will return random password
For example :-
first character should be UPPERCASE               ======> U
second character should be LOWERCASE          ======> L
third character should be NUMBER                   ======>N
forth character should be any character             ======>A
fifth character should be NON-ALPHANUMERIC  ======>S
sixth character should be Number                    ======> N   

This will generate string "ULNASN" regarding to abbreviation.



The password length will be the same as the number of characters used to configure password
in this example the password length will be 6 characters.

To call the function I will use
 RANDOM_PASSWORD('ULNASN');  

The Source Code of the Function
 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 should be UPPERCASE     =====> Abbreviation [U]  
 2- Character should be LOWERCASE      =====> Abbreviation [L]  
 3- Character should be NUMBER         =====> Abbreviation [N]  
 4- Character should be any character     =====> Abbreviation [A]  
 5- Character should 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'[0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]';  
     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;  

Let's Now run the previous example
 SELECT RANDOM_PASSWORD ('ULNASN') FROM DUAL;  

It will return
Pq51{0

It may be be different in your machine because it is random.

Thanks
Recommended Post Slide Out For Blogger