Fix MariaDB Access Denied Errors Without Broad Grants

A MariaDB access denied message does not mean “grant more permissions.” During connection, MariaDB first identifies a matching 'user'@'host' account, applies its authentication plugin or plugin chain, validates the credential and any TLS requirements, and only then creates a session. After login, separate authorization checks decide whether the account can use a database, table, routine, or administrative statement.

The fastest-looking fixes often create the largest security problem: adding 'app'@'%', granting ALL ON *.*, disabling TLS verification, changing root from unix_socket to a reusable password, or editing privilege tables directly. These changes can leave the original account mismatch unresolved while exposing every database from a broader network.

This guide builds a deterministic diagnosis from the client command to the selected MariaDB account. It distinguishes transport and server identity, USER() from CURRENT_USER(), host specificity, DNS and skip_name_resolve, password and socket authentication, client plugin support, TLS account requirements, expired credentials, Kubernetes secrets, proxy source addresses, and post-login privileges. The final fix should be the narrowest account or grant change that restores the intended path.

Capture the complete error and connection context

Record the exact message, numeric error, SQLSTATE, timestamp, client library/version, destination, transport, account, application release, and secret version.

Typical connection failure:

ERROR 1045 (28000): Access denied for user 'app'@'client-host' (using password: YES)

The host text is MariaDB's view of the connection source. It may be a resolved hostname, an IP, localhost, proxy address, or Unix-socket context.

using password: YES means the client sent a password response. It does not prove the password was correct, nonempty, read from the intended source, or compatible with the account plugin. NO indicates that path did not send a password response; a passwordless plugin may still have been intended.

Post-login authorization errors differ:

ERROR 1044 (42000): Access denied for user ... to database ...
ERROR 1142 (42000): SELECT command denied to user ... for table ...

Those prove authentication succeeded. Do not reset the password when only a table grant is missing.

Client-side messages about connection refused, timeout, unknown host, missing socket, certificate verification, or unsupported authentication plugin belong to other layers. Preserve them verbatim instead of relabeling everything as 1045.

Verify that the client reaches the intended server

Configuration drift often sends the application to a replica, old cluster, localhost sidecar, or wrong port where the expected account does not exist.

Inspect client environment without printing secrets:

env | rg '^(MYSQL|MARIADB|DB)_(HOST|PORT|USER|DATABASE)='

Do not dump all environment variables; they may contain passwords and tokens.

Resolve the destination:

getent ahosts db.internal.example

Check TCP reachability without sending credentials:

nc -vz -w 3 db.internal.example 3306

nc syntax varies, and successful TCP reachability does not prove MariaDB identity or TLS correctness.

From a working privileged session on the target, record:

SELECT @@hostname,
       @@port,
       @@socket,
       @@server_id,
       VERSION(),
       @@version_comment;

Compare with proxy/backend routing, Kubernetes Service endpoints, DNS, and topology inventory. Do not create a missing account until the destination is confirmed.

Make transport explicit: Unix socket versus TCP

On Linux, this command often uses the local Unix socket:

mariadb --user=app

This normally forces TCP loopback:

mariadb --protocol=TCP --host=127.0.0.1 --port=3306 --user=app

This forces a particular socket:

mariadb --protocol=SOCKET 
  --socket=/run/mysqld/mysqld.sock 
  --user=app

localhost, 127.0.0.1, and a local hostname are not interchangeable. The client can choose different transports, and MariaDB can select different account rows or authentication plugins.

Before concluding a password works “locally,” repeat the exact production transport. A successful sudo mariadb test commonly authenticates operating-system root through unix_socket, while the application uses TCP and password authentication.

Inspect client option sources without revealing password values:

mariadb --print-defaults
my_print_defaults client mariadb

Option files, environment variables, command flags, and connector settings can override host, user, socket, TLS, and plugin directory. Avoid passwords on the command line because process listings and shell history may expose them.

Understand USER() and CURRENT_USER()

After any successful test connection, run:

SELECT USER(), CURRENT_USER(), @@external_user;

USER() reports the client-supplied username and connection host as seen by MariaDB.
CURRENT_USER() reports the actual MariaDB 'user'@'host' account selected for authentication and privilege checks.
@@external_user can reveal the external identity supplied by plugins such as unix_socket.

