MariaDB Users and Grants: Least-Privilege Setup Guide

Managing MariaDB users and grants is not merely a setup task. It is the database security boundary that decides what an application, deployment pipeline, backup job, analyst, or administrator can do after authentication succeeds. A shared account with broad permissions may get a system running quickly, but it also turns a leaked password, faulty migration, or compromised service into a much larger incident.

This guide builds a practical least-privilege model from the database upward. It explains MariaDB's 'user'@'host' identity rules, creates separate runtime and operational accounts, introduces roles, verifies effective privileges, and tests that forbidden actions really fail. It also covers account limits, password rotation, grant drift, common errors, and safe decommissioning.

The SQL examples are intended for current supported MariaDB releases, but privilege names and administrative requirements can change between versions. Run SHOW PRIVILEGES on the target server and consult the documentation for that exact release before granting operational privileges. Replace every example network, database, username, and secret with values appropriate to your environment.

Start with identities, not GRANT statements

Least privilege begins by listing workloads and operations. Do not begin with GRANT ALL and try to reduce it later. For each caller, document:

• Identity: which process or person connects.
• Source: the host or network from which MariaDB sees the connection.
• Objects: databases, tables, views, or routines it needs.
• Actions: reads, writes, schema changes, backups, or monitoring.
• Lifetime: permanent service, temporary migration, or emergency access.
• Owner: the team responsible for review and rotation.
• Failure mode: what breaks if the account is locked or its password expires.

A typical production service should not use one account for every task. A useful separation is:

Separating these identities limits blast radius and makes audit records meaningful. If a runtime credential appears in a DROP TABLE attempt, the event is immediately suspicious. If all automation uses root, attribution is nearly impossible.

Understand how MariaDB matches accounts

MariaDB accounts have two components: a username and a host, written as 'user'@'host'. These are different accounts:

'orders_api'@'10.20.30.25'
'orders_api'@'10.20.30.%'
'orders_api'@'localhost'

The host is the client source as observed by the database server. It is not the database server's address. Network address translation, containers, proxies, and Kubernetes nodes can therefore change which host value must match.

Exact host entries are preferred over wildcard entries during account matching. MariaDB also gives named accounts priority over anonymous accounts in relevant matches. A password can appear correct while authentication still fails because a more specific account was selected and has a different authentication method or credential.

After connecting, inspect both identities:

SELECT USER() AS client_identity, CURRENT_USER() AS matched_account;

USER() reports the client-provided user and observed host. CURRENT_USER() reports the account MariaDB actually used for authentication and authorization. For an Access denied investigation, this distinction is often more useful than resetting the password.

Host wildcards % and _ are supported, but broad entries such as 'orders_api'@'%' expand exposure and hide network-design mistakes. Prefer an exact source address for a stable application host. Use a narrowly defined subnet only when clients legitimately move within that subnet, and enforce the same boundary at the firewall. An account grant is not a replacement for network controls or TLS.

Inspect the server before changing access

Connect through an approved administrative path and record the server release, available privileges, existing accounts, and grants:

sudo mariadb

SELECT VERSION();
SHOW PRIVILEGES;

SELECT User, Host, plugin, account_locked, password_expired
FROM mysql.user
ORDER BY User, Host;

SHOW PRIVILEGES is authoritative for the privilege vocabulary understood by that server. Avoid copying a grant from a blog written for another MariaDB or MySQL version.

Do not edit grant tables directly. Use account-management statements such as CREATE USER, ALTER USER, GRANT, REVOKE, and DROP USER. These statements preserve server semantics and avoid brittle assumptions about internal table layouts.

Before changing an existing account, capture its definition and grants:

SHOW CREATE USER 'orders_api'@'10.20.30.25';
SHOW GRANTS FOR 'orders_api'@'10.20.30.25';

Store the result in an access-change ticket or protected configuration repository. Do not place password hashes, connection strings, or secrets in ordinary logs.

Create the application database deliberately

