Implementing an Oracle Random Password Generator in Oracle 9i

A recent posting of mine showed how to implement a random password generator via pl/sql package. The example given in that posting works with Oracle 10.1 and above, as it uses dbms_crypto package and regexp_like function (both introduced in 10.1).

I recently had a need for a random password generator in one of my Oracle 9.2 databases. I needed to de-evolve (am I not man?) my package to work without the luxury of dbms_crypto and regexp_like.

Refer to my original posting as a baseline during this discussion.

In my Oracle 10 version, I use dbms_crypto to generate a random hex value and use utl_raw to transform the hex value to a character. I then use regexp_like function to interrogate it for being an alpha character, numeric character, or special character from user-supplied list of acceptable characters.

So to make it work in Oracle 9.2, the two main issues are:

1. How to generate a random character value.
2. How to interrogate the random character to classify it as alphabetic, numeric, or user-supplied acceptable special character.

Solving issue 1 was easy, and was essentially a one line change. There exists a dbms_random package (Oracle 9.2 documentation on dbms_random) that can generate random numbers. I used this and chr() to generate a random character. The little twist to this is that I am only interested in a chr(x) value where x is between 1 and 255, and dbms_random generates a random number that can be “any number” positive and negative. To overcome this, I take the absolute value of the random number, use the mod function to take the remainder of the random number divided by 255, and then add 1. This will always give me a random number between 1 and 255.

Solving issue 2 was a little trickier than issue 1. I needed to interrogate
chr(random_number) for being an alpha, numeric, special. I could use a bunch of if statements, or some negative tests with begin/exception when/end blocks, but that would simply muck up my code. I opted to use associative arrays and array functions. Before generating a password, I would load up characters a-z, 0-9, and the user-supplied special characters into respective alpha,numeric,special char arrays using the character as the index. I use the exists function to check the random characters accordingly. Although this added a new procedure and a few more lines of coded, I felt this was a more elegant approach, short of not having dbms_crypto and regexp_like.

See complete code and tests below. I highlighted the 9.2 specific changes in bold.

Hope some of this is of help. Enjoy!

Learning to be patient,
Walt Silva

ops$oracle> begin
dbms_output.put_line(random_pswd.generate);
end;
/
wmikfe9xj0em_9q

PL/SQL procedure successfully completed.

ops$oracle> declare
x number;
begin
for x in 1..10
loop
dbms_output.put_line(random_pswd.generate);
end loop;
end;
/
meo63mtlb97_m_u
a6oabekimw_fqcz
hy7jk2mucfp_avd
dsxrgfrj02d_jqq
mmvbcv_0jwg0phd
nfaifmf7ykwi9_o
ze_a0vocpb04eur
eo_mfppgkvr6lip
jkfg8xz_2a_zuax
rswofa64_fj2vsi

PL/SQL procedure successfully completed.

ops$oracle> declare
x number;
begin
random_pswd.set_max_iterations(500);
for x in 1..5
loop
dbms_output.put_line(random_pswd.generate(5));
end loop;
end;
/
h3t_b
l_k6j
c2_o3
rq4_o
ie_k3

PL/SQL procedure successfully completed.

