Random SQL Password Generator on Data Virtuality

Data Virtuality tutorial shares sample SQL codes which can be used to generate passwords automatically for data virtualization platform administrators. This password generator can be customized by SQL developers according to their password security rules and to achieve desired password complexity easily. I packaged the SQL code into a Data Virtuality SQL procedure so data virtualization platform users can CALL this password generator procedure for their data virtualization platform connected sources as well.

Data Virtuality SQL procedure which can be considered as a password generator tool takes care of following rules to prevent creation of a password string which can be easily guessed or identified.

First rule, the password length. The generated password string should be at least 8 characters and can be up to 12 characters long. The length of the password is randomly decided by the procedure during SQL code execution. The lower and upper limits for password character length is set as 8 and 12 respectively and can be easily adjusted.

declare integer @min = 8;
declare integer @max = 12;
declare integer @password_length;
@password_length = @min + convert(FLOOR( rand() * (1+@max-@min) ), integer);

Another criteria to increase the password strength is use of numbers, capital letters and lower case characters with special characters within the password at least once.

Maybe some character sets can be used to build the set of characters for each group. But I preferred to use array objects as the source of each character category to use in password string. Array functions is specific to Data Virtuality development platform which enables SQL programmers to build desired solutions easily.

declare object @array_characters = array('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q', 'r','s','t','u','v','y','w','z');
declare object @array_numbers = array('1','2','3','4','5','6','7','8','9','0');
declare object @array_specialcharacters = array('*','-','.','[',']','+','#');

For capital or upper case characters I used the same array but additionally applied UCASE() SQL function to convert character value to upper case letter. Data Virtuality developers can modify the above array contents based on their requirements.

While password generation, at least once one character is read randomly from eash array object.

-- lower case
@pwdchar = cast( array_get(@array_characters, convert(CEILING( rand() * array_length(@array_characters) ), integer)) as string);
@password = @password || @pwdchar;
-- capital letter
@pwdchar = cast( array_get(@array_characters, convert(CEILING( rand() * array_length(@array_characters) ), integer)) as string);
@password = @password || ucase(@pwdchar);
-- number
@pwdchar = cast( array_get(@array_numbers, convert(CEILING( rand() * array_length(@array_numbers) ), integer)) as string);
@password = @password || @pwdchar;
-- special character
@pwdchar = cast( array_get(@array_specialcharacters, convert(CEILING( rand() * array_length(@array_specialcharacters) ), integer)) as string);
@password = @password || @pwdchar;

After reading one character for each character category to build a random password, in password generation process next step is to fetch the rest of password characters from the overall list. The overall list of characters is built by using again a Data Virtuality SQL array function ARRAY_ADD()

declare object @all;
@all = array_add(array_add(@array_characters, @array_numbers), @array_specialcharacters);

To generate the password up to desired password length, a SQL WHILE LOOP is used and within the body of the WHILE loop, each time a random character is selected and appended to the temporary password string.

while (length(@password) < @password_length)
begin
 @pwdchar = cast( array_get(@all, convert(CEILING( rand() * array_length(@all) ), integer)) as string);
 if (convert(CEILING( rand() * 2 ), integer) = 2)
  @pwdchar = ucase(@pwdchar);
 @password = @password || @pwdchar;
end

The WHILE loop and its SQL code populates the password string up to the password length that is defined at the beginning of the password generator code.

One last step which will increase password strength at the point is to shuffle the characters selected for the password string. Because the previously shared code first selects a lower case character, then an upper case character, followed by numeric character and a special character. The first 4 character selection is identified by this process order.

If we shuffle the characters within the password generated by our SQL code, this will be a more random procedure to generate passwords on Data Virtuality platform.

To summerize, I select a random character from the password string.
Remove it from the password string and append it to the end.
I change the place of randomly selected characters in previously defined number of times.

-- change order of characters
while (0 < @swapcount)
begin
 @swapindex = convert(CEILING( rand() * @password_length ), integer);
 @password = left(@password, (@swapindex-1)) || right(@password, (@password_length-@swapindex)) || SUBSTRING(@password FROM @swapindex FOR 1);
 @swapcount = @swapcount - 1;
end

For manipulating the automatically generated password string, I used SQL string functions LEFT(), RIGHT() and SUBSTRING functions within a WHILE loop statement.

Following is the complete SQL code to create a password generator procedure on Data Virtuality data virtualization platform tool. Data Virtuality platform administrators or SQL developers can use the code shared in this tutorial on their servers.

In following SQL code, "DVAdminScripts" is the Data Virtuality virtual schema that the GeneratePassword() procedure is created within.

CREATE VIRTUAL PROCEDURE DVAdminScripts.GeneratePassword ()
returns (
"password" string
)
as

BEGIN

declare integer @min = 8;
declare integer @max = 12;
declare integer @password_length;
declare string @password;
declare string @pwdchar;

declare object @array_characters = array('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q', 'r','s','t','u','v','y','w','z');
declare object @array_numbers = array('1','2','3','4','5','6','7','8','9','0');
declare object @array_specialcharacters = array('*','-','.','[',']','+','#');
declare object @all;

declare integer @swapcount = 10;
declare integer @swapindex;

@password = '';

@all = array_add(array_add(@array_characters, @array_numbers), @array_specialcharacters);

@password_length = @min + convert(FLOOR( rand() * (1+@max-@min) ), integer);

-- lower case
@pwdchar = cast( array_get(@array_characters, convert(CEILING( rand() * array_length(@array_characters) ), integer)) as string);
@password = @password || @pwdchar;
-- capital letter
@pwdchar = cast( array_get(@array_characters, convert(CEILING( rand() * array_length(@array_characters) ), integer)) as string);
@password = @password || ucase(@pwdchar);
-- number
@pwdchar = cast( array_get(@array_numbers, convert(CEILING( rand() * array_length(@array_numbers) ), integer)) as string);
@password = @password || @pwdchar;
-- special character
@pwdchar = cast( array_get(@array_specialcharacters, convert(CEILING( rand() * array_length(@array_specialcharacters) ), integer)) as string);
@password = @password || @pwdchar;

while (length(@password) < @password_length)
begin
 @pwdchar = cast( array_get(@all, convert(CEILING( rand() * array_length(@all) ), integer)) as string);
 if (convert(CEILING( rand() * 2 ), integer) = 2)
  @pwdchar = ucase(@pwdchar);
 @password = @password || @pwdchar;
end

-- change order of characters
while (0 < @swapcount)
begin
 @swapindex = convert(CEILING( rand() * @password_length ), integer);
 @password = left(@password, (@swapindex-1)) || right(@password, (@password_length-@swapindex)) || SUBSTRING(@password FROM @swapindex FOR 1);
 @swapcount = @swapcount - 1;
end

select @password;

END;

If you are creating your Data Virtualization users automatically, maybe you can import the above password generator procedure into your process and assing separate random passwords for your each new user.

Here is how this password generator SQL procedure can be executed manually.

call "DVAdminScripts.GeneratePassword"();;

For example, when I execute the password generating SQL procedure, I got following randomly created password strings on Data Virtuality server:
m87UHd[6BOO0
#OUYU4v0
o]M*63G1z8
1[lTK**Pl[#k
t[OGWM4Q]
jC4.vYc-wRu9
As seen from the sample passwords generated, the length of the passwords varies between the password minumum and maximum length boundaries. You can also see we have special characters, numbers, lower and upper characters in our randomly created passwords.

I hope Data Virtuality platform professionals; administrators and SQL developers will find this SQL password generator tool and how SQL functions are used within the SQL procedure code used to generate passwords randomly.