Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 40 ยท Replication, Backup and Security

Securing MariaDB

Harden a MariaDB server: network exposure, TLS, encryption at rest, the audit plugin, data masking with views, prepared statements against SQL injection and a deployment checklist.

Database breaches rarely start with a clever exploit. They start with a server exposed to the internet, a default or shared password, an application that builds SQL from user input, a backup left in a public bucket, or an account with far more power than it needs. Security is therefore mostly a list of ordinary habits applied in layers: network, authentication, privileges, application code, encryption and auditing.

This lesson walks through each layer for MariaDB. Server settings and plugins are read-only examples; the SQL-injection and data-masking ideas have runnable demonstrations.

Layer 1: the network

  • Bind to the interface you need: bind-address = 127.0.0.1 for a local-only database, or a private IP for a cluster. Never expose port 3306 to the public internet; put the database in a private subnet and reach it through the application or a VPN/SSH tunnel.
  • Use firewall rules (security groups) that allow only the application hosts, replicas and monitoring.
  • skip_name_resolve = ON avoids DNS lookups and makes host-based accounts use IP addresses.
  • Disable features you do not use: local_infile = 0 (stops LOAD DATA LOCAL abuse), and restrict file operations with secure_file_priv = /var/lib/mysql-files so INTO OUTFILE and LOAD_FILE can only touch one directory.
  • Run mariadb-secure-installation on a new server: sets the root authentication, removes anonymous users and the test database, disallows remote root login.

Layer 2: authentication and privileges

Covered in users, grants and roles: one account per purpose, specific hosts, least privilege, long random passwords stored in a secret manager, password expiry and unix_socket for local administration. Add max_connections and per-user MAX_USER_CONNECTIONS so one runaway client cannot lock out the others, and consider connect_timeout/max_connect_errors plus fail2ban-style tooling on the host.

Layer 3: encrypt connections with TLS

Without TLS, passwords (hashed during the handshake) and query results cross the network in readable form. Enable TLS on the server:

INI
[mysqld]
ssl_ca   = /etc/mysql/ssl/ca.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key  = /etc/mysql/ssl/server-key.pem
tls_version = TLSv1.2,TLSv1.3

and make accounts demand it:

SQL
ALTER USER 'shop_app'@'10.0.0.%' REQUIRE SSL;
ALTER USER 'admin'@'%' REQUIRE X509;        -- client certificate required
SHOW STATUS LIKE 'Ssl_cipher';              -- non-empty when your session is encrypted
SELECT user, host, ssl_type FROM mysql.global_priv;   -- see mysql.user in old versions

Clients should verify the server certificate (--ssl-verify-server-cert, or sslmode=verify-full-style options in drivers), otherwise TLS only protects against passive eavesdropping, not an impersonating server. The same applies to replication links (MASTER_SSL=1 in CHANGE MASTER).

Layer 4: stop SQL injection in the application

SQL injection happens when user input is pasted into SQL text and becomes part of the statement. The fix is not clever escaping but prepared statements with placeholders: the SQL text and the values travel separately, so a value can never change the statement structure.

PHP
// vulnerable
$sql = "SELECT * FROM users WHERE name = '" . $_GET['name'] . "'";

// safe (PDO / mysqli prepared statements)
$stmt = $pdo->prepare('SELECT id, name FROM users WHERE name = ?');
$stmt->execute([$_GET['name']]);

Every language has the same feature (? or named parameters). Identifiers (table and column names, ORDER BY direction) cannot be bound, so map them from an allow-list in code. Also use a low-privilege application account so that even a successful injection cannot DROP or read other databases.

You can see exactly why concatenation is dangerous by building the final text yourself:

SQL What the injected statement looks like

Press Run (or Ctrl + Enter) to see the result here.

The second statement becomes ... WHERE name = 'x' OR '1'='1': a condition that is true for every row. With a prepared statement the server would search for a user literally named x' OR '1'='1 and find none. The next demo runs both versions against a real table:

SQL Concatenation versus a bound value

Press Run (or Ctrl + Enter) to see the result here.

In the first query the attacker's text is part of the SQL; in the second the same characters are only data (the doubled quotes are how a literal quote is written inside a string, which a driver does for you when binding).

Layer 5: limit what data can be seen

Never store secrets you do not need. Passwords belong in the database only as slow salted hashes made by the application (password_hash in PHP, bcrypt, argon2), never encrypted and never in plain text. Card numbers and national ids should live with a specialized provider whenever possible.

