DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Passwd aging - verify_function !

  1. #1
    Join Date
    Jul 2003
    Posts
    323

    Smile Passwd aging - verify_function !

    Hi,

    How to code a requirement like this in the Oracle supplied verify function:

    Passwords shall not use 3 or MORE consecutive chars
    i.e aaa or AAA (aAa is OK)
    bbbb
    ddddd
    ZZZ

    Using substring I can compare 1 set i.e a 3 consecutive char set like (ex. AAA) using an array like AAABBB.. as shown in the template verify func.
    but cannot derive the logic for 3 or more consec. chars!
    Any ideas will be appreciated !!

    Thanks in advance


    cruser3

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    To understand recursion, you must first understand recursion. Does that give you a hint?

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    You can also brute force the check:

    Code:
    create or replace procedure char_checker(password varchar2)
    --create or replace function char_checker(password varchar2)
    --return number
    is
      v_len number;
      v_posn number;
      v_retval number := 0;
      v_char varchar2(3);
      v_char2 varchar2(3);
    begin
      -- get the length
      v_len := length(password);  
      -- loop through characters, only need to go through length-2 of them
      for i in 1..(v_len - 2) loop
        -- get the first/next character
        v_char := substr(password,i,1);
        -- is the next character the same?
        v_char2 := substr(password, i+1, 1);
        -- test the characters
        if (v_char = v_char2) then
          -- grab the next one after that
          v_char2 := substr(password, i+2, 1);
          -- is this three in a row?
          if (v_char = v_char2) then
            -- three in a row, return and exit
            --return 1;
            v_retval := 1;
            exit;
          end if;
        end if;
      end loop;
    
      if (v_retval = 1) then
        dbms_output.put_line(password||' has three consecutive identical characters in it.');
      else
        dbms_output.put_line(password||' is good to go.');
      end if;    
    end;
    /
    
    Procedure created.
    
    SQL>   set serveroutput on
    SQL> exec char_checker('aaaPassword');
    aaaPassword has three consecutive identical characters in it.
    
    PL/SQL procedure successfully completed.
    
    SQL> exec char_checker('aAaPassword');
    aAaPassword is good to go.
    
    PL/SQL procedure successfully completed.
    Another way is to grab a character, then test for three in a row with instr. If the return value is greater than zero, you know there are three in a row, so the password is bad.

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Code:
    create or replace procedure instr_checker(password varchar2)
    is
      v_len number;
      v_posn number;
      v_retval number := 0;
      v_char varchar2(3);
    begin
      v_len := length(password);
      for i in 1..(v_len - 2) loop
        v_char := substr(password,i,1);
        -- build three in a row
        v_char := v_char||v_char||v_char;
        v_posn := instr(password, v_char, 1);
        if (v_posn > 0) then
          v_retval := 1;
          exit;
        end if;
      end loop;
    
      if (v_retval = 1) then
        dbms_output.put_line(password||' has three consecutive identical characters in it.');
      else
        dbms_output.put_line(password||' is good to go.');
      end if;
    end;
    /
    
    Procedure created.
    
    SQL> 
    SQL> exec instr_checker('thisisaPassworddd');
    thisisaPassworddd has three consecutive identical characters in it.
    
    PL/SQL procedure successfully completed.
    
    SQL> exec instr_checker('AAbbBCCdC');
    AAbbBCCdC is good to go.
    
    PL/SQL procedure successfully completed.

  5. #5
    Join Date
    Jul 2003
    Posts
    323
    Tx a lot bud for a simple & elegant sol. ! My brain shuts off on Fridays esp. as I'm now a DBA and dont do a lot of PL/SQL !!



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width