Digest password implementation

[Former Member]

Is there a mechanism of digest password implementation in SQL Anywhere. Basically, generation of User Password on the fly which the database is able to understand and logs the user in.

[former_member188493 · Accepted solution]

I assume you are talking about Digest Access Authentication, and no, there is no ready-to-run solution built in to SQL Anywhere.

However, SQL Anywhere does have the RAND() and one-way HASH()functions.

See also: Example: Working With RAND().

See also: Example: String De-Duplicationwhich uses HASH().

Here is an example of using RAND() to generate passwords:

CREATE FUNCTION generate_random_string (

   IN @return_length INTEGER,

   IN @character_set VARCHAR ( 100 ) DEFAULT '' )

   RETURNS LONG BINARY

   NOT DETERMINISTIC

BEGIN

   DECLARE @characters_to_use   LONG BINARY;

   DECLARE @character_count     DOUBLE;

   DECLARE @random_number       DOUBLE;

   DECLARE @return_position     INTEGER;

   DECLARE @character_position  INTEGER;

   DECLARE @return_value        LONG BINARY;

   CASE @character_set

      WHEN 'caps' THEN

         SET @characters_to_use = STRING (

            'QWERTYUIOPASDFGHJKLZXCVBNM' );

      WHEN 'letters' THEN

         SET @characters_to_use = STRING (

            'QWERTYUIOPASDFGHJKLZXCVBNM',

            'qwertyuiopasdfghjklzxcvbnm' );

      WHEN 'numbers' THEN

         SET @characters_to_use = STRING (

            '1234567890' );

      WHEN 'alphanumeric' THEN

         SET @characters_to_use = STRING (

            'QWERTYUIOPASDFGHJKLZXCVBNM',

            'qwertyuiopasdfghjklzxcvbnm',

            '1234567890' );

      WHEN 'loweralphanumeric' THEN

         SET @characters_to_use = STRING (

            'qwertyuiopasdfghjklzxcvbnm',

            '1234567890' );

      WHEN 'upperalphanumeric' THEN

         SET @characters_to_use = STRING (

            'QWERTYUIOPASDFGHJKLZXCVBNM',

            '1234567890' );

      WHEN 'printableASCII' THEN

         SET @characters_to_use = STRING (

         --  12345678901234567890123456789012 

            'QWERTYUIOPASDFGHJKLZXCVBNM',            -- 26

            'qwertyuiopasdfghjklzxcvbnm',            -- 26

            '1234567890',                            -- 10

            '~!@#$%^&*()_+`-={}|[]\:";''<>?,./' );   -- 32  = 94 (space excluded, quote doubled)

      ELSE

         SET @characters_to_use = STRING ( -- omitted: single quote, back slash

            'QWERTYUIOPASDFGHJKLZXCVBNM',

            'qwertyuiopasdfghjklzxcvbnm',

            '1234567890',

            '`~-=!@#$%^&*()_+{}|[]:"<>?;,./' );

   END CASE;

   SET @character_count = LENGTH ( @characters_to_use );

   SET @random_number = RAND();

   SET @return_position = 1;

   WHILE @return_position <= @return_length LOOP

      SET @character_position = CAST ( ROUND ( @random_number * @character_count, 0 ) AS INTEGER );

      SET @return_value = STRING ( @return_value, SUBSTR ( @characters_to_use, @character_position, 1 ) );

      -- MESSAGE STRING ( @return_position, ', ', @random_number, ', ', @character_position, ', ', @return_value ) TO CLIENT;

      SET @random_number = RAND();

      SET @return_position = @return_position + 1;

   END LOOP;

   RETURN @return_value;

END; -- generate_random_string

---------------------------------------------------------------------

-- Testing...

/*

BEGIN

DECLARE @seed        INTEGER;

DECLARE @dummy       DOUBLE;

-- Note: DATEDIFF returns INTEGER so the range of @seed is limited.

--       However, this logic will work well past the year 2055 since

--       a negative seed is OK.

-- Note: The extra "seeding" crap may no longer be necessary in V12.

SET @seed = DATEDIFF ( SECOND, CURRENT TIMESTAMP, '2055 10 27 00:00:00' );

SET @dummy = RAND ( @seed );

SET @dummy = RAND();

SET @dummy = RAND();

SELECT 'caps'              AS characters_to_use, CAST ( generate_random_string ( 5, characters_to_use ) AS VARCHAR )

UNION ALL

SELECT 'letters'           AS characters_to_use, CAST ( generate_random_string ( 5, characters_to_use ) AS VARCHAR )

UNION ALL

SELECT 'numbers'           AS characters_to_use, CAST ( generate_random_string ( 5, characters_to_use ) AS VARCHAR )

UNION ALL

SELECT 'alphanumeric'      AS characters_to_use, CAST ( generate_random_string ( 16, characters_to_use ) AS VARCHAR )

UNION ALL

SELECT 'loweralphanumeric' AS characters_to_use, CAST ( generate_random_string ( 15, characters_to_use ) AS VARCHAR )

UNION ALL

SELECT 'upperalphanumeric' AS characters_to_use, CAST ( generate_random_string ( 30, characters_to_use ) AS VARCHAR )

UNION ALL

SELECT 'printableASCII'    AS characters_to_use, CAST ( generate_random_string ( 13, characters_to_use ) AS VARCHAR )

UNION ALL

SELECT '[default]'         AS characters_to_use, CAST ( generate_random_string ( 5 ) AS VARCHAR )

ORDER BY 1;

END;

characters_to_use generate_random_string(5,characters_to_use)

[default]         A,.iW

alphanumeric      jVsQbv6TEHQp2pQI

caps              JUIRA

letters           dkYUw

loweralphanumeric 11nizrm24bo2h23

numbers           31697

printableASCII    Q)Q_~J#~V#Er(

upperalphanumeric 76LQXVL2U439YFVWZQJXU5QYQROD63

*/