Check MariaDB Access Rules Safely with mysqlaccess

"Why can this user connect from that host but not this one" is the question mysqlaccess answers. It shows exactly which privileges MariaDB would find for a given host, user and database, including wildcard host checks. The examples use mysqlaccess, the compatibility symlink to mariadb-access, from the Debian package mariadb-client version 1:10.11.14-0ubuntu0.24.04.1. The installed program identifies itself as version 2.10, dated 13 September 2019.

Allow about fifteen minutes. You need a shell, a reachable MariaDB server, credentials that can read the grant tables, and a known database name. This guide is read-only until the final warning section: it does not edit users, privileges or server configuration.

1. Confirm which program is installed

Start with commands that do not contact the database:

$ command -v mysqlaccess
/usr/bin/mysqlaccess
$ mysqlaccess --version
mariadb-access Version 2.10, 13 Sep 2019
By RUG-AIV, by Yves Carlier ([email protected])
Changes by Steve Harvey ([email protected])

The output may include the program's warranty notice and other identification lines. The important checks are that the command exists and reports a version. mysqlaccess and mariadb-access use the same installed script on this system.

Checkpoint: If command -v finds nothing, install the client package through your normal distribution process before continuing. Do not copy a script into /usr/bin by hand.

2. Understand the three positional values

The basic form is:

mysqlaccess [host_name [user_name [db_name]]] [options]

At least a user and database are required by the installed program. If you omit the privilege host, it assumes localhost. These are the host and user being checked, not necessarily the host and account used to connect to MariaDB. Keep that distinction clear: --rhost selects the server to contact, while --host selects the host value in the access rule.

For a local check, replace the placeholders with real values from your server:

$ mysqlaccess 'localhost' 'report_user' 'inventory' \
    --rhost='127.0.0.1' --user='audit_reader' --password

With --password and no password value, the client prompts instead of putting the secret in the command line. Do not use --password=secret in a shared shell, terminal recording or process list. The command also has separate --superuser and --spassword options for the account it uses to inspect or manage the grant database. Use only the least-privileged account that can perform the check.

3. Read the ordinary report

Run the previous command with a real server and credentials. A successful report describes the selected combination and then shows the privilege flags found in the user, db and host tables. A typical report has this shape:

Access-rights
for USER 'report_user', from HOST 'localhost', to DB 'inventory'
        +-----------------+---+ +-----------------+---+
        | select_priv     | Y | | drop_priv       | N |
        | insert_priv     | Y | | reload_priv     | N |
        | update_priv     | Y | | shutdown_priv   | N |
        +-----------------+---+ +-----------------+---+
The following rules are used:
 db    : ...
 host  : ...
 user  : ...

The exact rows and privilege columns depend on the server. Read the rule lines as the evidence behind the result, especially when a wildcard or more specific host rule is involved. A warning about passwordless access is a security finding, not harmless decoration.

This tool has a deliberate limit: it checks only the user, db and host tables. It does not check table privileges in tables_priv, column privileges in columns_priv, or routine privileges in procs_priv. If the application uses those finer-grained grants, follow up with a separate review.

4. Test wildcard host rules without shell expansion

MariaDB privilege patterns can contain wildcards. The shell can interpret some of the same characters before mysqlaccess sees them, so quote the host, user and database values. This checks the rule for a user connecting from any host represented by the literal pattern:

$ mysqlaccess '*' 'report_user' 'inventory' \
    --rhost='db.example.invalid' --user='audit_reader' --password --brief

The --brief option produces a single-line tabular report, which is useful when comparing several host patterns or feeding a result into a review note. Use --table when the wider table layout is easier to inspect by eye. Do not remove the quotes around *, ?, % or _ merely to make the command shorter.

Checkpoint: Compare the host value in the report with the value you intended to test. If it says localhost when you meant a wildcard, the shell changed your input or you supplied the arguments in the wrong order.

5. Separate diagnostics from grant-table changes

The normal report does not require --copy, --preview, --commit or --rollback. Those options are part of the script's older temporary-table workflow. --copy reloads temporary grant tables from the originals. --preview shows differences after temporary changes. --commit copies temporary rules into the live grant tables, and the manpage says the server grant tables must then be flushed. --rollback restores the most recent one-level backup.

Warning: Do not paste --commit into a diagnostic command. It changes access control data and can affect every client using the server. If you intentionally operate this legacy workflow, take a server-level backup, verify the proposed difference, use an account authorised to administer grants, and arrange the required grant-table reload. After an accidental commit, stop and assess the live grants before attempting the tool's one-level rollback; rollback is not a substitute for a tested backup and cannot undo several successive changes.

6. Troubleshoot the common failures

A missing user or database usually means the command was invoked without the required positional values. Recheck the order and use --help:

$ mysqlaccess --help
Usage: mariadb-access [host [user [db]]] OPTIONS
...
    At least the user and the db must be given

A connection error is separate from a denied privilege result. Check --rhost, the server port and the login account with your normal MariaDB client, then retry the access check. If the client reports that it cannot read the access database, the connecting account lacks the required visibility; ask a database administrator for a narrowly scoped audit account rather than trying a superuser password in a script.

If the command reports a broken pipe on a non-standard installation, inspect the installed mariadb-access script's configured path to the MariaDB client. The manpage documents an old configuration variable for this case. Treat editing a vendor script as a controlled maintenance change: preserve the original, test it, and prefer correcting the package installation or path management when possible.

Done means