For data that must be visible to some people only in part, expose a view that masks columns and grant access to the view, not the table:

SQL
CREATE VIEW customers_support AS
SELECT id,
       name,
       CONCAT(LEFT(email, 2), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email,
       CONCAT('****', RIGHT(card_last4, 4)) AS card
FROM customers;

GRANT SELECT ON shop.customers_support TO 'support'@'%';
SQL Masking with a view

Press Run (or Ctrl + Enter) to see the result here.

MariaDB's MaxScale proxy offers a masking filter as well, and the DEFINER mechanism described in views is what lets the low-privilege user read the view but not the table.

Layer 6: encryption at rest

Data files, logs and backups can reveal everything if a disk or snapshot is stolen. MariaDB supports transparent data-at-rest encryption for InnoDB, Aria, temporary files and binary logs:

INI
[mysqld]
plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/encryption/keyfile.enc
file_key_management_filekey  = FILE:/etc/mysql/encryption/keyfile.key
file_key_management_encryption_algorithm = AES_CBC

innodb_encrypt_tables      = ON
innodb_encrypt_log         = ON
innodb_encryption_threads  = 4
encrypt_binlog             = ON
aria_encrypt_tables        = ON
encrypt_tmp_files          = ON
SQL
CREATE TABLE payments (...) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=1;
SELECT NAME, ENCRYPTION_SCHEME FROM information_schema.INNODB_TABLESPACES_ENCRYPTION;

The file key plugin is simple, but the key sits on the same host; for production use a key service (HashiCorp Vault plugin, AWS KMS plugin or similar). Encryption protects stolen disks and backups, not a logged-in attacker or SQL injection, since the server decrypts data for authorized queries. Remember to encrypt backups too (see backup and restore).

Layer 7: audit and monitor

The audit plugin logs connections and queries so you can answer "who changed this?" and detect attacks:

INI
plugin_load_add = server_audit
server_audit_logging = ON
server_audit_events  = CONNECT,QUERY_DDL,QUERY_DCL
server_audit_file_path = /var/log/mysql/audit.log
server_audit_file_rotate_size = 100000000

Event types include CONNECT (logins, including failures), QUERY (all statements, heavy), QUERY_DDL, QUERY_DCL (grants), and QUERY_DML. Ship the log to a separate system so an intruder cannot erase it. Also watch: failed-login bursts, new accounts and grants, Aborted_connects, long-running statements, unusual SELECT volume from one account, and replication errors. Keep the server version patched; subscribe to MariaDB security announcements.

Hardening checklist

  1. Private network only, firewall in front, TLS required.
  2. No anonymous or remote root; unique account per application and tool; least privilege.
  3. Prepared statements everywhere; allow-lists for identifiers.
  4. Passwords hashed by the app; secrets in a secret store; backups encrypted.
  5. local_infile=0, secure_file_priv set, test database removed.
  6. Encryption at rest if the data is sensitive or regulated.
  7. Audit plugin on, logs shipped off-host, alerts configured.
  8. Updates applied; restore tested; incident contacts known.

Common mistakes

  • Opening 3306 to 0.0.0.0/0 "just for a minute".
  • One shared password in every environment, committed to git.
  • Escaping strings by hand instead of using prepared statements.
  • Believing encryption at rest stops SQL injection.
  • Verifying nothing: TLS enabled but the client skips certificate checks.
  • Logging full queries with sensitive values and leaving the log world-readable.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the most reliable defence against SQL injection?

    With bound parameters the value can never change the structure of the statement.
  2. 2. Which setting restricts where SELECT ... INTO OUTFILE and LOAD_FILE can read and write?

    secure_file_priv limits file import/export to one directory (or disables it).
  3. 3. What does encryption at rest protect against?

    The server still decrypts data for authorized queries, so it does not replace access control or injection defences.
  4. 4. Why mask data with a view and grant access only to the view?

    The view exposes a controlled subset or masked form of the data; the base table stays inaccessible.

Summary

  • Secure in layers: private network, TLS, least-privilege accounts, safe application code, encryption at rest and auditing.
  • Prepared statements, not string escaping, stop SQL injection.
  • Hash passwords in the application, mask data with views, encrypt backups.
  • Keep servers patched, monitor logs, and rehearse restores.

Next: Project: Library Management System