Example interpretation:

USER()         = app@10.20.4.17
CURRENT_USER() = app@10.20.%

This proves which account matched. If CURRENT_USER() is an anonymous account, a more specific anonymous host row may have taken precedence over the broad named account.

You cannot run these functions after a failed login. Use a separate authorized administrative session to inspect candidate accounts and server logs, then validate with a successful narrow test.

Understand MariaDB account matching

MariaDB accounts are pairs:

'username'@'host_pattern'

These are distinct:

'app'@'localhost'
'app'@'127.0.0.1'
'app'@'10.20.%'
'app'@'%'

When multiple rows match, MariaDB sorts accounts by host specificity and username rules and selects the first match. An exact host usually outranks a wildcard. Anonymous accounts have an empty username and can match login attempts in ways that surprise operators. A broad 'app'@'%' does not override every more-specific account.

Inventory candidates through the compatibility view:

SELECT User, Host, plugin
FROM mysql.user
WHERE User IN ('app', '')
ORDER BY User, Host;

On MariaDB 10.4+, mysql.user is a view over mysql.global_priv. Use supported account statements rather than editing either object directly.

Inspect each exact candidate:

SHOW CREATE USER 'app'@'10.20.%'G
SHOW GRANTS FOR 'app'@'10.20.%';

SHOW CREATE USER can reveal authentication and TLS policy. Treat output as sensitive and restrict who can inspect it.

Do not drop “duplicate” host rows until every source path and precedence case is tested. A local maintenance account and remote application account often intentionally differ.

Determine the source address MariaDB actually sees

The application may connect through:

NAT or Kubernetes SNAT.
MaxScale, ProxySQL, or a cloud database proxy.
Service mesh or sidecar.
SSH tunnel or bastion.
Load balancer with or without proxy protocol.
Local Unix socket.

MariaDB normally authenticates the TCP peer it sees, which may be the proxy rather than original client. The error host and USER() on a successful session are direct evidence.

Inspect server connections by source:

SELECT USER,
       HOST,
       COUNT(*) AS connections
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST
ORDER BY connections DESC;

If a proxy forwards original addresses through PROXY protocol, MariaDB must be configured to trust only the exact proxy networks. Accepting spoofable proxy headers from untrusted sources is a security vulnerability. Do not enable it merely to make host grants more convenient.

In Kubernetes, source IP can change with Service traffic policy, node routing, proxy mode, and topology. Prefer stable authenticated proxy identity or controlled network ranges over creating one account per ephemeral pod IP.

Diagnose skip_name_resolve and DNS behavior

Inspect:

SHOW GLOBAL VARIABLES LIKE 'skip_name_resolve';

When skip_name_resolve=ON, MariaDB does not resolve client IPs to hostnames. Grant-table host values must use IP addresses or localhost according to MariaDB documentation. An account such as:

'app'@'api.internal.example'

will not provide the intended remote hostname match in that mode.

skip_name_resolve is a non-dynamic startup setting. Changing it affects account resolution across the server and requires a restart. Prefer correcting accounts to deliberate IP/subnet or proxy identities after inventorying all hostname grants.

When name resolution is enabled, reverse and forward DNS consistency, latency, caching, and spoofing controls matter. Inspect the server's resolution, not only the client:

getent hosts 10.20.4.17
getent ahosts api.internal.example

Do not widen a host pattern because DNS is temporarily broken. Fix the naming path or use an approved stable address model.

Inspect the authentication plugin before resetting a password

Use:

SHOW CREATE USER 'app'@'10.20.%'G

MariaDB supports plugin-specific authentication, including:

mysql_native_password
unix_socket
ed25519
PAM
GSSAPI
named pipe on Windows
PARSEC from MariaDB 11.6
other installed plugins

From MariaDB 10.4, an account can have multiple authentication plugins in an ordered alternative chain. A client must support the server's selected exchange.

List active authentication plugins:

SELECT PLUGIN_NAME,
       PLUGIN_STATUS,
       PLUGIN_TYPE,
       PLUGIN_MATURITY
FROM information_schema.PLUGINS
WHERE PLUGIN_TYPE = 'AUTHENTICATION'
ORDER BY PLUGIN_NAME;

If the server plugin is missing or disabled, changing a password is not the root fix. If the connector lacks the required client plugin, upgrade/configure the supported connector or migrate the account through a tested rollout.

