Pronounceable password generator in PL/SQL

Photo by KOBU Agency on Unsplash

Pronounceable password generator in PL/SQL

How often do I need to generate a password for one of my database users and colleagues?

Here is how I do that.

function auth_pwgen return varchar2 is
  l_pos integer;
  l_pw  varchar2(100);
  l_c   varchar2(100) := 'bcdfghjklmnprstvwz'; --consonants
  l_v   varchar2(100) := 'aeiou'; --vowels
  l_a   varchar2(100) := l_c || l_v; --both
  l_s   varchar2(100) := '!@#$%^&*()';
begin
  --use three syllables...
  for ii in 1 .. 3
  loop
    l_pw := l_pw || substr(l_c, trunc(dbms_random.value(0, length(l_c))), 1);
    l_pw := l_pw || substr(l_v, trunc(dbms_random.value(0, length(l_v))), 1);
    l_pw := l_pw || substr(l_a, trunc(dbms_random.value(0, length(l_a))), 1);
  end loop;
  --... Make one Uppercase
  l_pos := round(dbms_random.value(1, length(l_pw)));
  l_pw  := substr(l_pw, 1, l_pos - 1) 
        || upper(substr(l_pw, l_pos, 1)) 
        || substr(l_pw, l_pos + 1);
  --... and add a nice number
  l_pw := l_pw || round(dbms_random.value(10, 99));
  --... and add a special character
  l_pw := l_pw || substr(l_s, dbms_random.value(1, length(l_s)), 1);

  return l_pw;

end auth_pwgen;