Salts and UUIDs for your MariaDB/MySQL databases - Panos Zafiropoulos

Reading time: 4 Minutes

221102

A concise post presenting practical implementation of UUIDs and Salts with MariaDB/MySQL Databases.

Intro

This post is a case of practical implementations of UUIDs and Salts when working with MariaDB/MySQL Databases. The topics that will be discussed are:

Working with the database and creating the demousers table
MaraDB UUIDs – Using the uuid() function
Using Trigger(s)
Using a hash verification stored function

Prerequisites

It is supposed that you understand the basic database concepts, UUIDs, and password salting and hashing. The previous post of mine gives some grasp on those subjects.

Moreover, in order to follow the examples of this post, you must have access to a running MariaDB instance and the mysqlsh or mysql/mariadb CLI tools. [Here and here you can find how you can install them in your system as stand-alone tools]. If you wish you can take a look at my post below on how to create and start using a MariaDB Docker container.

https://medium.com/@zzpzaf.se/mariadb-in-docker-65130d77959b?sk=b52cad21459a10dc0c873fcf15b612c8

Note: Pay attention when you use string literals with SQL commands, because they have to be enclosed in backticks `….` and/or simple quotes ‘….’.

Start working with MariaDB and create a new user and the demousers table

If you have followed my previous post of mine (“MariaDB in Docker”) a user with username “user1” should have been already created. Otherwise, access your MariaDB preferable instance as a user with appropriate privileges, e.g., login as “root” user:

~ mysql -u root -prpassw1 -h 192.168.0.17 -P 3316

Then create a new database named “ticket-management”

CREATE DATABASE `ticket-management` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

After that create the user “user1” with password “upassw1”, and grant him all privileges of that database:

GRANT ALL PRIVILEGES ON `ticket-management.*` TO 'user1'@'%' IDENTIFIED BY 'upassw1';
FLUSH PRIVILEGES;

After that, logout and re-login accessing the CLI at your running MySQL/MariaDB instance, using the credentials for user1 (username and password):

mysql -u user1 -pupassw1 -h 192.168.0.17 -P 3316 'ticket-management'

Next, create a new table in the ticket-management (or your preferable) database/schema:

After that create a UNIQUE constraint for username column:

ALTER TABLE `demousers` ADD UNIQUE (`username`);

That’s it. Now it’s time to see how we can have UUID values for the id column.

MariaDB UUIDs

As we have said in the intro post, the id field is the UUID. How we can do that? Don’t worry! With MariaDB It’s quite simple! MariaDB offers us with a built-in secondary function, the UUID() function. Try it yourself:

So, we can use it with a ‘before insert’ trigger to do the job! (Generally, triggers automate database tasks upon event occurrence). So, here it is! Create the following trigger:

Then you can use the show triggers command to check it:

Now we can test it by adding a new user:

BEGIN;
INSERT INTO `demousers` (`username`, `password`) VALUES ('panos', 'panospassw1');
COMMIT;

Then, see what we get:

That’s it! As you can see, the auto-created “e033beef-448b-11ed-a3c3-0242ac110003” UUID has been automatically inserted as the value in id field!

The 2nd step is to see how we can deal with salting and hashing.

MariaDB Hashing and Salting

Before to proceed creating the necessary trigger(s), let’s take a quick look to the MariaDB/MySQL built-in functions that we will use.

First, we will use the RAND() function which is one of the MATH functions offered by the database and which returns a random floating-point value. We will use it for a random generated salt.
Next, we will use the SHA2() function.
Finally the other SQL functions we will use are the CONCAT() and SUBSTR() functions.

Now, you are ready to define a 2^nd trigger that will automatically generate and store both the salt and the SHA2 hash value of a user-provided plain-text password to the password field. The trigger is fired upon an insertion of a user into the “demousers” table. Below an example code is given for such a trigger:

Check it using the show triggers command again:

After that, we can test the trigger “Hash_Password_insert” we’ve just created, by inserting a new user:

BEGIN;
INSERT INTO `demousers` (`username`, `password`) VALUES ('panos2', 'panospassw2');
COMMIT;

As you can see above, the trigger seems works fine. But how we can check if a password provided (e.g., with user credentials for login purposes), is the correct one? How we can verify this password?

Password Verification with a stored function

We can do that within MariaDB database by using a stored function. Below is such an example:

After that, you can check the function created, by using the command:

SHOW FUNCTION STATUS;

The function accepts 2 VARCHAR parameters for username and password. First, it obtains the existing user password using the username provided. Then, extracts the salt, which is actually the string of the first 16 characters of the password. Next, re-calculates a new hash value using the same algorithm. And finally compares the newly calculated hash value with the existing hash value which is the last part of the existing password string (from 17^th character till the end). It returns TRUE (1) if there is a match, otherwise it returns FALSE (0). Let’s check it:

SELECT IsUserPasswordValid('panos2', 'panospassw2');

As you can see, it returns 1 = TRUE, so we have a match! Note, that in your backend/middleware, you can just create the same query string to call the stored function!

That’s it all demonstrating how you can start dealing with UUID and salts and hashes in MySQL/MariaDB!

Keep reading other posts of mine for implementation examples of other databases.

That’s it for now! I hope you enjoyed it!
Thanks for reading? and stay tuned!

About Post Author

Panos

administrator

See author's posts