Do not downgrade authentication to an older plugin only to support an obsolete client without a security review. Connector upgrades are often safer.

Diagnose unix_socket authentication

The unix_socket plugin authenticates a local Unix-socket client using the operating-system identity obtained from the socket peer. It does not validate a normal database password.

Check the OS user and socket:

id
whoami
stat /run/mysqld/mysqld.sock

Test explicitly:

sudo -u app mariadb 
  --protocol=SOCKET 
  --socket=/run/mysqld/mysqld.sock 
  --user=app

Use only if the app OS account and MariaDB account are intentionally mapped. File permissions, container user namespaces, sudo policy, and mounted sockets affect the result.

MariaDB installation packages commonly configure 'root'@'localhost' to try unix_socket, and on MariaDB 10.4+ may retain a password plugin alternative with an initially invalid password. Therefore:

sudo mariadb

can succeed while:

mariadb --protocol=TCP --host=127.0.0.1 --user=root --password

fails. This is expected until the TCP/password method is deliberately configured.

Do not disable unix_socket globally or convert root merely for a script. Create a dedicated least-privilege automation account with the intended transport and secret management.

SET PASSWORD is ignored or inappropriate for plugins that do not store a MariaDB password, including unix_socket, named pipe, GSSAPI, and many PAM configurations. Fix the external identity or alter the account's plugin through a planned migration.

Validate password handling without exposing the secret

Common failures include:

Wrong secret version after rotation.
Hidden newline or whitespace.
Shell interpolation of special characters.
URL encoding in a connection URI.
Base64 encoded Kubernetes Secret decoded incorrectly.
An empty environment variable interpreted as no password.
Client option file overriding the intended user/host.
Old pooled connections surviving rotation.
Different secret on one application replica.

Do not print the password. Verify metadata such as secret resource version, byte length, and a cryptographic checksum only within an approved secure workflow. Even a checksum can become sensitive if the password is low entropy.

Use interactive prompting for a manual test:

mariadb --protocol=TCP 
  --host=db.internal.example 
  --port=3306 
  --user=app 
  --password

Avoid --password=secret on the command line.

For Kubernetes, inspect keys and metadata without decoding values into terminal history:

kubectl -n application get secret mariadb-app 
  -o jsonpath='{.metadata.resourceVersion}{"n"}{.data}'

This example still emits base64-encoded secret data and should be avoided in shared terminals/logs. Prefer listing only key names with a reviewed tool or JSON processor, and use the platform's secret-management workflow for value validation.

Coordinate password rotation:

Create or activate the new credential path.
Deploy clients that can use it.
Recycle pools deliberately.
Verify successful connections by release/source.
Remove the old path.
Test rollback and audit stale consumers.

An immediate password replacement can break long-lived jobs and disaster-recovery tooling that were not inventoried.

Diagnose expired passwords and client capability

Inspect account definition and expiration policy:

SHOW CREATE USER 'app'@'10.20.%'G

MariaDB can place a user with an expired password into a restricted mode where only password-changing actions are allowed, if the client advertises support. Batch clients may need:

mariadb --connect-expired-password 
  --host=db.internal.example 
  --user=app 
  --password

Do not disable password expiration globally to fix one unrotated service. Rotate the account through the normal secret process and update automation before expiry.

Different connectors implement expired-password and authentication-plugin capabilities differently. Record the exact driver and version from the failing application.

Verify TLS requirements on the account

An account can require:

TLS (REQUIRE SSL).
A valid client certificate (REQUIRE X509).
A specific issuer.
A specific certificate subject.
A permitted cipher requirement.

Inspect:

SHOW CREATE USER 'app'@'10.20.%'G
SHOW GLOBAL VARIABLES LIKE 'have_ssl';
SHOW GLOBAL VARIABLES LIKE 'ssl%';

On a successful test session:

SHOW SESSION STATUS LIKE 'Ssl_cipher';

An empty cipher indicates the session is not using TLS. Client command:

mariadb --host=db.internal.example 
  --user=app 
  --ssl-ca=/etc/db-certs/ca.pem 
  --ssl-verify-server-cert 
  --password

