In today’s data-driven world, securing sensitive information is paramount. One effective way to enhance data security in MariaDB (and MySQL) is by using encryption. This blog post will guide you through creating stored procedures that encrypt sensitive data upon insertion and decrypt it upon retrieval.

Step 1: Creating a Secure User

To begin, create a user with the necessary privileges to define and execute our stored procedures. This user will have restricted access to ensure the security of our operations.

CREATE USER ‘secure_user’@’localhost’ IDENTIFIED BY ‘secure_password’;
GRANT ALL PRIVILEGES ON `your_database`.* TO ‘secure_user’@’localhost’;

Step 2: Setting Up the Table

Next, create a table to store the encrypted data. We will use a VARBINARY column to hold the encrypted information.

CREATE TABLE sensitive_table (
id INT AUTO_INCREMENT PRIMARY KEY,
data_column VARBINARY(255)
);

Step 3: Creating the Insert Procedure

Now, we will create a stored procedure that encrypts data before inserting it into the table. The AES_ENCRYPT function in MariaDB allows us to securely encrypt our data.

DELIMITER //

CREATE DEFINER=’secure_user’@’localhost’ PROCEDURE insert_encrypted_data(IN plaintext_data VARCHAR(255))
SQL SECURITY DEFINER
BEGIN
DECLARE encryption_key VARBINARY(32);
SET encryption_key = SHA2(‘my_secret_key’, 256); — Use a secure key

INSERT INTO sensitive_table (data_column)
VALUES (AES_ENCRYPT(plaintext_data, encryption_key));
END //

DELIMITER ;

In this procedure:

• encryption_key is generated using the SHA2 function for hashing our secret key.
• The AES_ENCRYPT function encrypts the plaintext_data using the encryption_key.

Step 4: Creating the Select Procedure

To retrieve and decrypt the data, we will create another stored procedure. This procedure uses the AES_DECRYPT function to convert the encrypted data back to its original form.

DELIMITER //

CREATE DEFINER=’secure_user’@’localhost’ PROCEDURE select_decrypted_data(OUT decrypted_data VARCHAR(255))
SQL SECURITY DEFINER
BEGIN
DECLARE encryption_key VARBINARY(32);
SET encryption_key = SHA2(‘my_secret_key’, 256); — Use a secure key

SELECT CAST(AES_DECRYPT(data_column, encryption_key) AS CHAR(255))
INTO decrypted_data
FROM sensitive_table
WHERE id = 1; — Modify as needed to select the appropriate row
END //

DELIMITER ;

In this procedure:

• The AES_DECRYPT function decrypts the data using the same encryption_key.
• The decrypted data is cast to VARCHAR and selected into the decrypted_data variable.

Step 5: Granting Execute Permissions

Finally, we grant execute permissions to the user who needs to run these procedures.

GRANT EXECUTE ON PROCEDURE `your_database`.`insert_encrypted_data` TO ‘invoking_user’@’localhost’;
GRANT EXECUTE ON PROCEDURE `your_database`.`select_decrypted_data` TO ‘invoking_user’@’localhost’;

Complete Example

Combining all the steps, here’s the complete example:--

— Create a secure user
CREATE USER ‘secure_user’@’localhost’ IDENTIFIED BY ‘secure_password’;
GRANT ALL PRIVILEGES ON `your_database`.* TO ‘secure_user’@’localhost’;

— Create the table
CREATE TABLE sensitive_table (
id INT AUTO_INCREMENT PRIMARY KEY,
data_column VARBINARY(255)
);

— Insert Procedure
DELIMITER //

CREATE DEFINER=’secure_user’@’localhost’ PROCEDURE insert_encrypted_data(IN plaintext_data VARCHAR(255))
SQL SECURITY DEFINER
BEGIN
DECLARE encryption_key VARBINARY(32);
SET encryption_key = SHA2(‘my_secret_key’, 256); — Use a secure key

INSERT INTO sensitive_table (data_column)
VALUES (AES_ENCRYPT(plaintext_data, encryption_key));
END //

DELIMITER ;

— Select Procedure
DELIMITER //

CREATE DEFINER=’secure_user’@’localhost’ PROCEDURE select_decrypted_data(OUT decrypted_data VARCHAR(255))
SQL SECURITY DEFINER
BEGIN
DECLARE encryption_key VARBINARY(32);
SET encryption_key = SHA2(‘my_secret_key’, 256); — Use a secure key

SELECT CAST(AES_DECRYPT(data_column, encryption_key) AS CHAR(255))
INTO decrypted_data
FROM sensitive_table
WHERE id = 1; — Modify as needed to select the appropriate row
END //

DELIMITER ;

— Grant execute permissions
GRANT EXECUTE ON PROCEDURE `your_database`.`insert_encrypted_data` TO ‘invoking_user’@’localhost’;
GRANT EXECUTE ON PROCEDURE `your_database`.`select_decrypted_data` TO ‘invoking_user’@’localhost’;

Key Considerations

1. Secure Key Management: Ensure that your encryption keys are securely stored and managed. The SHA2('my_secret_key', 256) is a basic example; consider using more sophisticated key management strategies.
2. User Permissions: Limit user permissions to only what is necessary for their role to enhance security.
3. Testing: Thoroughly test these procedures in a development environment before deploying them to production to ensure they meet your security and functionality requirements.

By following these steps, you can effectively secure sensitive data in MariaDB using encryption and decryption techniques. This approach helps protect your data from unauthorized access and ensures that sensitive information is handled securely within your database applications.