DevOpsPadDownload

Create a read-only MySQL user for your desktop client

· Guides

If you browse production MySQL from a desktop client, connect with an account that can only read. A “read-only mode” in the client helps, but it is the database’s grants that decide what a statement can do. This guide creates a SELECT-only account scoped to one schema, limits where it can connect from, and shows how to check the result.

The SQL below works on MySQL 8.0 and later. Replace app, the host pattern and the password with your own values.

Why the client’s read-only mode is not enough

Many clients, DevOpsPad included, run queries inside a read-only transaction. In MySQL that means START TRANSACTION READ ONLY, which rejects INSERT, UPDATE and DELETE on regular tables for the life of that transaction.

It does not turn the session into a read-only account. Statements that cause an implicit commit, which includes most DDL, end the current transaction before they run. The transaction flag also does nothing if a different tool, a script or a colleague uses the same credentials. The only setting that covers every path is the account’s privileges.

Step 1: create the account

Create a dedicated user for interactive reads. Do not reuse the application’s account: it almost always has write privileges.

CREATE USER 'inspector'@'10.0.%'
  IDENTIFIED BY 'use-a-long-random-password'
  REQUIRE SSL;
  • '10.0.%' limits the hosts the account can connect from. If you connect through an SSH tunnel, MySQL sees the connection coming from the SSH server, so use that machine’s address. For a tunnel to 127.0.0.1 on the database host itself, use 'inspector'@'localhost' or 'inspector'@'127.0.0.1'; MySQL treats the two differently depending on whether the client uses a socket or TCP.
  • REQUIRE SSL refuses unencrypted connections. Drop it only if the traffic is already inside an SSH tunnel and the server has no TLS configured.

Step 2: grant only what reading needs

GRANT SELECT, SHOW VIEW ON app.* TO 'inspector'@'10.0.%';

SELECT lets the account read rows and also lets it see table definitions with SHOW CREATE TABLE, SHOW COLUMNS and SHOW INDEX. SHOW VIEW is needed for SHOW CREATE VIEW. The account will only see the app schema in SHOW DATABASES, which also keeps the browser tree short.

Things to leave out on purpose:

Privilege Why not
INSERT, UPDATE, DELETE Writes
CREATE, DROP, ALTER, INDEX Schema changes
PROCESS Shows other sessions’ statements, which can contain secrets
FILE Reads files on the database server
SUPER, any *_ADMIN Server administration
ON *.* grants Covers schemas you did not mean to expose, including mysql

If the account needs a second schema, add a second GRANT line for it rather than widening to *.*.

Step 3: put a ceiling on usage

A read-only account can still run an expensive query. Two cheap limits help:

ALTER USER 'inspector'@'10.0.%'
  WITH MAX_USER_CONNECTIONS 5;

For statement time, MySQL’s max_execution_time system variable applies to read-only SELECT statements. You can set it per session, or globally on a reporting replica. Pointing interactive reads at a replica, rather than the primary, is the strongest option if you have one.

Step 4: share access through a role (optional)

With more than one person, create a role once and grant it to each named account. Removing someone is then a single DROP USER.

CREATE ROLE 'app_read';
GRANT SELECT, SHOW VIEW ON app.* TO 'app_read';

CREATE USER 'alice'@'10.0.%' IDENTIFIED BY '...' REQUIRE SSL;
GRANT 'app_read' TO 'alice'@'10.0.%';
SET DEFAULT ROLE 'app_read' TO 'alice'@'10.0.%';

Without SET DEFAULT ROLE, the role is granted but not active when Alice connects, and her queries fail with an access error that looks like a missing grant.

Step 5: verify

Check what the account actually holds:

SHOW GRANTS FOR 'inspector'@'10.0.%';

The output should list USAGE ON *.* (which means “no global privileges”) and your SELECT, SHOW VIEW ON app.* line, nothing else. Then connect as the new user and try a write against a scratch table. You should get error 1142, “command denied to user”.

Doing this in DevOpsPad

Create the profile under New connection, choose MySQL, enter the inspector credentials and click Test connection. The password goes to the OS keychain, not to a config file. If the database is only reachable from a bastion, enable Connect through SSH tunnel, and remember that the host pattern in step 1 has to match the SSH server’s address.

DevOpsPad then adds its own layer on top. Every MySQL query runs in a server-side read-only transaction, and a preview stops at 500 rows or 8 MiB. Those limits protect the desktop app. Your grants protect the database. You can read more in MySQL read-only queries, connecting to MySQL and least-privilege accounts. The MySQL connector page lists what the client can and cannot do today.