How to create a read-only user in MySQL

Before connecting your MySQL database to any external tool, create a dedicated read-only user. This limits the blast radius if credentials are ever leaked and prevents accidental writes from queries that go wrong.

Step 1: Create the user

MySQL 8.0

CREATE USER 'byesql_reader'@'%' IDENTIFIED BY 'choose-a-strong-password';

The '%' wildcard allows connections from any host. If your tool always connects from a specific IP range (e.g. a fixed egress IP), restrict it:

CREATE USER 'byesql_reader'@'203.0.113.0/24' IDENTIFIED BY 'choose-a-strong-password';

MySQL 5.7

The syntax is the same, but MySQL 5.7 uses mysql_native_password by default:

CREATE USER 'byesql_reader'@'%' IDENTIFIED WITH mysql_native_password BY 'choose-a-strong-password';

Step 2: Grant SELECT on the database

GRANT SELECT ON your_database_name.* TO 'byesql_reader'@'%';

This grants SELECT on all tables in your_database_name, now and in the future. Unlike PostgreSQL, MySQL applies ON database.* grants to tables created after the grant was given — no additional default privilege configuration is needed.

Step 3: Apply the privileges

FLUSH PRIVILEGES;

This is technically not required when using GRANT (MySQL reloads grant tables automatically), but it is a common convention to run it explicitly for clarity.

Grant on a specific database only

The your_database_name.* syntax scopes access to a single database. The user cannot access other databases on the same server.

To confirm:

SHOW GRANTS FOR 'byesql_reader'@'%';

You should see something like:

GRANT SELECT ON `your_database_name`.* TO `byesql_reader`@`%`

Limiting access to specific tables

If you need to hide certain tables — for example, a payment_methods table or a user_credentials table — grant SELECT per table instead of using .*:

GRANT SELECT ON your_database_name.orders TO 'byesql_reader'@'%';
GRANT SELECT ON your_database_name.products TO 'byesql_reader'@'%';
GRANT SELECT ON your_database_name.customers TO 'byesql_reader'@'%';
FLUSH PRIVILEGES;

The user will see only those three tables and nothing else.

Verify the setup

Connect as the read-only user and test:

-- As byesql_reader:
SELECT * FROM orders LIMIT 1;             -- should succeed
INSERT INTO orders (status) VALUES ('x'); -- should fail: INSERT command denied

Differences between MySQL 5.7 and 8.0

If you’re on MySQL 8.0 and connecting with an older client that doesn’t support caching_sha2_password, add:

ALTER USER 'byesql_reader'@'%' IDENTIFIED WITH mysql_native_password BY 'choose-a-strong-password';

Revoking access

REVOKE ALL PRIVILEGES ON your_database_name.* FROM 'byesql_reader'@'%';
DROP USER 'byesql_reader'@'%';
FLUSH PRIVILEGES;

A note on defense in depth

A read-only MySQL user is your database-level safeguard. ByeSQL adds a second layer through SAFE mode: all write operations (INSERT, UPDATE, DELETE, DROP, TRUNCATE, ALTER) are blocked at the application level before any SQL is sent to your database.

Both controls together mean that even if one layer has a gap, the other catches it. Use both.