ops$oracle> declare
x number;
begin
random_pswd.set_max_iterations(500);
for x in 1..5
loop dbms_output.put_line(random_pswd.generate(in_length=>10,in_allowe
d_special_characters=>’!@#$%^&*()_+’));
end loop;
end;
/
rdgl#vr&6p
uwkl*4ph3e
x*2tga&l%b
qhj+6ijdj(
sbv1k(qktz

PL/SQL procedure successfully completed.

ops$oracle> declare
x number;
begin
random_pswd.set_max_iterations(500);
dbms_output.put_line(random_pswd.generate(
in_length=>25 ,in_allowed_special_characters=>’~!@#$%^&*()_}{|.:?><‘
,in_mixed_case=>true
));
end;
/
l%Feint%R8qxglT2:udzZbrRg

PL/SQL procedure successfully completed.

ops$oracle> begin
dbms_output.put_line(
random_pswd.create_user(
in_user=>’PATIENCE’
,in_other_ddl=>’quota unlimited on patience_tbs’
)
);
end;
/
create user PATIENCE
identified by bsorntmm_1uu6lq
quota unlimited on patience_tbs

ops$oracle> begin
random_pswd.create_user(
in_user=>’PATIENCE’
,in_other_ddl=>’ ‘
,in_no_update=>false
);
end;
/
create user PATIENCE
identified by uqnon_bmbbe5exb
/

PL/SQL procedure successfully completed.

ops$oracle> grant create session to patience;

Grant succeeded.

ops$oracle> connect patience/uqnon_bmbbe5exb
Connected.
ops$oracle> connect / as sysdba
Connected.

ops$oracle>
ops$oracle>
ops$oracle>
—
— Example of error using illegal special characters
—
begin
random_pswd.alter_user(
in_user=>’PATIENCE’
,in_other_ddl=>’ ‘
,in_length=>20 ,in_allowed_special_characters=>’~!@#$%^&*()_}{|.:?><‘
,in_no_update=>false
);
end;
/
Error while executing: alter user PATIENCE
identified by fmar2gbjd}1:s1uskp)u

ORA-00911: invalid character
alter user PATIENCE
identified by fmar2gbjd}1:s1uskp)u
/

PL/SQL procedure successfully completed.

ops$oracle> begin
random_pswd.alter_user(
in_user=>’PATIENCE’
,in_other_ddl=>’ ‘
,in_length=>20
,in_no_update=>false
);
end;
/
alter user PATIENCE
identified by lxn1mxjvsv_decavnilq
/

PL/SQL procedure successfully completed.

ops$oracle> connect patience/lxn1mxjvsv_decavnilq Connected.

CREATE OR REPLACE package random_pswd
is
procedure set_max_iterations(in_max_iterations number); procedure alter_user(
in_user varchar2
,in_other_ddl varchar2
,in_no_update boolean default true
,in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false ); function alter_user(
in_user varchar2
,in_other_ddl varchar2
,in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false
) return varchar2;
procedure create_user(
in_user varchar2
,in_other_ddl varchar2
,in_no_update boolean default true
,in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false ); function create_user(
in_user varchar2
,in_other_ddl varchar2
,in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false
) return varchar2;
function generate
(
in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false
) return varchar2;
end;
/

