Salts and UUIDs for your Oracle database
221103
A concise post for practical implementation steps for UUIDs and Password Salted Hashes when working with an Oracle Database.
Intro
This post is a case of practical implementations of UUIDs and Salts when working with Oracle Databases. The topics that will be discussed are:
- Working with the database and creating the demousers table
- Oracle UUIDs – Using the built-in SYS_GUID() 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 Oracle database instance and the SQLcl CLI tool [Here you can find how you can install it in your system as a stand-alone tool]. If you wish you can take a look at my post below on how to create and start using an OracleDB container.
Start working with the Oracle database, create a new schema/user, and the demousers table
Connect to your Oracle running instance via the sqlcl utility. Below we use a command to connect directly to a PDB (Pluggable Database) named ‘dockor19cPDB’ as sysdba:
~ sqlcl sys/myPassw@192.168.0.17:1621/dockor19cPDB as sysdba
[? Note: Above, the sql executable, has been renamed from sql to sqlcl]
After you have accessed the Oracle running instance , you can use the CLI to work with. Let’s start.
You can either create a new database or just connect to your preferable database. Here we will create and use the database named “ticket-management”, e.g.:
Create a new user (user schema)
You have to be aware that, in Oracle, when you create a new user, automatically you create a schema with the same name. An Oracle schema can be considered as a similar to a “database” concept in other databases (e.g. MariaDB/MySQL, PostgreSQL or MS SQL Server). That said, let’s create a new user with name “ticket_management” and with password “ticketPassw1”.
When you have created the user/schema, alter it and grant the necessary privileges. Then, you can have a look at the users, querying the dba_users. See the output, below:
SQL> CREATE USER ticket_management IDENTIFIED BY "ticketPassw1"; User TICKET_MANAGEMENT created. SQL> SQL> GRANT connect, resource, create any context TO TICKET_MANAGEMENT; Grant succeeded. SQL> ALTER USER TICKET_MANAGEMENT quota unlimited on users; User altered. SQL>
[? Note: Oracle does not recommend using quoted identifiers for database object names. These quoted identifiers are accepted by SQL*Plus, but they may not be valid when using other tools that manage database objects].
See below, he output of the commands, as well as check if the user object it has been created::
After that, logout and re-login, as as ticket-management user, and ensure/check that you have logged-in correctly::
~ sqlcl TICKET_MANAGEMENT/ticketPassw1@192.168.0.17:1621/DOCKOR19CPDB
Now we are ready to proceed creating our demousers table.
Create the demousers table and add a couple of constraints for primary key and username uniqueness. Use the desc command to check it:
Now we are ready to s see how we can make the ID column to be a UUID column.
Oracle database UUIDs
The Oracle SYS_GUID() function
Oracle offers the built-in SYS_GUID() function that can be used to generate globally unique identifiers. It actually returns a random 16-byte RAW value (32 Hex characters), as you can see below:
Note, that the generated value does not follow any UUID standard. However, we can elaborate a bit the result, to make it to look like a UUID standard. For instance, we can use some Oracle RegEx to format it to look like a UUID:
However, there is one caveat that should be taken into account is that it actually generates a sequence which makes it not really random. You will notice it if you repeat calling it some more times:
So, a better solution might be to use the Oracle capabilities for creating a cryptographically secure pseudo-random sequence of bytes, that can be also be elaborated by a custom stored function to be UUID compatible.
The Oracle DBMS_CRYPTO package
For that purpose, we have to use the DBMS_CRYPTO package which offers a cryptographic pseudo-random number generator we can use.
Note that, the DBMS_CRYPTO is a package, by default, is owned by the SYS schema. So before using it you have to grant the execute privilege to the user/schema you are going to use it:
SQL> GRANT EXECUTE ON dbms_crypto TO "ticket-management";
Note that the DBMS_CRYPTO package is also needed for our salt hashing we will see later on.
Now we have the DBMS_CRYPTO available in our schema we can proceed.
First, we will define a custom stored function for creating a randomly generated UUID and after that we will create a trigger to use it.
Below, 2 example functions, are given. Both of them use the sys.dbms_crypto.randombytes() function to create a rundom string of 16 hex-characters (8-bytes). However the 2nd function uses some RegEx “alchemy” to make the return result UUIDv4 compatible. The names of the stored function are ‘RUUID’ and ‘RUUIDv4’ respectively.
‘Example 1
Example 2
As we’ve said, this is a UUID version 4 compatible function. We do that, by adding just 1 line of code to the previous function, that actually substitutes the 13th hex character of the randomly generated 32-byte hex string with the version number indicator which is 4.
Now you can check the functions and what they return:
SQL> SELECT OBJECT_NAME FROM user_objects WHERE object_type IN('FUNCTION', 'PROCEDURE');
SQL> SELECT RUUID() FROM DUAL;
SQL> SELECT "RUUIDv4"() FROM DUAL;
So far so good. Now, it’s time for the next required step. It is just about creating a new trigger which will be fired upon each new record insertion. Nothing special. It just uses the function previously created:
That’s it! Now, let’s test it by adding a new user to our table:
SQL> INSERT INTO DEMOUSERS (username, password) VALUES ('panos', 'panospassw1');
Cool! As you can see, the auto-created “8B1D3C05-8FAA-463A-D4CC-105252CAAE13” UUID has been correctly inserted as the value in id field!
Oracle database Hashing and Salting
Below is a simple SQL script that creates the trigger that automatically stores a salted hash password:
If you wish, you can use the following sql command to list all your triggers so far:
SQL> SELECT OBJECT_NAME FROM user_objects WHERE object_type = 'TRIGGER';
After we have created our “HASH_PASSWORD_INSERT“ trigger, we can test it, by inserting a new user and then by selecting all rows from DEMOUSERS table:
SQL> INSERT INTO DEMOUSERS (username, password) VALUES ('panos2', 'panospassw2');
SQL> SELECT * FROM DEMOUSERS;
The output is given below:
As you can see above, the trigger works OK. However, we need one more stored function to check if a given password corresponds to the one hashed and stored in the password column in the DEMOUSERS table.
Here is an example of such a stored function:
The function accepts 2 VARCHAR2 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 the 17th character till the end). It returns TRUE (1) if there is a match, otherwise, it returns FALSE (0).
Finally, let’s check it:
SQL> SELECT ISUSERPASSWORDVALID('panos2', 'panospassw2') FROM DUAL;
The output is given below:
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! Cool!
Well, that’s it all demonstrating how you can start dealing with UUID and salts and hashes using an Oracle database!
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