Implementing an Oracle Random Password Generator

I published a posting a few weeks ago on how to implement a password check routine (“Using Regular Expression Functions in PL/SQL to Check for Complex Password Values”) so when either users are created or users are changing passwords, the system automagically checks for strength of password value. It’s purpose was to establish a process that would facilitate adherence to IT Security Policy. In the published example, “strength of value” meant assuring there was an alpha character, numeric character, and special character in the password given.

Interestingly enough, that posting has had the most hits of all my postings by far. As of the time of this writing, it has accounted for 43% of my posting hits. Further, the search referral strings seem to indicate there are a lot of folks looking to find out how to look for alphas, numerics, special chars in pl/sql, as well as use of regexp functions in pl/sql.

Just recently I had a need to create a routine to generate random password values that needed to follow the same value strength standard. My implementation required further use of regexp functions to meet that standard. In light of the apparant interest in these subjects, I felt compelled to post this implementation.

Refer to the package below in the following discussion.

The main interface to this package is generate, which returns a strong password value. The strong password value standard in this implementation consists requires at least one alphabetic character, at least one numeric character, and at least one special character. Passing no arguments defaults to returning a 15 character password in lower case with at least one underscore character “_”. The reason for the underscore character is that I read somewhere that Oracle allows only three special characters (can’t remember the complete list of those), and of those three, only the underscore is recommended for everyday use. However, there are optional arguments that allow the user to choose password length, a list of allowed special characters, and whether to return mixed case or not. The code uses dbms_crypto (Oracle 10.2 documentation for dbms_crypto) for generating a random raw hex value, and then utl_raw (Oracle 10.2 documentation for utl_raw) for converting the hex to character.

I went a little further and created wrappers for “create user” and “alter user”. The procedures are used to output the create/alter user DDL with the strong password value, with optional execution of the DDL (default is “no execute”), and the functions are used to return the complete create/alter user DDL with the strong password value. In addition to the aforementioned password value arguments, there is the option to pass addendum create/alter user DDL that typically follows after the “identified by” clause.

The need for set_max_iterations is so that I don’t leave an unbounded loop when trying to generate a password (see internal_generate proc). There may be a need to toggle this number if it cannot generate a password within the requested requirements in the default number of attempts. Shorter length passwords may require this number to be higher.

Various use examples of this package:

begin
dbms_output.put_line(random_pswd.generate);
end;
/
zcv0rz9a8vibnz_

PL/SQL procedure successfully completed.

declare
x number;
begin
for x in 1..10
loop
dbms_output.put_line(random_pswd.generate);
end loop;
end;
/
ladr0dwqk5_m2cs
icdgfb5b_owby_6
rgeoauyfis_m6sv
pq1zu_y35v7tjwo
to1x3d1nh_le62z
t3_w_kuniratuqy
ortj4gasup3we8_
j25tggc_e3tnrdn
ug6i_j9mv_gvnz1
wwe_hq2wwp302kc

PL/SQL procedure successfully completed.

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;
/
oo1_n
z_bj7
l1_hj
l_u8q
et5k_

PL/SQL procedure successfully completed.

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_allowed_special_characters=>’!@#$%^&*()_+’
));
end loop;
end;
/
sp#mfy3pj)
twz(elax9g
dtz$#8xxn&
ptt*u8to!o

PL/SQL procedure successfully completed.

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;
/
gUw2M_0~f#ZMPNm1)8AI$4zN*

PL/SQL procedure successfully completed.

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 bpr2vd_f2a2uelo
quota unlimited on patience_tbs

begin
random_pswd.create_user(
in_user=>’PATIENCE’
,in_other_ddl=>’ ‘
,in_no_update=>false
);
end;
/

create user PATIENCE
identified by mipfd_tdi7brhey
/

PL/SQL procedure successfully completed.

grant create session to patience;

Grant succeeded.

connect patience/mipfd_tdi7brhey
Connected.
connect / as sysdba


— 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 gp2$at)e>svktvzm4jga
ORA-00922: missing or invalid option
alter user PATIENCE
identified by gp2$at)e>svktvzm4jga
/

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

connect patience/e8lgz_ejgzsut6x3mmyv
Connected.

Hope some of this is of help. Enjoy!

Learning to be patient,
Walt Silva

CREATE OR REPLACE package inferior.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 inferior.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;
——————————————————
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;
begin
iterations:=1;
vPassword:=”;
bPass:=false;
while not bPass
loop
vPassword:=”;
x:=1;
while x<=cLength
loop
vByte:=utl_raw.cast_to_varchar2(dbms_crypto.RANDOMbytes(1));
–vByte:=lower(vByte);
if regexp_like(lower(vByte),’^[abcdefghijklmnopqrstuvwxyz0123456789’||vAllowedS
pecialCharacters||’]$’)
then
if x=1
then
if regexp_like(lower(vByte),’^[a-z]$’)
then
vPassword:=vPassword||vByte;
x:=x+1;
end if;
else
vPassword:=vPassword||vByte;
x:=x+1;
end if;
end if;
end loop;
–dbms_output.put_line(vPassword);
if regexp_like(lower(vPassword),’^.*[a-z].*$’)
and regexp_like(vPassword,’^.*[0-9].*$’)
and regexp_like(lower(vPassword),’^.*[^abcdefghijklmnopqrstuvwxyz0123456789].*$’
)
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;
/

About these ads

One Response to Implementing an Oracle Random Password Generator

  1. […] 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 […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: