OneLinersCommand workbench
Guides
Security / Databases & Data

Harden a MariaDB Server for production

Remove anonymous and test access, restrict administration, enforce TLS where remote clients are required, audit grants, and verify exposure.

60 min8 stepsChanges system stateRevision 2
Save or explore
#mariadb#hardening#accounts#tls#firewall#audit
Save to collectionCreate a collection in the sidebar first.
0 of 8 steps completed
Goal

Reduce default and legacy database attack paths without relying blindly on an interactive hardening script.

Supported environments
  • MariaDB 10.11 LTS, 11.4 LTS
Prerequisites
  • Recovery access and maintenance window Keep console access and schedule write-affecting work so an interrupted package, grant, backup, or replication change can be recovered.
  • Verified backup Create a fresh backup and prove that its format, encryption key, and restore procedure are available before changing production data.
  • Capacity and compatibility Check database version, storage headroom, character sets, application driver compatibility, and expected downtime.df -h; df -i; free -m
Operating boundary

OneLiners never runs these steps or stores secrets. Review placeholders, versions, current state, and change-control requirements before using a command.

Full guide

What you will build

System
  • A production-ready MariaDB implementation of “Harden a MariaDB Server for production” with explicit identities, configuration ownership, observable checkpoints and a tested reversal path.
  • A before-state, native validation sequence and concrete evidence set covering the database service, client behavior, data or administrative surface affected by this procedure.
  • A least-privilege operational runbook that keeps secrets out of commands and verifies both the required success path and an expected denial or failure boundary.
Observable outcome
  • Reduce default and legacy database attack paths without relying blindly on an interactive hardening script. The result is demonstrated through the guide's native verification commands and representative application or restore evidence.
  • Unrelated databases, accounts, listeners and services remain unchanged; operators can identify the exact revision, artifact and rollback action for this change.

Architecture

How the parts fit together

The database listener, authentication plugins, administrative accounts, filesystem ownership, logging and service policy form separate security boundaries. The tutorial inventories effective state first, removes anonymous/test exposure, restricts network reachability, and validates a dedicated application account. Each change is applied through supported configuration or SQL, observed through native status tables and tested before broader restrictions replace the prior state.

Effective server configurationDefines listeners, TLS, logging and runtime limits.
Account and grant modelLimits administration and application capabilities.
Host firewall/filesystemRestricts network and local access outside SQL authorization.
Audit evidenceShows denials, successful application behavior and remaining exceptions.
  1. Capture effective configuration, versions, listeners, identities, grants, workload health and a recoverable before-state.
  2. Prepare the hardening candidate using only official MariaDB interfaces, protected secret delivery and the narrowest necessary scope.
  3. Apply one controlled layer at a time, run the immediate checkpoint and inspect native logs/status before proceeding.
  4. Perform end-to-end success and expected-failure tests; retain rollback evidence and schedule recurring verification.

Assumptions

  • The installed MariaDB family and version are supported by the official sources linked below, and package/configuration provenance is known.
  • A maintenance or canary window, current recoverable backup and console-level recovery path exist before state-changing database work begins.
  • Operator, application, backup, replication and web-administration identities are separate; secrets are supplied by protected option files, environment injection with appropriate controls, or a secret manager.
  • Representative schema size, workload, storage capacity, network policy and recovery objectives are documented for the target environment.

Key concepts

user@host identity
MySQL-family authorization matches both an account name and its permitted client origin, not the name alone.
effective configuration
The final runtime values after packaged defaults and ordered configuration fragments have been combined.
consistent state
Data and metadata captured at one valid transactional or recovery point rather than a mixture of moments.
recovery objective
The acceptable data-loss window and restoration time that drive backup, replication and validation design.
positive and negative verification
Proving an intended operation succeeds and an operation outside the granted/security boundary fails.

Before you copy

Values used in this guide

{{appUser}}

Non-login operating-system identity used by the application workers.

Example: billing
{{dbHost}}

Operator-reviewed value used by this tutorial for dbHost; derive it from the target environment and never from untrusted request data.

Example: db-primary.example.com

Security and production boundaries

  • Never place database passwords, encryption keys, recovery codes or private certificates directly in reusable shell history or repository files.
  • Do not use `%`, global grants, disabled TLS verification, public administrative interfaces or world-readable dumps as troubleshooting shortcuts.
  • Treat backups, slow logs and phpMyAdmin sessions as sensitive data; encrypt, restrict, rotate and monitor them according to the same or stronger policy as production.

Stop before continuing if

  • Stop if the server version/config family is ambiguous, the backup cannot be restored, secrets would be exposed, or a proposed command affects databases/accounts beyond the declared scope.
  • Do not continue when native validation, representative workload, expected-denial test, TLS identity, replication continuity or storage capacity evidence is incomplete for this procedure.
  • Immediately isolate or revert a change that broadens a listener/grant, breaks application health, introduces replication error, loses recoverability or produces unbounded sensitive logs.
01

instruction

Back up data, accounts, and grants

caution

Capture a logical backup, mysql.user-compatible metadata through SHOW CREATE USER, and current grants before deleting or altering accounts.

Why this step matters

Back up data, accounts, and grants is a separate checkpoint because it establishes one auditable part of reduce default and legacy database attack paths without relying blindly on an interactive hardening script. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Capture a logical backup, mysql.user-compatible metadata through SHOW CREATE USER, and current grants before deleting or altering accounts. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Back up data, accounts, and grants”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb-dump
Invokes the principal tool or configuration operation for “Back up data, accounts, and grants”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
|
Passes standard output to the next read-only inspection stage; review each stage separately if the combined result is surprising.
Command
sudo mariadb-dump --all-databases --single-transaction --routines --events --triggers | gzip -c > /root/mariadb-before-hardening.sql.gz; sudo mariadb -NBe "SELECT User,Host FROM mysql.user ORDER BY User,Host" > /root/mariadb-accounts-before.txt
Example output / evidence
Backup and account inventory files are non-empty.

Checkpoint: Checkpoint: Back up data, accounts, and grants

Continue whenBackup and account inventory files are non-empty. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb-dump reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Back up data, accounts, and grants” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb-dump 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
02

command

Remove anonymous accounts

caution

List the exact rows first, then drop every blank-user account using quoted host values.

Why this step matters

Remove anonymous accounts is a separate checkpoint because it establishes one auditable part of reduce default and legacy database attack paths without relying blindly on an interactive hardening script. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

List the exact rows first, then drop every blank-user account using quoted host values. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Remove anonymous accounts”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb
Invokes the principal tool or configuration operation for “Remove anonymous accounts”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
Command
sudo mariadb -NBe "SELECT User,Host FROM mysql.user WHERE User=''"; sudo mariadb -e "DROP USER IF EXISTS ''@'localhost', ''@'$(hostname -s)';"
Example output / evidence
No blank User rows remain.

Checkpoint: Checkpoint: Remove anonymous accounts

Continue whenNo blank User rows remain. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Remove anonymous accounts” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
03

command

Remove the test database and wildcard grants

caution

Drop the default test database and verify no account retains privileges on test or test_% schemas.

Why this step matters

Remove the test database and wildcard grants is a separate checkpoint because it establishes one auditable part of reduce default and legacy database attack paths without relying blindly on an interactive hardening script. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Drop the default test database and verify no account retains privileges on test or test_% schemas. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Remove the test database and wildcard grants”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb
Invokes the principal tool or configuration operation for “Remove the test database and wildcard grants”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
Command
sudo mariadb -e "DROP DATABASE IF EXISTS test; DELETE FROM mysql.db WHERE Db='test' OR Db LIKE 'test\\_%'; FLUSH PRIVILEGES;"
Example output / evidence
The test schema and wildcard test grants are absent.

Checkpoint: Checkpoint: Remove the test database and wildcard grants

Continue whenThe test schema and wildcard test grants are absent. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Remove the test database and wildcard grants” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
04

config

Restrict administrative authentication

caution

Keep root local through unix_socket where supported, create named break-glass administration separately, and reject remote root hosts.

Why this step matters

Restrict administrative authentication is a separate checkpoint because it establishes one auditable part of reduce default and legacy database attack paths without relying blindly on an interactive hardening script. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Keep root local through unix_socket where supported, create named break-glass administration separately, and reject remote root hosts. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Restrict administrative authentication”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb
Invokes the principal tool or configuration operation for “Restrict administrative authentication”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
configuration file
Is the persistent configuration boundary changed by this step. Keep an attributable backup and validate the consumer before reload.
Configuration
sudo mariadb -e "SELECT User,Host,plugin FROM mysql.user WHERE User IN ('root','admin');"; sudo mariadb -NBe "SELECT CONCAT(QUOTE(User),'@',QUOTE(Host)) FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost','127.0.0.1','::1');"
Example output / evidence
Root has no wildcard or remote host account.

Checkpoint: Checkpoint: Restrict administrative authentication

