Secure MySQL with cnspec

Scan your MySQL-compatible servers to find security risks before they become incidents. cnspec evaluates accounts and their host patterns, password state, global and schema privileges, roles, authentication plugins, transport encryption, stored routines, tablespace encryption, and configuration variables.

cnspec connects with a MySQL account and inventories the server through read-only queries against information_schema, performance_schema, and the system catalogs. It never touches your data.

MySQL, MariaDB, and Percona Server are all supported by one provider. The flavor is detected automatically and reported as mysqldb.instance.flavor.

If you're new to cnspec, start with the Quickstart. For an overview of every database cnspec can scan, see the database scanning overview.

Prerequisites

To scan a MySQL-compatible server with cnspec, you must have:

• cnspec installed on your workstation
• Network access to the server on its port (3306 by default)
• An account that can read mysql.user, information_schema, and performance_schema

Authenticate

cnspec authenticates with a MySQL account and password, and negotiates TLS according to --tls-mode:

cnspec shell mysqldb db.contoso.com --user root --ask-pass

For a verified-TLS connection, add the certificate flags:

cnspec shell mysqldb db.contoso.com --user auditor --ask-pass --tls-mode true --tls-ca ca.pem

Prefer a least-privileged account for auditing. Read access to mysql.user, information_schema, and performance_schema covers everything below. Without it those collections return empty rather than failing the scan, so confirm mysqldb.instance.users is populated before trusting a passing account check.

Connection options

Verify with a quick MySQL check

cnspec shell mysqldb db.contoso.com --user root --ask-pass

cnspec> mysqldb.instance { version flavor requireSecureTransport localInfile }
mysqldb.instance: {
  version: "11.8.8-MariaDB-ubu2404"
  flavor: "mariadb"
  requireSecureTransport: false
  localInfile: true
}

If cnspec connects and shows the version, you're ready to scan.

Scan MySQL

By default cnspec discovers each schema as its own mysqldb-database asset alongside the server asset:

cnspec scan mysqldb db.contoso.com --user auditor --ask-pass

To scan the server only:

cnspec scan mysqldb db.contoso.com --user auditor --ask-pass --discover none

When a scan completes, cnspec prints a summary of all the checks it ran, grouped by policy, along with a risk score from 0 (no risk) to 100 (highest risk). Failed checks include remediation guidance to help you fix issues. To learn more about reading scan results, read Understand cnspec Results.

Mondoo ships Mondoo MySQL Security and Mondoo MariaDB Security policies, but those assess the server's on-disk option files through the os provider, not a live connection. They can't see accounts, granted privileges, or the effective value of a dynamic variable, which is exactly what this provider reads. Run both: the option-file policy tells you what the server loads at startup, and the checks below tell you what the running server actually has in effect.

For the live connection there's no out-of-the-box policy yet. The resources are shaped around the CIS MySQL Benchmark, so the checks below map closely to its recommendations. Use them as a starting point and create your own policies.

Explore and test checks interactively

cnspec shell mysqldb db.contoso.com --user auditor --ask-pass

List accounts

cnspec> mysqldb.instance.users { user host authPlugin hasPassword accountLocked }

Read a configuration variable

cnspec> mysqldb.instance.variables.where(name == "log_bin") { name value }

List global privileges held by an account

cnspec> mysqldb.instance.users.where(user == "appuser").first.privileges.where(scope == "GLOBAL") { privilegeType scope }

List installed plugins and components

cnspec> mysqldb.instance.plugins { name type status }
cnspec> mysqldb.instance.components { name urn }

List stored routines in a schema

cnspec> mysqldb.instance.schemas.where(name == "appdb").first.routines { name type securityType definer }

Example security checks

Ensure there are no anonymous accounts

An anonymous account has an empty user name and matches any name the client offers:

cnspec> mysqldb.instance.users.none(isAnonymous == true)
[ok] value: true

Ensure no account is reachable from any host

cnspec> mysqldb.instance.users.none(isWildcardHost == true)
[ok] value: true

Ensure every account has a password

cnspec> mysqldb.instance.users.where(accountLocked == false).all(hasPassword == true)
[ok] value: true

Ensure the server requires encrypted connections

cnspec> mysqldb.instance.requireSecureTransport == true
[ok] value: true

Ensure local_infile is disabled

local_infile lets a client instruct the server to read files from the client host, which turns a compromised server into a file-exfiltration tool:

cnspec> mysqldb.instance.localInfile == false
[ok] value: true

Ensure file access is confined to a directory

An empty secure_file_priv allows LOAD DATA and SELECT ... INTO OUTFILE anywhere the server user can reach:

cnspec> mysqldb.instance.secureFilePriv != ""
[ok] value: true

Ensure no account holds FILE

FILE reads and writes files on the server host as the server's OS user:

cnspec> mysqldb.instance.users.all(privileges.none(privilegeType == "FILE"))
[ok] value: true

Ensure SUPER is limited

cnspec> mysqldb.instance.users.where(privileges.any(privilegeType == "SUPER")) { user host }

Ensure a password-validation component is installed

cnspec> mysqldb.instance.components.any(name == "component_validate_password") || mysqldb.instance.plugins.any(name == "validate_password" && status == "ACTIVE")
[ok] value: true

Ensure passwords expire

A passwordLifetime of -1 means the account falls back to the server default, so check the default too:

cnspec> mysqldb.instance.variables.where(name == "default_password_lifetime").all(value != "0")
[ok] value: true

Ensure replication channels verify the source certificate

A channel that allows TLS but skips certificate verification is open to an interposed source:

cnspec> mysqldb.instance.replicationChannels.all(sslAllowed == true && sslVerifyServerCert == true)
[ok] value: true

Review SECURITY DEFINER routines

A DEFINER routine runs as its definer, so one defined by a privileged account is a privilege-escalation surface:

cnspec> mysqldb.instance.schemas { name routines.where(securityType == "DEFINER") { name type definer } }

Review unencrypted tables

cnspec> mysqldb.instance.schemas { name tables.where(encrypted == false) { name engine } }

Learn more

• MySQL Resource Pack Reference: every MySQL resource and field cnspec can query
• Write Effective MQL: guide to authoring checks and queries