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

To understand recursion, you must first understand recursion. Does that give you a hint?

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.

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.

---

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 !!


:)