Sunday, 13 October 2013

Oracle: Random password generator SQL function

You can call the below function as:
select RANDOM_PASSWORD('ULLLLLNN') from dual;

This will generate:
-         -  8 Alpha-numeric
-         -  First digit capital letter
-          - Ended with 2 numbers

-          - No special characters

 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 := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
     ELSIF UPPER(LC$CRITERIA ) = 'L' THEN  
        LC$PATTERN := 'abcdefghijklmnopqrstuvwxyz';
     ELSIF UPPER(LC$CRITERIA ) = 'N' THEN  
        LC$PATTERN := '0123456789';
     ELSIF UPPER(LC$CRITERIA ) = 'A' THEN  
        LC$PATTERN := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
     ELSIF UPPER(LC$CRITERIA ) = 'S' THEN  
        LC$PATTERN := '~!@#$%^&*()_+-}{|":;?.,<>[]/\';
     ELSE
        LC$PATTERN := '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;  

No comments:

Post a Comment

Number of Visitors