If mutual TLS is required, provide the approved client certificate and private key through protected files. Never disable server-certificate verification as a permanent workaround. Confirm SAN/hostname, CA chain, expiry, clock, permissions, and proxy TLS termination.

If the proxy terminates TLS and opens a non-TLS backend connection, a backend account with REQUIRE SSL sees the proxy-to-database leg, not the client-to-proxy leg. Encrypt both legs or design account policy explicitly.

Distinguish authentication from authorization

After login, establish the selected account:

SELECT USER(), CURRENT_USER(), DATABASE();
SHOW GRANTS FOR CURRENT_USER;

Test only the required operation:

SELECT 1;
USE application_db;
SELECT id FROM application_db.healthcheck LIMIT 1;

Do not use a broad data query as a connection test. It can fail for legitimate row/table permissions and confuse authentication diagnosis.

If the error is authorization, map application commands to privileges:

SELECT, INSERT, UPDATE, DELETE on specific schemas/tables.
EXECUTE on routines.
CREATE TEMPORARY TABLES only if needed.
DDL privileges only for a migration account.
Replication monitoring privileges only for monitoring.
Administrative privileges separate from application access.

Grant narrowly:

GRANT SELECT, INSERT, UPDATE
ON application_db.orders
TO 'app'@'10.20.%';

Verify:

SHOW GRANTS FOR 'app'@'10.20.%';

The example may still be broader or narrower than the real application needs. Use schema migrations and privilege-as-code where possible.

Do not issue:

GRANT ALL PRIVILEGES ON *.* TO 'app'@'%';

as troubleshooting. It combines global authorization and broad network identity, masking the faulty layer and creating a serious escalation path.

Create the correct account when it is truly missing

After confirming destination, transport, source identity, TLS policy, plugin, and least privileges, create an exact account:

CREATE USER 'orders_api'@'10.20.%'
  IDENTIFIED BY 'use-a-secret-manager-generated-value'
  REQUIRE SSL;

Do not put a real secret in SQL history. Use an approved secure provisioning mechanism and password-validation policy.

Grant only required objects:

GRANT SELECT, INSERT, UPDATE
ON orders_db.*
TO 'orders_api'@'10.20.%';

Schema-wide DML may be too broad; table- or routine-level grants can be better. Separate read, write, migration, monitoring, backup, and administration accounts.

Validate from the real application network and connector, then record USER(), CURRENT_USER(), TLS cipher, and a harmless authorized operation. Do not conclude success from an admin console on localhost.

Avoid direct privilege-table edits

On MariaDB 10.4+, account state lives in mysql.global_priv, while mysql.user is a compatibility view. Direct INSERT or UPDATE can create malformed plugin JSON/state, bypass validation, and behave differently across versions.

Use:

CREATE USER
ALTER USER
DROP USER
SET PASSWORD only for compatible password plugins
GRANT
REVOKE
SHOW CREATE USER
SHOW GRANTS

These statements update caches and validate syntax. FLUSH PRIVILEGES is generally unnecessary after supported account statements; it is relevant when privilege tables were edited directly, which production automation should avoid.

Inspect logs without creating a credential leak

Check MariaDB's error destination:

SHOW GLOBAL VARIABLES LIKE 'log_error';

On systemd systems:

journalctl -u mariadb --since '-30 minutes' --no-pager

Authentication plugins such as PAM may also write to system authentication logs. Correlate timestamps, user, source, plugin, and error without copying secrets into tickets.

Audit plugins can record connection failures where configured. Confirm scope, retention, encryption, and overhead before enabling new logging during an attack or outage.

Do not enable the general query log for authentication troubleshooting. It adds high volume and can expose SQL literals without showing a plaintext password validation answer.

Repeated access failures may be brute force or credential stuffing. Rate-limit at the network/proxy layer, alert security, and preserve source evidence rather than widening grants.

Use host-cache evidence for connection failures

Where Performance Schema host cache is available:

DESCRIBE performance_schema.host_cache;

SELECT *
FROM performance_schema.host_cache
ORDER BY SUM_CONNECT_ERRORS DESC
LIMIT 30;

It includes counts for categories of handshake and authentication-related failures by host. Column availability varies.

max_connect_errors blocks a host after repeated successive connection errors:

SHOW GLOBAL VARIABLES LIKE 'max_connect_errors';

