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

5 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Such a nice article stuff. Thanks for sharing this useful article
    Strong Password Generator

    ReplyDelete
  3. hi every one i need to create a function for password generating having 2 char, 2 number and 2 special char. i had a function it couldn't give me the required result
    Thanks,

    ReplyDelete
    Replies
    1. Hi, did you ever figure out a function that would return the results you were looking for? I'm having a similar issue. THanks

      Delete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...