Create the database with an explicit character set and collation chosen for the application. This avoids inheriting an unexpected server default:

CREATE DATABASE orders
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Confirm the result:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'orders';

Collation choice affects comparison, ordering, uniqueness, and index behavior. Treat it as an application design decision, not a cosmetic preference. A later article in this series covers character-set migration and collation selection in depth.

Create a runtime account with narrow permissions

Create the account before granting privileges. Account creation alone gives it no application privileges:

CREATE USER 'orders_api'@'10.20.30.25'
  IDENTIFIED BY 'replace-with-a-secret-manager-value';

Do not paste a production password into shell history, chat, source control, or an orchestration manifest stored in plain text. Prefer your platform's secret-delivery mechanism and rotate any example credential copied during testing.

For an application that reads and modifies existing rows but does not deploy schema changes:

GRANT SELECT, INSERT, UPDATE, DELETE
ON orders.*
TO 'orders_api'@'10.20.30.25';

This account cannot create or drop tables. That is intentional. If the application genuinely calls stored routines, grant EXECUTE only where required. If it needs a particular sequence of table operations, consider table-level grants rather than expanding the entire database scope.

Verify the stored grants:

SHOW GRANTS FOR 'orders_api'@'10.20.30.25';

Do not add WITH GRANT OPTION to runtime accounts. That option allows the grantee to delegate its privileges and changes an ordinary application credential into an access-management credential.

Separate schema migrations from runtime traffic

Schema deployment often requires privileges such as CREATE, ALTER, INDEX, DROP, CREATE VIEW, or TRIGGER. Giving them permanently to the runtime account means a SQL injection path or faulty application release can alter structure.

Create a separate migration identity:

CREATE USER 'orders_migrate'@'10.20.30.40'
  IDENTIFIED BY 'replace-with-a-different-secret';

GRANT SELECT, INSERT, UPDATE, DELETE,
      CREATE, ALTER, INDEX, DROP,
      CREATE VIEW, SHOW VIEW, TRIGGER
ON orders.*
TO 'orders_migrate'@'10.20.30.40';

Tailor this list to the migration tool and actual change set. For example, a deployment that only adds an index does not necessarily need DROP. Some online-schema-change tools require additional metadata or trigger permissions. Test the exact tool against a staging server with production-like grants.

Lock the migration account when it is not in use if your release and deployment process support that workflow:

ALTER USER 'orders_migrate'@'10.20.30.40' ACCOUNT LOCK;

Unlock it only for an approved migration window, then lock it again:

ALTER USER 'orders_migrate'@'10.20.30.40' ACCOUNT UNLOCK;

For automated pipelines, frequent locking may be less practical than short-lived secrets, restricted source hosts, and tightly controlled jobs. The goal is to make powerful credentials unavailable during normal runtime.

Build read-only access without accidental writes

A reporting service often needs only selected tables or views. Start at the smallest practical scope:

CREATE USER 'orders_report'@'10.20.30.60'
  IDENTIFIED BY 'replace-with-a-reporting-secret';

GRANT SELECT
ON orders.order_summary
TO 'orders_report'@'10.20.30.60';

If a report needs many stable objects, a role can reduce repetitive administration. MariaDB roles group privileges and can be assigned to multiple users:

CREATE ROLE orders_readonly;

GRANT SELECT
ON orders.*
TO orders_readonly;

GRANT orders_readonly
TO 'orders_report'@'10.20.30.60';

SET DEFAULT ROLE orders_readonly
FOR 'orders_report'@'10.20.30.60';

Setting a default role matters because an assigned role may not be active automatically in every account configuration. A session can inspect its role state:

SELECT CURRENT_ROLE();

An administrator can inspect both direct and role-derived grants:

SHOW GRANTS FOR 'orders_report'@'10.20.30.60';
SHOW GRANTS FOR orders_readonly;

Roles are useful for stable job functions, but they are not a reason to create a universal role. A role with broad privileges assigned to many users increases the impact of one mistaken change.