Continue whenRoot has no wildcard or remote host account. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Restrict administrative authentication” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
05

config

Constrain listener and firewall

caution

Use loopback when possible; otherwise use a private address plus an explicit firewall allowlist and no public 0.0.0.0/0 rule.

Why this step matters

Constrain listener and firewall is a separate checkpoint because it establishes one auditable part of reduce default and legacy database attack paths without relying blindly on an interactive hardening script. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Use loopback when possible; otherwise use a private address plus an explicit firewall allowlist and no public 0.0.0.0/0 rule. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Constrain listener and firewall”. The scope must remain limited to the selected application and supported host.

Syntax explained

ss
Invokes the principal tool or configuration operation for “Constrain listener and firewall”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
|
Passes standard output to the next read-only inspection stage; review each stage separately if the combined result is surprising.
configuration file
Is the persistent configuration boundary changed by this step. Keep an attributable backup and validate the consumer before reload.
Configuration
sudo ss -lntp | grep ':3306' || true; sudo nft list ruleset 2>/dev/null | grep -n 3306 || true
Example output / evidence
Only intended interfaces and source networks can reach MariaDB.

Checkpoint: Checkpoint: Constrain listener and firewall

Continue whenOnly intended interfaces and source networks can reach MariaDB. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when ss reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Constrain listener and firewall” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v ss 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
06

config

Configure and require encrypted remote connections

caution

Install a private-key-protected server certificate, configure CA/cert/key paths, restart, and apply REQUIRE SSL or REQUIRE X509 to remote accounts.

Why this step matters

Configure and require encrypted remote connections is a separate checkpoint because it establishes one auditable part of reduce default and legacy database attack paths without relying blindly on an interactive hardening script. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Install a private-key-protected server certificate, configure CA/cert/key paths, restart, and apply REQUIRE SSL or REQUIRE X509 to remote accounts. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Configure and require encrypted remote connections”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb
Invokes the principal tool or configuration operation for “Configure and require encrypted remote connections”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
configuration file
Is the persistent configuration boundary changed by this step. Keep an attributable backup and validate the consumer before reload.
Configuration
sudo mariadb -e "SHOW VARIABLES LIKE 'have_ssl'; SHOW VARIABLES LIKE 'ssl_%'; SELECT User,Host,ssl_type FROM mysql.user WHERE Host NOT IN ('localhost','127.0.0.1','::1');"
Example output / evidence
TLS is enabled and every approved remote account has an explicit requirement.

Checkpoint: Checkpoint: Configure and require encrypted remote connections

Continue whenTLS is enabled and every approved remote account has an explicit requirement. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Configure and require encrypted remote connections” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
07

instruction

Audit wildcard hosts and global privileges

read-only

Review accounts with %, anonymous hosts, GRANT OPTION, FILE, PROCESS, SUPER-equivalent, and privileges on *.*; replace them with roles or database-scoped grants.

Why this step matters

Audit wildcard hosts and global privileges is a separate checkpoint because it establishes one auditable part of reduce default and legacy database attack paths without relying blindly on an interactive hardening script. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Review accounts with %, anonymous hosts, GRANT OPTION, FILE, PROCESS, SUPER-equivalent, and privileges on *.*; replace them with roles or database-scoped grants. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Audit wildcard hosts and global privileges”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb
Invokes the principal tool or configuration operation for “Audit wildcard hosts and global privileges”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
|
Passes standard output to the next read-only inspection stage; review each stage separately if the combined result is surprising.
Command
sudo mariadb -NBe "SELECT User,Host FROM mysql.user ORDER BY User,Host" | while read -r u h; do sudo mariadb -NBe "SHOW GRANTS FOR '$u'@'$h'"; done
Example output / evidence
Every account and global privilege has a named owner and reason.

Checkpoint: Checkpoint: Audit wildcard hosts and global privileges

Continue whenEvery account and global privilege has a named owner and reason. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Audit wildcard hosts and global privileges” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
08

verification

Restart and test expected and denied paths

caution

Test local admin, application login, encrypted remote login, rejected anonymous login, and rejected unapproved source before closing recovery access.

Why this step matters

Restart and test expected and denied paths is a separate checkpoint because it establishes one auditable part of reduce default and legacy database attack paths without relying blindly on an interactive hardening script. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Test local admin, application login, encrypted remote login, rejected anonymous login, and rejected unapproved source before closing recovery access. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Restart and test expected and denied paths”. The scope must remain limited to the selected application and supported host.

Syntax explained

