MariaDB Guide

System requirements

MariaDB

Tested on 11.8 LTS (official MariaDB repository RPMs). The service name in the commands is mariadb.

Database settings

Server and connection character set utf8mb4

Database server OS

Linux x86_64, glibc 2.28 or later (for example RHEL or Rocky Linux 8 or later)

Build tools

gcc, make, MariaDB-devel, curl, tar, sha256sum (coreutils 8.25 or later)

Privileges

An OS account with sudo on the database server, and the database root account (sudo mariadb)

MariaDB 10.x from RHEL AppStream, Debian and Ubuntu packages, Galera and replication, and SELinux enforcing configurations were not tested. For MySQL, see the MySQL Guide. Both use the same integration source bundle.

Where to run each step

mariadbd loads the UDF library mysql_strcrypt.so, and the UDFs call libstrcrypt.so in the same plugin directory. The libraries, key file, and settings all live on the database server. Nothing needs to be installed on the application server. UDFs are registered server-wide, so they are not installed per database.

1. Pre-checks

mariadb --version
ldd --version | head -1
sudo mariadb -e "SELECT @@version, @@plugin_dir, @@character_set_server"
systemctl cat mariadb | grep -E '^(User|ProtectHome|ProtectSystem)='
command -v gcc make curl sha256sum tar mariadb_config

This prints the MariaDB version, the glibc version, the plugin directory and server character set (utf8mb4), the service account and protection settings, and the installed tools in order. With ProtectHome=true on the service, the server process cannot see home directories, so the key file goes under /etc.

Common errors

2. Build and install the libraries

Only if the build tools are missing

sudo dnf install -y gcc make MariaDB-devel

Download, verify, build, and install

mkdir -p ~/strcrypt-mariadb && cd ~/strcrypt-mariadb
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/libstrcrypt.so
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/libstrcrypt.so.hmac
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/strcrypt-mysql-0.3.1.tar.gz
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/SHA256SUMS
sha256sum -c --ignore-missing SHA256SUMS
tar xzf strcrypt-mysql-0.3.1.tar.gz && cd strcrypt-mysql-0.3.1
PLUGIN_DIR=$(sudo mariadb -N -e 'SELECT @@plugin_dir')
sudo install -m 755 ../libstrcrypt.so "$PLUGIN_DIR"
sudo install -m 644 ../libstrcrypt.so.hmac "$PLUGIN_DIR"
make STRCRYPT_LIB_DIR="$PLUGIN_DIR" STRCRYPT_INCLUDE_DIR=.
sudo install -m 755 mysql_strcrypt.so "$PLUGIN_DIR"
ldd "$PLUGIN_DIR/mysql_strcrypt.so" | grep strcrypt

The UDFs have to be built against the headers on the database server, so they ship as source, in the same strcrypt-mysql-0.3.1.tar.gz used for MySQL. The build runs under ~/strcrypt-mariadb, and all three libraries go to the plugin directory. Installing libstrcrypt.so into the plugin directory first and building with that path keeps the UDF library from referring to a home directory path. The step succeeded if the last line prints libstrcrypt.so => /usr/lib64/mysql/plugin/libstrcrypt.so. No restart is needed at this step.

Common errors

3. Generate the key file

sudo install -d -o mysql -m 700 /etc/strcrypt
head -c 24 /dev/urandom | base64 | head -c 32 | sudo -u mysql tee /etc/strcrypt/strcrypt.key > /dev/null
sudo chmod 600 /etc/strcrypt/strcrypt.key
sudo -u mysql wc -c /etc/strcrypt/strcrypt.key

This creates a 32-byte key in a file only the mysql account can read. The last line should print 32 /etc/strcrypt/strcrypt.key. The second line overwrites an existing key file, so do not run it again once a key exists. The key is never passed through SQL arguments or tables. If you lose the key, the ciphertext cannot be recovered, so keep a separate backup.

Common errors

Key file problems appear after ERROR 1123 (HY000): Can't initialize function 'strcrypt_encrypt'; 환경 변수 오류: when a function is called in step 5 or later. The server cuts the reason at 80 bytes, so the end of the message looks truncated. After fixing the key file, restart mariadb for the change to apply.

4. Pass the key path to the server

sudo mkdir -p /etc/systemd/system/mariadb.service.d
printf '[Service]\nEnvironment=STRCRYPT_KEY_FILE=/etc/strcrypt/strcrypt.key\n' | sudo tee /etc/systemd/system/mariadb.service.d/strcrypt.conf
sudo systemctl daemon-reload
sudo systemctl restart mariadb
systemctl show mariadb -p Environment

A systemd drop-in file passes the environment variable STRCRYPT_KEY_FILE to the mariadb service. It is applied when the last line prints Environment=STRCRYPT_KEY_FILE=/etc/strcrypt/strcrypt.key. Database connections drop briefly during the restart. A value exported in your shell does not reach the server. To use algorithms outside the Korean standards, add the line Environment=STRCRYPT_MODE=non-approved.