Create operational accounts from tool requirements

Operational accounts require special care because many examples online grant global privileges without explaining why.

Backup identity

For MariaDB Backup, create a dedicated account and derive its privileges from the documentation for the server and backup-tool version. A commonly documented current baseline includes RELOAD, PROCESS, LOCK TABLES, and BINLOG MONITOR:

CREATE USER 'mariadb_backup'@'10.20.30.70'
  IDENTIFIED BY 'replace-with-a-backup-secret';

GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR
ON *.*
TO 'mariadb_backup'@'10.20.30.70';

Do not treat that example as universal. Extra backup options, encrypted data, history recording, or different releases can require additional permissions. MariaDB Backup should also be compatible with the server release. Run a real backup and restore validation in a non-production environment; a successful command exit is not proof that the recovery objective is met.

For mariadb-dump, permissions depend on what is dumped and which options are used. Inspect the tool documentation and grant only the required database and metadata access.

Monitoring identity

A simple monitoring probe may need no table privilege at all if it only connects and runs SELECT 1. A deeper collector might require status visibility or access to selected Performance Schema objects. Begin with the collector vendor's documented minimum and verify it against your MariaDB version.

For a local custom check that reads selected status variables, test whether an unprivileged authenticated account already provides what is needed before granting a global administrative privilege:

CREATE USER 'mariadb_monitor'@'10.20.30.80'
  IDENTIFIED BY 'replace-with-a-monitoring-secret';

Then connect as that account and run only the intended checks. Grant additional visibility one privilege at a time. Do not use a full administrator account in a metrics exporter merely because setup is easier.

Human administration

Give each administrator a named account. Avoid a shared password because it defeats individual revocation and audit attribution. Administrative access should use a controlled source, strong authentication appropriate to the release, secure transport, and an approved elevation process. Daily read-only diagnostics and emergency destructive access can be separate roles.

Add resource limits where they reduce risk

CREATE USER and ALTER USER can attach account resource limits. Supported options include limits for queries, updates, connections per hour, simultaneous user connections, and statement execution time. For example:

ALTER USER 'orders_report'@'10.20.30.60'
  WITH MAX_USER_CONNECTIONS 5
       MAX_STATEMENT_TIME 30;

Limits can contain a runaway report or connection storm, but choose values from measured workload. A limit below the application's connection-pool size creates an outage. A statement-time limit can cancel legitimate batch reports. Roll out limits in staging, monitor rejections, and document the rollback:

ALTER USER 'orders_report'@'10.20.30.60'
  WITH MAX_USER_CONNECTIONS 0
       MAX_STATEMENT_TIME 0;

In these contexts, zero generally removes the corresponding limit. Confirm semantics on the deployed release before relying on them.

Verify access with positive and negative tests

SHOW GRANTS proves what MariaDB stores, but a connection test proves account matching and effective behavior. From the authorized application host:

mariadb --host=10.20.30.10 
  --user=orders_api --password 
  --database=orders

Inside the session:

SELECT USER(), CURRENT_USER(), CURRENT_ROLE();
SELECT DATABASE();
SELECT 1;

Test an allowed operation using disposable test data in a staging database or a transaction you can safely roll back:

START TRANSACTION;
INSERT INTO access_test(note) VALUES ('least privilege test');
ROLLBACK;

Do not run a write test against an unknown production table. Prepare a controlled validation object or use staging.

Negative tests are equally important. The runtime account should fail to create a table:

CREATE TABLE should_not_exist (id BIGINT PRIMARY KEY);

The reporting account should fail to write:

UPDATE orders.order_summary
SET status = 'changed'
WHERE 1 = 0;

Even though the predicate changes no rows, MariaDB still evaluates authorization for the statement. Record the expected denial without inventing a terminal output. Also test connections from an unauthorized source host where safe. A complete acceptance checklist verifies permitted work succeeds and prohibited work fails.

Troubleshoot common MariaDB grant errors

Access denied for user

First separate network reachability from authentication. If MariaDB returns Access denied, the client reached the server. Check the exact account rather than opening the firewall:

SELECT User, Host, plugin, account_locked, password_expired
FROM mysql.user
WHERE User = 'orders_api';

Confirm the source address MariaDB sees, look for a more specific competing account, and verify the authentication plugin. Avoid immediately resetting every account with the same username.

The account connects but SQL is denied

Run:

SELECT USER(), CURRENT_USER(), CURRENT_ROLE();
SHOW GRANTS;

Check object scope carefully. orders.* does not grant access to a similarly named database, and a table-level grant does not cover new tables. A role may be assigned but inactive. The application may also be connecting with a different account than its configuration suggests because a pool retained old sessions.

No database selected or unknown database

No database selected is not a privilege problem. Specify a database in the connection string or run:

USE orders;

If the database is reported as unknown, verify its spelling and server environment. Do not create a new empty production database merely to silence the error.

GRANT appears to succeed but the application still fails

Check whether the application reused an existing connection, whether it needs a routine or view privilege not included in the original model, and whether the query accesses another schema. Capture the exact failing statement class without logging sensitive values. Compare it with SHOW GRANTS and CURRENT_USER().

A role was granted but privileges are unavailable

Inspect CURRENT_ROLE(). Activate the role for the session when testing:

SET ROLE orders_readonly;

For service accounts, configure the intended default role rather than depending on application code to activate it after every connection.

The wrong account was altered

Always write the full 'user'@'host' identity in account-management statements. These commands affect different accounts:

ALTER USER 'orders_api'@'localhost' ACCOUNT LOCK;
ALTER USER 'orders_api'@'10.20.30.25' ACCOUNT LOCK;

Before a sensitive change, use SHOW CREATE USER and SHOW GRANTS for the exact identity.

Rotate credentials without losing control

Credential rotation is an application change and a database change. Coordinate both sides:

1. Confirm which exact account and clients use the credential.
2. Prepare the new secret in the approved secret manager.
3. Change the account password with the supported account-management syntax.
4. Restart or reload clients so connection pools stop using the old secret.
5. Verify new connections and application behavior.
6. Remove any temporary compatibility credential.

For a controlled change:

ALTER USER 'orders_api'@'10.20.30.25'
  IDENTIFIED BY 'new-secret-from-approved-channel';

Depending on release and authentication setup, SET PASSWORD may also be appropriate. Prefer the documented method for the account's authentication plugin. Never expose the replacement secret in a ticket transcript.

Where zero-downtime rotation is required, a safer pattern may be a second temporary account with identical narrow grants: deploy clients to the new identity, verify old sessions drain, then lock and remove the old identity. This avoids a moment when half the application fleet has the wrong password, but it requires disciplined cleanup.

Revoke access and decommission accounts safely

Revocation should be staged when ownership is unclear. First lock the account:

ALTER USER 'orders_report'@'10.20.30.60' ACCOUNT LOCK;

Monitor for failed dependencies during an agreed observation window. If none appear, inspect and revoke role assignments or direct grants:

REVOKE orders_readonly
FROM 'orders_report'@'10.20.30.60';

Then remove the account:

DROP USER 'orders_report'@'10.20.30.60';

DROP USER is destructive: clients using that identity can no longer authenticate. Confirm the exact host-qualified account, application ownership, backup jobs, scheduled tasks, and connection pools before execution. Removing an account does not necessarily terminate every already established session immediately; handle live sessions through an approved operational procedure when immediate revocation is required.

Detect privilege drift

Access tends to expand during incidents and deployments. Build a periodic review that compares actual grants with a declared baseline. At minimum:

SELECT User, Host, plugin, account_locked, password_expired
FROM mysql.user
ORDER BY User, Host;

For every non-system account, collect:

SHOW CREATE USER 'account_name'@'account_host';
SHOW GRANTS FOR 'account_name'@'account_host';

Review for:

• Wildcard hosts introduced without approval.
• Direct grants that bypass the intended role model.
• ALL PRIVILEGES or WITH GRANT OPTION on service accounts.
• Dormant migration, vendor, and former employee accounts.
• Multiple host variants with inconsistent credentials.
• Password-expired or locked states that do not match policy.
• Operational accounts whose privileges no longer match tool versions.
• Database grants left behind after an application is retired.

Protect audit output because account definitions and infrastructure names are sensitive. A companion article in this series covers automated privilege auditing and security-event collection in greater depth.

Production best practices

Use these rules as a release gate:

• Create one identity per workload and environment; never share production credentials with development.
• Restrict both host and object scope.
• Keep DDL privileges out of runtime accounts.
• Use roles for stable job functions and verify default-role activation.
• Grant operational privileges from the exact tool and server documentation.
• Store secrets outside code and rotate them through a tested process.
• Test allowed and denied actions from the real client path.
• Record SHOW CREATE USER and SHOW GRANTS before and after changes.
• Prefer account locking as a reversible first step in decommissioning.
• Review grant drift regularly and remove temporary access promptly.
• Combine database authorization with firewall restrictions, TLS, logging, and patch management.

Least privilege is not the smallest possible grant list at any cost. It is the smallest well-understood set that lets a workload operate reliably, with a documented method to prove and review that claim.

FAQ

Does CREATE USER give a MariaDB account access to databases?

No. It creates the account but does not grant application-object privileges. Use a separate GRANT statement for the required database, table, routine, or server scope.

Should an application use GRANT ALL PRIVILEGES?

Normally no. Runtime applications usually need a limited DML set such as SELECT, INSERT, UPDATE, and DELETE. Put schema changes in a separate migration identity.

What is the difference between USER() and CURRENT_USER()?

USER() shows the client-supplied identity and observed connection host. CURRENT_USER() shows the MariaDB account that actually matched and supplies privileges.

Why does a MariaDB role not work after login?

The role may be assigned but not active. Check CURRENT_ROLE(), test SET ROLE, and configure the correct default role for service accounts.

Is 'user'@'%' safe for production?

It is broader than most workloads require. Prefer an exact client host or controlled subnet, supported by firewall rules and encrypted transport.

Do I need FLUSH PRIVILEGES after CREATE USER or GRANT?

No. Account-management statements apply their changes through MariaDB's supported privilege system. Manual flushing is associated with direct grant-table edits, which should be avoided.

Which privileges does a MariaDB backup user need?

It depends on the backup tool, options, and MariaDB release. Follow the matching official documentation and prove the configuration with a restore test.

How should I remove an uncertain legacy account?

Identify its owner and dependencies, capture its grants, lock it during an observation window, and drop it only after confirming nothing legitimate still depends on it.

Conclusion

Well-designed MariaDB users and grants turn authorization into an explicit, testable production control. Separate runtime, migration, reporting, backup, monitoring, and human identities; constrain each by host, object, and action; then verify both successful and rejected operations from the real connection path.

The work does not end after the initial GRANT. Rotate credentials, review roles, compare actual access with a declared baseline, and retire dormant accounts through a reversible process. That routine discipline prevents temporary permissions from becoming permanent exposure.

Suggested Internal Links

• Secure a New MariaDB Server: Production Hardening Guide
• Configure MariaDB Remote Access Without Exposing It
• Configure TLS Encryption for MariaDB Client Connections
• MariaDB Authentication Plugins Explained for Linux Admins
• Audit MariaDB Users, Privileges, and Security Events
• Back Up MariaDB with MariaDB Backup and Restore It

Suggested External Sources

• MariaDB CREATE USER documentation
• MariaDB GRANT documentation
• MariaDB SHOW PRIVILEGES documentation
• MariaDB SET PASSWORD documentation
• MariaDB Backup overview
• MariaDB Performance Schema accounts table