CREATE OR REPLACE package body random_pswd
is
gMaxIterations number:=300;
crlf constant varchar2(2):=chr(13)||chr(10);
procedure ddl_user(
in_ddl1 varchar2
,in_ddl2 varchar2
,in_no_update boolean default true
,in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false ); function ddl_user(
in_ddl1 varchar2
,in_ddl2 varchar2
,in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false
) return varchar2;
function internal_generate
(
in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’
) return varchar2;
type char_table_type is table of boolean index by varchar2(1);
gChars char_table_type;
gNums char_table_type;
gSpecialChars char_table_type;
——————————————————
procedure refresh_char_tables(in_special_characters varchar2) is x number; begin
gChars.delete;
gNums.delete;
gSpecialChars.delete;
for x in 97..122
loop
gChars(chr(x)):=true;
end loop;
for x in 48..57
loop
gNums(chr(x)):=true;
end loop;
for x in 1..length(in_special_characters)
loop
gSpecialChars(substr(in_special_characters,x,1)):=true;
end loop;
end;
——————————————————
procedure set_max_iterations(in_max_iterations number)
is
begin
gMaxIterations:=in_max_iterations;
end;
——————————————————
function generate
(
in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false
) return varchar2
is
vPassword varchar2(32767);
begin
vPassword:=internal_generate(in_length,in_allowed_special_characters);
if not in_mixed_case
then
vPassword:=lower(vPassword);
end if;
return vPassword;
end;
——————————————————
function internal_generate
(
in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’
) return varchar2
is
cLength constant number:=in_length;
iterations number;
x number;
vRandomBytes varchar2(32767);
vPassword varchar2(32767);
vByte varchar2(1);
vAllowedSpecialCharacters varchar2(32767):=in_allowed_special_characters;
bPass boolean;
bCharsHit boolean;
bNumsHit boolean;
bSpecialCharsHit boolean;
begin
refresh_char_tables(vAllowedSpecialCharacters);
iterations:=1;
vPassword:=”;
bPass:=false;
while not bPass
loop
vPassword:=”;
x:=1;
bCharsHit:=false;
bNumsHit:=false;
bSpecialCharsHit:=false;
while x<=cLength
loop
vByte:=chr(mod(abs(dbms_random.random),255)+1);
if gChars.exists(lower(vByte)) or gNums.exists(vByte) or
gSpecialChars.exists(vByte)
then
if x=1
then
if gChars.exists(lower(vByte))
then
vPassword:=vPassword||vByte;
x:=x+1;
if gChars.exists(lower(vByte))
then
bCharsHit:=true;
end if;
if gNums.exists(vByte)
then
bNumsHit:=true;
end if;
if gSpecialChars.exists(vByte)
then
bSpecialCharsHit:=true;
end if;
end if;
else
vPassword:=vPassword||vByte;
x:=x+1;
if gChars.exists(lower(vByte))
then
bCharsHit:=true;
end if;
if gNums.exists(vByte)
then
bNumsHit:=true;
end if;
if gSpecialChars.exists(vByte)
then
bSpecialCharsHit:=true;
end if;
end if;
end if;
end loop;
if bCharsHit
and bNumsHit
and bSpecialCharsHit
then
bPass:=true;
end if;
iterations:=iterations+1;
if iterations>gMaxIterations
then
bPass:=true;
vPassword:=”;
end if;
end loop;
return vPassword;
end;
——————————————————
procedure alter_user(
in_user varchar2
,in_other_ddl varchar2
,in_no_update boolean default true
,in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false
)
is
vDDL varchar2(32767);
begin
ddl_user(
‘alter user ‘||in_user
,in_other_ddl
,in_no_update
,in_length
,in_allowed_special_characters
,in_mixed_case
);
end;
——————————————————
function alter_user(
in_user varchar2
,in_other_ddl varchar2
,in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false
) return varchar2
is
begin
return ddl_user(
‘alter user ‘||in_user
,in_other_ddl
,in_length
,in_allowed_special_characters
,in_mixed_case
);
end;
——————————————————
procedure create_user(
in_user varchar2
,in_other_ddl varchar2
,in_no_update boolean default true
,in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false
)
is
vDDL varchar2(32767);
begin
ddl_user(
‘create user ‘||in_user
,in_other_ddl
,in_no_update
,in_length
,in_allowed_special_characters
,in_mixed_case
);
end;
——————————————————
function create_user(
in_user varchar2
,in_other_ddl varchar2
,in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false
) return varchar2
is
begin
return ddl_user(
‘create user ‘||in_user
,in_other_ddl
,in_length
,in_allowed_special_characters
,in_mixed_case
);
end;
——————————————————
function ddl_user(
in_ddl1 varchar2
,in_ddl2 varchar2
,in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false
) return varchar2
is
vDDL varchar2(32767);
begin
vDDL:=in_ddl1;
vDDL:=vDDL||crlf||’identified by ‘||generate(
in_length

,in_allowed_special_characters
,in_mixed_case
);
vDDL:=vDDL||crlf||in_ddl2;
return vDDL;
end;
——————————————————
procedure ddl_user(
in_ddl1 varchar2
,in_ddl2 varchar2
,in_no_update boolean default true
,in_length number default 15
,in_allowed_special_characters varchar2 default ‘_’ ,in_mixed_case boolean default false
)
is
vDDL varchar2(32767);
begin
vDDL:=ddl_user(
in_ddl1
,in_ddl2
,in_length
,in_allowed_special_characters
,in_mixed_case
);
if not in_no_update
then
begin
execute immediate vDDL;
exception when others
then
dbms_output.put_line(‘Error while executing: ‘||vDDL);
dbms_output.put_line(sqlerrm);
end;
end if;
dbms_output.put_line(vDDL||crlf||’/’);
end;
——————————————————
end;
/