systemctl
Invokes the principal tool or configuration operation for “Restart and test expected and denied paths”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}
Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
Command
Fill variables0/2 ready

Values stay on this page and are never sent or saved.

sudo systemctl restart mariadb; sudo mariadb-admin ping; mariadb --ssl --host={{dbHost}} --user={{appUser}} --password -e 'STATUS;'
Example output / evidence
Expected paths work, TLS is in use, and anonymous or unauthorized paths fail.

Checkpoint: Checkpoint: Restart and test expected and denied paths

Continue whenExpected paths work, TLS is in use, and anonymous or unauthorized paths fail. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when systemctl reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Restart and test expected and denied paths” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v systemctl 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

Finish line

Verification checklist

Account hygienesudo mariadb -NBe "SELECT User,Host,plugin,ssl_type FROM mysql.user ORDER BY User,Host"No anonymous or remote-root account exists and remote users require the intended TLS mode.
Public exposuresudo ss -lntp | grep ':3306' || true; sudo nft list ruleset 2>/dev/null | grep 3306 || trueThe approved bind address and firewall allowlist are the only exposure.

Recovery guidance

Common problems and safe checks

The native client reports access denied even though a similarly named account has grants.

Likely causeDatabase identities include a host component and authentication plugin; the connection matched another user@host row or transport.

Safe checks
  • mysql -e 'SELECT USER(), CURRENT_USER();'
  • mysql -e "SELECT User,Host,plugin FROM mysql.user ORDER BY User,Host;"
  • mysql -e 'SHOW GRANTS FOR CURRENT_USER;'

ResolutionIdentify the exact matched account and create or modify only the intended user@host identity; do not broaden it to '%' as a shortcut.

TLS is enabled but the client still connects with an unexpected identity or without hostname verification.

Likely causeThe client uses encryption-only mode, connects by an address absent from the SAN, trusts a broad system CA set, or another option file overrides policy.

Safe checks
  • mysql --print-defaults
  • openssl s_client -starttls mysql -connect db.example.com:3306 -servername db.example.com </dev/null
  • mysql -e "SHOW STATUS LIKE 'Ssl_%';"

ResolutionUse the documented hostname, intended CA and VERIFY_IDENTITY-equivalent mode; remove conflicting client options and retest expected failure with a wrong hostname.

A change passes syntax checks but application errors, lag, latency or disk use increases.

Likely causeThe candidate is syntactically valid but workload, lock, cache, rotation, privilege or version assumptions are wrong.

Safe checks
  • mysqladmin ping
  • mysql -e 'SHOW PROCESSLIST;'
  • journalctl -u mariadb -u mysql -p warning --since '-10 min' --no-pager

ResolutionStop promotion, preserve evidence, revert the smallest changed configuration/account/routing layer and reproduce with a safe representative workload.

After the procedure

Alternatives and next steps

Consider these alternatives

  • Use a managed database only after private connectivity, identity, encryption, monitoring, backup restoration and responsibility boundaries are proven.
  • Use immutable configuration automation and a disposable recovery environment when it produces stronger review, repeatability and rollback than manual host edits.

Operate it safely

  • Automate health, capacity, certificate, replication, backup and restore-test alerts with an owner and response runbook.
  • Review accounts, grants, package support, encryption material and recovery evidence after every major release and at least every 90–180 days.
  • Practice the rollback or restore from recorded artifacts on an isolated host so the documented time and dependencies remain credible.

Reference

Frequently asked questions

Does a successful command mean the procedure is complete?

No. Database tools may return success before application compatibility, replica replay, restore integrity, TLS identity or expected denials are proven. Use every checkpoint and final verification.

Can an administrator use the application account for convenience?

No. Separate identities make least privilege, rotation, audit and incident containment possible; the application should not hold administrative capabilities.

Why test restoration or negative access separately?

A backup that cannot restore and a grant that was never tested for denial are assumptions, not controls. Isolation makes those tests safe and repeatable.

Recovery

Rollback

Restore accounts and configuration from the protected pre-change backup, not by enabling broad access.

  1. Restore the previous configuration, validate options, and restart MariaDB.
  2. Recreate only required accounts from captured SHOW CREATE USER and SHOW GRANTS statements.
  3. Restore the test schema or anonymous account only if a documented legacy dependency proves it is required, then isolate it.

Evidence

Sources and review

Verified 2026-07-25Review due 2026-10-23
MariaDB security guidanceofficialMariaDB account management SQL statementsofficialMariaDB secure connections and TLSofficial