Common errors

5. Register the UDFs

cd ~/strcrypt-mariadb/strcrypt-mysql-0.3.1
sudo mariadb < install.sql

install.sql registers six functions and runs an encryption and decryption test at the end. The step succeeded if the output shows [OK] and === 설치 완료 === (installation complete). Registration survives server restarts, and the script can be run again. The script drops the existing functions first, so running it while the library files are missing removes all of them.

Common errors

6. Restrict execute privileges

MariaDB has no execute privilege for UDFs, so you cannot limit which users call the decryption function. Any account that can connect, even one with only USAGE, can decrypt a ciphertext string. Instead, remove anonymous accounts and restrict which accounts can access the ciphertext column and the key file.

Remove anonymous accounts, revoke FILE, and set secure_file_priv

sudo mariadb -e "SELECT user, host FROM mysql.user WHERE user = '' OR (File_priv = 'Y' AND user NOT IN ('root', 'mysql'))"
sudo mariadb -N -e "SELECT CONCAT('DROP USER ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user WHERE user = ''" | sudo mariadb
sudo mariadb -N -e "SELECT CONCAT('REVOKE FILE ON *.* FROM ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user WHERE File_priv = 'Y' AND user NOT IN ('root', 'mysql')" | sudo mariadb
sudo install -d -o mysql -g mysql -m 750 /var/lib/mysql-files
printf '[mariadb]\nsecure_file_priv=/var/lib/mysql-files\n' | sudo tee /etc/my.cnf.d/strcrypt-hardening.cnf
sudo systemctl restart mariadb
sudo mariadb -N -e "SELECT @@secure_file_priv"

The first line shows the anonymous accounts and FILE-privileged accounts that will be changed. The settings are applied when the last line prints /var/lib/mysql-files/. The final restart drops database connections briefly, and afterwards LOAD DATA INFILE and INTO OUTFILE outside that directory are refused. Check first whether existing applications use them. This block can be run again.

Example column privilege that leaves out the ciphertext column

GRANT SELECT (id, name) ON appdb.customers TO 'reporter'@'localhost';

Common errors

7. Verify the installation

Character set and a basic round trip

SELECT @@character_set_client, @@character_set_connection, @@character_set_server;
SELECT CONVERT(strcrypt_decrypt(strcrypt_encrypt_algo('test', 'aria256gcm')) USING utf8mb4) AS result;

Round-trip test (1,000 rows; the test table is dropped at the end)

CREATE TABLE strcrypt_selftest (id INT PRIMARY KEY, val VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL);
INSERT INTO strcrypt_selftest SELECT seq, CONCAT('row-', seq, '-홍길동') FROM seq_1_to_1000;

SELECT COUNT(*) AS CHECKED,
       SUM(NOT (CAST(strcrypt_decrypt(strcrypt_encrypt_algo(val, 'aria256gcm')) AS BINARY) <=> CAST(val AS BINARY))) AS MISMATCHES
FROM strcrypt_selftest;

DROP TABLE strcrypt_selftest;

Run these as the application user in the application database (for example mariadb -u app_user -p appdb). The first query should return utf8mb4 three times, the second test, and the round-trip test CHECKED 1000 | MISMATCHES 0. The round-trip test needs CREATE, DROP, INSERT, and SELECT privileges.

Common errors

SQL functions

Use them like ordinary functions in SELECT, INSERT, and UPDATE. NULL input gives NULL output. The result character set is binary, so wrap results in CONVERT(... USING utf8mb4) to use them as strings, and compare bytes with CAST(... AS BINARY) (see the step 7 examples). Name the algorithm with strcrypt_encrypt_algo when encrypting. Ciphertext from strcrypt_encrypt without an algorithm has no header, so it no longer decrypts if the server operating mode changes.

When encryption or decryption of one row fails, the statement does not stop; only that row becomes NULL. Tampered ciphertext, a different key, values that are not Base64, input that is not UTF-8, and input containing NUL bytes all end this way, and the result cannot be told apart from NULL input.

Ciphertext differs every time, even for the same value, so it cannot be indexed. For exact-match search, store strcrypt_search_index(value, 'table.column') in an indexed search column and compare it with the index of the search term. Do not use an unkeyed SHA-256 hash: guessable values such as national ID numbers can be recovered by hashing every candidate. A search column reveals which rows share a value, so add one only where search is needed, keeping in mind that UDFs have no per-function execute privilege, so any account that can connect can compute indexes for candidate values.

Bulk processing

MariaDB has no UDF that takes many rows at once. UDFs are called once per row, so the number of calls cannot be reduced inside SQL. For many rows, query chunks in primary key order outside the database, encrypt or decrypt each chunk with the CLI in one call, and apply the results by primary key. Ciphertext from the CLI can be decrypted with the UDFs, and ciphertext from the UDFs can be decrypted with the CLI. This method needs neither the server's local_infile setting nor the temporary table privilege, only SELECT and UPDATE.

