Imagine you need to design an online system for storing documents on a per-user basis where nobody, including database administrators, would be able to change the content of those documents without being noticed by document owners.
In Percona Server for MySQL 8.0.28-20, we added a new component called Encryption UDFs – an open-source alternative to MySQL Enterprise Encryption that allows users to access a number of low-level OpenSSL encryption primitives directly from MySQL. This includes calculating digests (with a great variety of hash functions), asymmetric key generation (RSA, DSA), asymmetric encryption/decryption for RSA, and calculating/verifying digital signatures (RSA, DSA) as well as primitives for working with Diffie-Hellman (DH) key exchange algorithm.
Prerequisites
In contrast to MySQL Enterprise Encryption, to make the functions available, users of Percona Server for MySQL do not have to register them manually with
1 | CREATE FUNCTION … SONAME … |
for each individual function. All they have to do is invoke
1 | INSTALL COMPONENT 'file://component_encryption_udf' |
Schema definition
Now, let us define a simple data schema in which we will store all the info required for our online document storage service.
It will have two tables: user and document.
1 2 3 4 5 6 7 8 | CREATE TABLE user( id INT UNSIGNED NOT NULL AUTO_INCREMENT, login VARCHAR(128) NOT NULL, key_type VARCHAR(16) NOT NULL, public_key_pem TEXT NOT NULL, PRIMARY KEY(id), UNIQUE(login) ); |
Here,
id – unique user identifier (numerical, for internal usage).
login – unique user identifier (lexical, for public usage).
key_type – type of the asymmetric key generated by the user (currently, either RSA or DSA).
public_key_pem – public component of the asymmetric key generated by the user in PEM format (“–––––BEGIN PUBLIC KEY–––––”).
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE document( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_ref INT UNSIGNED NOT NULL, name VARCHAR(128) NOT NULL, content BLOB NOT NULL, digest_type VARCHAR(16) NOT NULL, signature VARBINARY(2048) NOT NULL, PRIMARY KEY(id), FOREIGN KEY (user_ref) REFERENCES user(id), UNIQUE (user_ref, name) ); |
Here,
id – unique document identifier (numerical, internal).
user_ref – the ID of the user who owns the document.
name – a name under which the document is stored for the user_ref user.
content – binary object that holds document content.
digest_type – the name of the hash function used to calculate the digital signature on content.
signature – digital signature calculated by signing the digest value (calculated with digest_type hash function on content) with a private key that belongs to user_ref.
User registration
So, the first step in registering a new user would be to generate an asymmetric key pair. Although in this example we will be using the RSA algorithm, our system is flexible enough and allows us to use DSA keys as well.
Important notice: for our use case, private keys must always be generated on the client machine and never leave local secure storage. Only public keys are allowed to be transferred via the network.
To generate an RSA private key, the user can run the standard openssl utility.
1 | openssl genrsa -out private_key.pem 4096 |
The content of the generated 4096-bit RSA private key in PEM format will be written to the private_key.pem file.
Next, we need to extract the public component from the generated key.
1 | openssl rsa -in private_key.pem -pubout -out public_key.pem |
The extracted 4096-bit RSA public key in PEM format will be written to the public_key.pem file.
1 | SET @public_key = '<public_key_pem_content>'; |
Here, <public_key_pem_content> is the content of the public_key.pem file (a public key in PEM format with the “–––––BEGIN PUBLIC KEY–––––” header).
Just for the simplicity of this blog post (Once again, never use this approach in a real system in production), RSA / DSA keys can be generated by the Percona Server:
1 2 3 | SET @algorithm = 'RSA'; SET @private_key = create_asymmetric_priv_key(@algorithm, 4096); /* never do this in production */ SET @public_key = create_asymmetric_pub_key(@algorithm, @private_key); /* never do this in production */ |
Now, when we have a public key, the user registration is straightforward.
1 | INSERT INTO user VALUES(DEFAULT, 'alice', 'RSA', @public_key); |
Again, in production, @public_key must be set to the content of the public_key.pem generated locally (with openssl utility, for instance) rather than generated on the server.
Uploading documents
When a user wants to upload a new file to our online document storage, the first step would be to calculate its digital signature.
For instance, if we are going to upload a local file called secure_data.doc and we want to use, say, SHA256 as a hash function to calculate digest before signing with a previously generated 4096-bit RSA private key, execute the following
1 | openssl dgst -sha256 -sign private_key.pem -out secure_data.binsig secure_data.doc |
The signature in binary format will be written to the secure_data.binsig.
In order to simplify copying the content of this file to SQL statements, let us also convert this signature to HEX format. We will be using the xxd utility to perform this operation (please notice that on some Linux distributions this utility is a part of the vim-common package).
1 | xxd -p -u -c0 secure_data.binsig secure_data.hexsig |
The signature in HEX format will be written to the secure_data.hexsig file. After that, the user is supposed to call the upload_document() stored procedure.
1 2 | CALL upload_document('alice', 'secure_data.doc', <file_content>, 'SHA256', UNHEX(<file_signature_hex>), @upload_status); |
Here,
alice – name of the document owner.
secure_data.doc – a name under which the document will be stored.
<file_content> – the content of the local secure_data.doc passed as binary data.
SHA256 – the name of the hash function used to calculate the file digest.
<file_signature_hex> – the file signature in HEX format (the content of the secure_data.hexsig file).
On the server upload_document() stored routine should do the following.
First, it needs to find @user_ref, @key_type, and @public_key_pem in the user table for the provided owner’s login (alice).
1 2 3 4 | SELECT id, key_type, public_key_pem INTO user_id, user_key_type, user_public_key_pem FROM user WHERE login = user_login; |
Second, it needs to calculate message digest @digest using the provided hash function name (SHA256) for the provided file data (file_content).
1 | SET digest = create_digest(digest_type, file_content); |
Then, the server code needs to verify the file signature provided by the user (file_signature) with the public key associated with the file owner (alice).
1 2 3 | SET verification_result = asymmetric_verify( user_key_type, digest, file_signature, user_public_key_pem, digest_type); |
After that, only if verification_result is equal to 1, we confirm the identity of the document owner and insert a new record into the document table.
1 2 | INSERT INTO document VALUES(DEFAULT, @user_ref, 'secure_data.doc', file_content, 'SHA256', file_signature); |
Here is how upload_document() may look like
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | CREATE PROCEDURE upload_document( user_login VARCHAR(128), file_name VARCHAR(128), file_content BLOB, digest_type VARCHAR(16), file_signature VARBINARY(2048), OUT status INT) L_return: BEGIN DECLARE success INT DEFAULT 0; DECLARE error_login_not_found INT DEFAULT 1; DECLARE error_verification_failed INT DEFAULT 2; DECLARE user_id INT UNSIGNED DEFAULT 0; DECLARE user_key_type VARCHAR(16) DEFAULT NULL; DECLARE user_public_key_pem TEXT DEFAULT NULL; DECLARE verification_result INT DEFAULT 0; DECLARE digest VARBINARY(64) DEFAULT NULL; SELECT id, key_type, public_key_pem INTO user_id, user_key_type, user_public_key_pem FROM user WHERE login = user_login; IF user_id = 0 THEN SET status = error_login_not_found; LEAVE l_return; END IF; SET digest = create_digest(digest_type, file_content); SET verification_result = asymmetric_verify( user_key_type, digest, file_signature, user_public_key_pem, digest_type) IF verification_result = 0 THEN SET status = error_verification_failed; LEAVE l_return; END IF; INSERT INTO document VALUES( DEFAULT, user_id, file_name, file_content, digest_type, file_signature); SET status = success; END |
Downloading documents and verifying their integrity
In order to download a file from our online document storage, the first step will be getting its content along with digital signature metadata.
1 2 | CALL download_document('alice', 'secure_data.doc', @downloaded_content, @downloaded_digest_type, @downloaded_signature, @download_status); |
Here,
alice – name of the document owner.
secure_data.doc – the name of the file we want to download.
@downloaded_content – the content of the downloaded file will be put in this variable.
@downloaded_digest_type – the name of the hash function used to calculate file digest for this file will be put in this variable.
@downloaded_signature – the digital signature of the downloaded file will be put in this variable.
On the server, the download_document() stored routine should do the following.
First, it needs to find @user_ref, @key_type, and @public_key_pem in the user table for the provided owner’s login (alice).
1 2 3 4 | SELECT id, key_type, public_key_pem INTO user_id, user_key_type, user_public_key_pem FROM user WHERE login = user_login; |
Second, it needs to get the file content and digital signature metadata.
1 2 3 4 | SELECT id, content, digest_type, signature INTO file_id, file_content, file_digest_type, file_signature FROM document WHERE user_ref = user_id AND name = file_name; |
After that, we calculate the digest of the file_content using the file_digest_type hash function.
1 | SET digest = create_digest(file_digest_type, file_content); |
And finally, we verify the integrity of the document:
1 2 3 | SET verification_result = asymmetric_verify( user_key_type, digest, file_signature, user_public_key_pem, file_digest_type); |
Only if verification_result is equal to 1, do we confirm the integrity of the document and return successful status.
Here is how download_document() may look like
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | CREATE PROCEDURE download_document( user_login VARCHAR(128), file_name VARCHAR(128) OUT file_content BLOB, OUT file_digest_type VARCHAR(16), OUT file_signature VARBINARY(2048), OUT status INT) L_return: BEGIN DECLARE success INT DEFAULT 0; DECLARE error_login_not_found INT DEFAULT 1; DECLARE error_file_not_found INT DEFAULT 2; DECLARE error_verification_failed INT DEFAULT 3; DECLARE user_id INT UNSIGNED DEFAULT 0; DECLARE user_key_type VARCHAR(16) DEFAULT NULL; DECLARE user_public_key_pem TEXT DEFAULT NULL; DECLARE verification_result INT DEFAULT 0; DECLARE file_id INT UNSIGNED DEFAULT 0; DECLARE digest VARBINARY(64) DEFAULT NULL; SELECT id, key_type, public_key_pem INTO user_id, user_key_type, user_public_key_pem FROM user WHERE login = user_login; IF user_id = 0 THEN SET status = error_login_not_found; LEAVE l_return; END IF; SELECT id, content, digest_type, signature INTO file_id, file_content, file_digest_type, file_signature FROM document WHERE user_ref = user_id AND name = file_name; IF file_id = 0 THEN SET status = error_file_not_found; LEAVE l_return; END IF; SET digest = create_digest(file_digest_type, file_content); SET verification_result = asymmetric_verify( user_key_type, digest, file_signature, user_public_key_pem, file_digest_type); IF verification_result = 0 THEN SET status = error_verification_failed; LEAVE l_return; END IF; SET status = success; END |
Although we included a digital signature verification code into the download_document() routine, it does not guarantee that the end-user (caller of the download_document() routine) will get the unmodified document. This code was added only as an additional step to detect integrity violations at earlier stages. The real digital signature verification must be performed on the client-side, not inside Percona Server.
Basically, after calling download_document(), we need to save the content of the @downloaded_content output variable to a local file (say, downloaded.doc). In addition, the content of the @downloaded_signature in HEX format (HEX(@downloaded_signature)) must be saved into a local file as well (say, downloaded.hexsig).
After that, we can convert the signature in HEX format into binary form.
1 | xxd -r -p -c0 downloaded.hexsig downloaded.binsig |
A digital signature in binary form will be written to the downloaded.binsig file. Now, all we have to do is verify the digital signature:
1 | openssl dgst -sha256 -verify public_key.pem -signature downloaded.binsig downloaded.doc |
And only if we see the desired Verified OK status line, we can be sure that the document we just downloaded has not been modified.
Conclusion
To begin with, I would like to highlight that a real production-ready online document storage system is far more complicated than the one we just described. Probably because it does not use the OpenSSL command-line utility to perform cryptographic operations on the client-side. 🙂 Moreover, it takes into consideration a number of other security aspects that are outside the scope of this blog post.
Nevertheless, I still hope that the example with digital signatures shown here helped to convince you that asymmetric cryptography is not rocket science and with the help of Encryption UDFs component for Percona Server for MySQL can be indeed easy and straightforward. Check out the full documentation.