Fix the client/TLS/network problem before unblocking. FLUSH HOSTS clears host-cache state broadly and can remove security evidence; use the version-supported targeted action where available or perform an approved broad flush only after capture.

Raising max_connect_errors indefinitely does not correct bad credentials and can reduce protection.

Troubleshoot application-only failures

If the CLI succeeds but the application fails, compare:

Destination host and port.
TCP versus socket.
Username including case.
Secret resource/version and encoding.
Database name in the connection string.
TLS mode, CA, hostname verification, client certificate.
Driver authentication plugin support.
Connection URI escaping.
Proxy routing and source identity.
Session initialization statements.
Pool recycling after credential rotation.

Test with the same connector build in a minimal program or application's diagnostic command, without logging secrets. CLI success proves the server/account path for one client, not driver compatibility.

Connection URLs are especially error-prone when passwords contain @, :, /, ?, #, or %. Use structured connector parameters rather than hand-building a URI, or apply correct percent encoding exactly once.

Troubleshoot Kubernetes and container failures

Inside the same application container:

getent hosts "$DB_HOST"
nc -vz -w 3 "$DB_HOST" "${DB_PORT:-3306}"

Do not print $DB_PASSWORD. Compare pod identity, namespace, network policy, Secret version, mounted-file update behavior, and rollout time.

Kubernetes Secret values are base64 encoded, not encrypted merely by that representation. RBAC and encryption at rest matter. Avoid decoding into terminal output or shell history.

A Secret update does not guarantee an application process reloads environment variables; environment variables are fixed at process start. Mounted secret files can update asynchronously, but libraries may cache credentials and pools may retain sessions. Plan a controlled rollout or reload.

Check whether a service mesh changes destination, TLS, or source IP. Validate MariaDB account matching after the actual proxy path.

Roll back an unsafe emergency change

If someone created a broad temporary account or grant, do not remove it until the intended narrow path is verified. Then:

Capture current account definitions and access logs.
Create/fix the narrow account.
Deploy clients and recycle pools.
Verify selected account and privileges.
Revoke broad privileges or lock/drop the emergency account.
Monitor access-denied and success metrics.

Revoke explicitly:

REVOKE ALL PRIVILEGES, GRANT OPTION
FROM 'app'@'%';

This may not remove the account itself and may interrupt legitimate clients. Confirm exact syntax/version and inventory before execution.

Lock an unused account during observation:

ALTER USER 'app'@'%' ACCOUNT LOCK;

Then drop only after a full workload and recovery-tool cycle:

DROP USER 'app'@'%';

These are destructive access changes. Keep rollback DDL and a tested administrative session.

Verify the fix end to end

From the actual application environment and exact connector:

Resolve the expected endpoint.
Connect through intended transport and TLS.
Query USER() and CURRENT_USER().
Confirm nonempty Ssl_cipher when TLS is required.
Run one harmless required operation.
Confirm a prohibited operation remains denied.
Recycle and retest pooled connections.
Test rolling deploy, secret rotation, restart, and failover.
Monitor errors by account/source without logging secrets.

Negative test example with a dedicated test account/environment: confirm a source outside the approved network cannot authenticate. Do not brute-force production or generate security alerts without coordination.

Success is not “admin can connect.” It is the intended service connecting to the intended server through the intended security path with only required capabilities.

Troubleshoot common misleading cases

The password is correct but access is denied

The selected host account may be different, the plugin may not use passwords, TLS may be required, the client may lack plugin support, or the destination may be wrong. Inspect the full path.

`'user'@'%'` exists but localhost login fails

A more-specific local or anonymous account can take precedence. Socket transport and unix_socket authentication may also be involved. Inspect all matching rows.

sudo mariadb works but mariadb -u root -p fails

The first likely uses unix_socket as OS root. The TCP/password alternative may have no valid password. This is normal on many MariaDB 10.4+ installations.

Access broke after enabling skip_name_resolve

Hostname-based remote accounts no longer match because MariaDB uses IP addresses without resolution. Create reviewed IP/subnet accounts before enabling the setting.

Access broke after password rotation

Some pods, jobs, proxies, or disaster-recovery scripts may still use the old secret, or pools may not have recycled. Compare secret versions and rollout state by source.

Login succeeds but USE database fails