Prepare the CLI

mkdir -p ~/strcrypt && cd ~/strcrypt
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/strcrypt
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/strcrypt.hmac
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/SHA256SUMS
sha256sum -c --ignore-missing SHA256SUMS
chmod +x strcrypt

Working directory (key copy and connection details)

mkdir -p ~/strcrypt-batch && cd ~/strcrypt-batch
sudo install -o "$USER" -m 600 /etc/strcrypt/strcrypt.key strcrypt.key
(umask 077; printf '[client]\nuser=%s\npassword=%s\n' 'app_user' 'APP_PASSWORD' > app.cnf)
export STRCRYPT_KEY_FILE="$PWD/strcrypt.key"
export PATH="$HOME/strcrypt:$PATH"
DB="mariadb --defaults-extra-file=$PWD/app.cnf appdb"

The CLI must use the same key as the database. The server key file is readable only by mysql, so this makes a copy the current account can read and keeps the application user's connection details in a file with permission 600. Replace APP_PASSWORD with the real password. Delete the key copy and app.cnf when you are done.

Encrypt an existing column in bulk

set -euo pipefail
last=0
while :; do
  rows=$($DB -N -B -e "SELECT id, REPLACE(TO_BASE64(ssn), '\n', '') FROM customers
                       WHERE id > $last AND ssn IS NOT NULL AND ssn_enc IS NULL ORDER BY id LIMIT 1000")
  [ -z "$rows" ] && break
  enc=$(printf '%s\n' "$rows" | cut -f2 | strcrypt encrypt-batch -a aria256gcm)
  paste <(printf '%s\n' "$rows" | cut -f1) <(printf '%s\n' "$enc") | awk -F'\t' '
    BEGIN { print "UPDATE customers SET ssn_enc = CASE id" }
    { print "WHEN " $1 " THEN \047" $2 "\047"; ids = ids (NR > 1 ? "," : "") $1 }
    END { print "END WHERE id IN (" ids ");" }' | $DB
  last=$(printf '%s\n' "$rows" | tail -n 1 | cut -f1)
  echo "encrypted up to id $last"
done

Run it in the same shell as the working directory setup. In an interactive shell, set -e closes the shell on an error, so saving both blocks to one file and running it with bash is safer. Each pass queries 1,000 rows of plaintext as Base64, encrypts them with encrypt-batch, and applies the chunk with a single UPDATE statement. Each chunk prints encrypted up to id .... Because of the ssn_enc IS NULL condition, running the same command again resumes where it stopped. Each statement must be smaller than max_allowed_packet, so reduce the chunk size for large values.

• TO_BASE64 inserts a line break every 76 characters. Without REPLACE removing them, the CLI stops with invalid Base64.
• NULL comes out of the query as the string NULL, which also reads as Base64, and the CLI encrypts the wrong value without an error, so the ssn IS NOT NULL condition cannot be dropped.

Check the results (run as app_user)

SELECT COUNT(*) AS CHECKED,
       SUM(NOT (CAST(strcrypt_decrypt(ssn_enc) AS BINARY) <=> CAST(ssn AS BINARY))) AS MISMATCHES
FROM customers WHERE ssn IS NOT NULL;

If MISMATCHES is 0, all ciphertext produced by the CLI decrypted with the UDF.

Decrypt ciphertext from the UDFs with the CLI

$DB -N -B -e "SELECT strcrypt_encrypt_algo(ssn, 'aria256gcm') FROM customers WHERE id IN (1, 7) ORDER BY id" |
  strcrypt decrypt-batch | while read -r line; do printf '%s' "$line" | base64 -d; echo; done

decrypt-batch writes plaintext as Base64, so base64 -d turns each line back and prints it.

Limitations

• UDF calls cannot be restricted per user. Protect data with connecting accounts and read privileges on the ciphertext column.
• Plaintext must be UTF-8 without NUL bytes. Pass values from columns that are not utf8mb4 as CONVERT(value USING utf8mb4).
• Results sent to the client must be smaller than max_allowed_packet. Values stored into a table inside the server can exceed that limit.
• Changes to the key file or server environment variables apply after mariadb restarts.
• The same value encrypts to a different ciphertext each time, so you cannot search or index the ciphertext column. Keep a separate hash column if you need lookups.
• Plaintext passes through the memory of the mariadbd process on the database server.

API reference

Encryption and decryption results use the binary character set. Key errors, library errors, and a disallowed algorithm given as a constant stop the whole statement with ERROR 1123; other per-row failures return NULL for that row only.

Test environments

Steps 1 to 7, the bulk processing example (a 2,495-row round trip including Korean and long values), calls from an account with only USAGE and from anonymous accounts, blocking by column privileges, and refusal to read the key file after setting secure_file_priv were confirmed (2026-09-17).