MySQL: Creating Users and Granting Read Access (And the Caveats That'll Bite You)

Creating a read-only MySQL user sounds simple enough — and it mostly is. But there are enough subtle traps in MySQL's user/host model that it's worth spelling out the full picture, especially the parts that only show up when something breaks in production.

Creating the User

MySQL user accounts are identified by both a username and a host. These aren't the same account with different access rules — they're completely separate identities.

Scoped to a specific host:

CREATE USER 'readuser'@'10.0.1.50' IDENTIFIED BY 'strongpassword';

From any host (wildcard):

CREATE USER 'readuser'@'%' IDENTIFIED BY 'strongpassword';

Both simultaneously (common for service accounts that need both remote and flexible access):

CREATE USER 'readuser'@'10.0.1.50' IDENTIFIED BY 'strongpassword';
CREATE USER 'readuser'@'%' IDENTIFIED BY 'strongpassword';

These are two separate rows in mysql.user. Changing the password on one does not affect the other.

Granting Read Access

Grant SELECT on all tables in a target database:

GRANT SELECT ON mydb.* TO 'readuser'@'10.0.1.50';
GRANT SELECT ON mydb.* TO 'readuser'@'%';

FLUSH PRIVILEGES;

FLUSH PRIVILEGES is technically only required if you've manipulated the grant tables directly with raw INSERT or UPDATE statements. After a GRANT statement, MySQL reloads the in-memory privilege cache automatically. It's harmless to run anyway and a reasonable habit for clarity.

Verifying the Grants

SHOW GRANTS FOR 'readuser'@'10.0.1.50';
SHOW GRANTS FOR 'readuser'@'%';

You should see output like:

GRANT SELECT ON `mydb`.* TO `readuser`@`10.0.1.50`

Caveats Worth Knowing

% does not include localhost

This is probably the most common gotcha. The % wildcard matches any remote host but does not match connections made via the Unix domain socket using the hostname localhost. If your application connects locally (common in same-host setups), you may need a third account:

CREATE USER 'readuser'@'localhost' IDENTIFIED BY 'strongpassword';
GRANT SELECT ON mydb.* TO 'readuser'@'localhost';

Host matching is most-specific-wins

If both @'10.0.1.50' and @'%' accounts exist, a connection arriving from 10.0.1.50 will match the specific entry and ignore the wildcard. This matters if the two accounts have different passwords or privilege sets — make sure they're consistent if that could be an issue.

SELECT on mydb.* covers future tables automatically

Unlike PostgreSQL (which requires ALTER DEFAULT PRIVILEGES to extend access to future objects), MySQL's GRANT SELECT ON mydb.* applies to all tables in that database, including ones created after the grant. This is usually what you want for a read role, but be aware of it if the database is shared and tables with sensitive data might be added later.

Views and stored procedures are separate

GRANT SELECT ON mydb.* covers base tables and views, but does not grant the ability to call stored procedures or functions. If those are needed, add:

GRANT EXECUTE ON mydb.* TO 'readuser'@'%';

Column-level restrictions

If you need to exclude specific columns (say, a password_hash, ssn, or api_key column), you can get granular:

GRANT SELECT (id, name, email, created_at) ON mydb.users TO 'readuser'@'%';

This gets verbose at scale, but it's the right tool when a table has genuinely sensitive columns that shouldn't be exposed through a read role.

Auth plugin compatibility (MySQL 8+)

MySQL 8 defaults to caching_sha2_password, which older client libraries — particularly older PHP, Python mysqlclient, or legacy JDBC drivers — may not support. You'll see connection errors that aren't always obvious about the root cause. If you're dealing with older clients, specify the legacy plugin at creation time:

CREATE USER 'readuser'@'%' IDENTIFIED WITH mysql_native_password BY 'strongpassword';

Or alter an existing user:

ALTER USER 'readuser'@'%' IDENTIFIED WITH mysql_native_password BY 'strongpassword';

Enforce SSL for sensitive environments

For anything production-grade — and especially for PCI or SOC 2 scoped environments — require encrypted connections for the account:

ALTER USER 'readuser'@'%' REQUIRE SSL;

This prevents the credentials from being usable over a plaintext connection even if a misconfigured client or network path allows it.

Keep the grant surface minimal

SELECT alone is what you want for a true read role. Be careful not to drift into adding SHOW DATABASES, PROCESS, or REPLICATION CLIENT unless there's a specific reason — those have broader visibility implications that don't fit a pure read use case.

Quick Reference

-- Create user for specific host and wildcard
CREATE USER 'readuser'@'10.0.1.50' IDENTIFIED BY 'strongpassword';
CREATE USER 'readuser'@'%'         IDENTIFIED BY 'strongpassword';

-- Grant read on all tables in mydb
GRANT SELECT ON mydb.* TO 'readuser'@'10.0.1.50';
GRANT SELECT ON mydb.* TO 'readuser'@'%';

-- Optional: require SSL
ALTER USER 'readuser'@'%' REQUIRE SSL;

-- Verify
SHOW GRANTS FOR 'readuser'@'10.0.1.50';
SHOW GRANTS FOR 'readuser'@'%';

The user+host identity model in MySQL trips up even experienced engineers when they're moving fast. Keeping these patterns in a runbook or internal doc saves a lot of "why can't this thing connect" debugging.