Authentication is complete; the selected account lacks database authorization or the requested schema name is wrong. Inspect CURRENT_USER() and SHOW GRANTS.

TLS client works only with verification disabled

Fix CA trust, server certificate SAN, hostname, chain, expiry, or proxy configuration. Do not retain an insecure verification bypass.

SET PASSWORD succeeds but socket login behavior does not change

The account may authenticate via unix_socket or another external plugin that does not use MariaDB password storage. Inspect SHOW CREATE USER.

One proxy node works and another fails

Backend source identity, TLS certificates, plugin support, routing, or secrets differ. Compare proxy node configuration and MariaDB's observed host.

Monitoring and security controls

Monitor:

Authentication failure rate by sanitized account/source category.
Host-cache connection errors and blocked hosts.
Successful connection rate and selected account where audit policy permits.
Secret/certificate expiry and rollout completion.
TLS use and certificate validation failures.
Account creation, alteration, grants, revokes, and drops.
Broad host patterns and global privileges in periodic audits.
Unknown authentication plugins or disabled required plugins.
Application pool reconnect and access-denied storms.
Proxy routing and source identity changes.

Avoid using raw username, host, or SQL as unbounded monitoring labels. Authentication data is security-sensitive; apply retention and access control.

Periodically inventory:

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

Review anonymous accounts, wildcard hosts, administrative privileges, stale users, TLS requirements, password expiry, and service ownership. Use SHOW CREATE USER and SHOW GRANTS for authoritative details.

Best practices checklist

Preserve exact error code, SQLSTATE, host text, and timestamp.
Confirm destination server before changing accounts.
Reproduce the exact TCP/socket and proxy path.
Use USER() and CURRENT_USER() after a successful test.
Inspect every candidate 'user'@'host' row and anonymous account.
Check skip_name_resolve before relying on hostname grants.
Inspect authentication plugin before resetting a password.
Treat unix_socket as OS identity, not password auth.
Validate connector plugin support and TLS requirements.
Never print secrets or put passwords on command lines.
Separate authentication errors from object privilege errors.
Grant only required objects and actions.
Avoid direct edits to mysql.global_priv or mysql.user.
Coordinate secret rotation with pool recycling and rollback.
Verify the real application path and a negative permission test.

FAQ

What does using password: YES mean in MariaDB error 1045?

It means the client sent a password authentication response. It does not prove the password was correct, nonempty, read from the intended secret, or relevant to the account's plugin.

Why are USER() and CURRENT_USER() different?

USER() shows the client identity and observed source. CURRENT_USER() shows the account MariaDB selected, which controls authentication and privileges.

Why does localhost behave differently from 127.0.0.1?

localhost commonly uses a Unix socket, while 127.0.0.1 forces TCP. Transport, source identity, matched account, and authentication plugin can differ.

Does root need a password on MariaDB?

Not necessarily. Many Linux installations authenticate 'root'@'localhost' through the unix_socket plugin using OS root identity. Remote/TCP root access should not be assumed.

Should I create `'app'@'%'` to fix access denied?

No. First determine the real source and selected account. Use the narrowest stable host/proxy identity and least privileges; % can expose the account broadly.

Does SET PASSWORD work with unix_socket authentication?

No in the normal sense. unix_socket authenticates the OS peer and does not store/use a MariaDB password. Change the OS mapping or migrate the plugin deliberately.

Why can I connect but cannot select a table?

Authentication succeeded, but the selected account lacks object authorization. Check CURRENT_USER() and SHOW GRANTS, then grant only the required action.

Can skip_name_resolve break existing users?

Yes. Remote hostname-based account patterns stop matching because MariaDB no longer resolves client IPs. Convert accounts to reviewed IP/subnet identities before enabling it.

Conclusion

Fixing MariaDB access denied safely is an identity-resolution exercise. Confirm the exact server and transport, determine the source MariaDB sees, inspect competing account rows, and understand the selected authentication plugin before touching credentials. Socket, password, external identity, client plugin, and TLS failures need different corrections.

Once login succeeds, use USER() and CURRENT_USER() to prove the path and treat database/table denial as authorization, not authentication. Create or alter only the narrow account required, preserve TLS verification, grant the smallest object privileges, and coordinate secret rotation with connection pools and failover tooling. A successful fix restores the intended service while every broader path remains closed.