TL;DR - Create the user: CREATE USER 'readonly'@'%' IDENTIFIED BY 'strong_password'; - Grant read-only access: GRANT SELECT ON your_database.* TO 'readonly'@'%'; - Apply changes: FLUSH PRIVILEGES; - For all databases, use *.* instead of your_database.*. For specific tables, use your_database.table_name. - Always scope the host (%, localhost, or a specific IP) to the minimum required access.

Table of Contents

• Introduction
• Why You Need Read-Only MySQL Users
• Step 1: Create the MySQL User
• Step 2: Grant SELECT-Only Permissions
• Read-Only Access to a Specific Database
• Read-Only Access to All Databases
• Read-Only Access to Specific Tables
• Read-Only Access with SHOW VIEW
• Step 3: Verify Permissions with SHOW GRANTS
• How to Create a Read-Only User in phpMyAdmin
• How to Revoke Permissions or Drop the User
• Common Mistakes to Avoid
• FAQ
• Conclusion

How to Create a Read-Only MySQL User (Step by Step)

Giving everyone full access to a production database is one of those shortcuts that works until it does not. Sooner or later, someone runs an unintended UPDATE or DELETE, and you spend an afternoon restoring from backups. The fix is straightforward: create a read-only MySQL user that can only run SELECT queries. This guide walks through the exact SQL to do it, from creating the user account to granting and verifying permissions.

According to the 2024 Verizon Data Breach Investigations Report, 68% of breaches involve a non-malicious human element such as errors or misuse of privileges. Read-only users are one of the simplest ways to reduce that surface area. Whether you are handing off access to a QA engineer, a support team, a contractor reviewing data, or a reporting tool like Metabase or Grafana, the principle of least privilege says they should get SELECT and nothing more.

Why You Need Read-Only MySQL Users

Before jumping into SQL, here are the most common scenarios where a mysql read only user is the right choice:

• QA and testing. QA engineers need to inspect data but should never modify production rows. A read-only account lets them run any SELECT query without risk.
• Customer support. Support agents looking up order details or account info do not need INSERT, UPDATE, or DELETE access.
• Contractors and freelancers. External developers often need to understand the data model or debug queries. Read-only access gives them what they need without trusting them with write access.
• Reporting and BI tools. Tools like Metabase, Redash, Grafana, and custom dashboards only need to read data. Connecting them with a full-privilege user is an unnecessary risk.
• Compliance and audits. SOC 2, PCI DSS, and HIPAA all require least-privilege access controls. A 2023 IBM report found that the average cost of a data breach reached $4.45 million globally, and credential misuse was the most common attack vector.

A read-only user costs nothing to set up and takes about 60 seconds. There is no reason to skip it.

Step 1: Create the MySQL User

Connect to your MySQL server as a user with CREATE USER and GRANT privileges (typically root or an admin account):

mysql -u root -p

Then create the new user:

CREATE USER 'readonly'@'%' IDENTIFIED BY 'a_strong_password_here';

Breaking this down:

• 'readonly' — the username. Use something descriptive: readonly, reports, qa_user, support_read, etc.
• '%' — the host the user can connect from. % means any host. You should scope this down when possible (see below).
• IDENTIFIED BY — sets the password. Use a strong, unique password. MySQL 8.0+ uses caching_sha2_password by default, which is a significant improvement over the older mysql_native_password plugin.

Scoping the Host

The host portion of the user account controls where connections are allowed from. This is your first line of defense:

For production systems, avoid '%' when you know the connecting IP. If your reporting tool runs on 10.0.1.15, create the user as 'readonly'@'10.0.1.15'. This limits the blast radius if the password is compromised.

If you are connecting through DBEverywhere, you can whitelist the static IP listed on the IP whitelisting page and scope the user to that address specifically.

Step 2: Grant SELECT-Only Permissions

After creating the user, you need to grant select only access. The GRANT statement controls exactly what the user can do and on which databases or tables.

Read-Only Access to a Specific Database

This is the most common pattern. Grant SELECT on every table in a single database:

GRANT SELECT ON your_database.* TO 'readonly'@'%';
FLUSH PRIVILEGES;

The your_database.* syntax means "all tables in your_database." The user can run SELECT queries on any table in that database but cannot read any other database on the server.

FLUSH PRIVILEGES reloads the grant tables so the changes take effect immediately. Strictly speaking, MySQL applies GRANT statements immediately when using the GRANT command (as opposed to directly modifying grant tables), but running FLUSH PRIVILEGES is a safe habit that costs nothing.

Read-Only Access to All Databases

If the user needs to read across multiple databases — common for BI tools or monitoring:

GRANT SELECT ON *.* TO 'readonly'@'%';
FLUSH PRIVILEGES;

The *.* syntax means "all tables in all databases." Use this cautiously. It includes system databases like mysql, information_schema, performance_schema, and sys. The user will be able to see the user accounts table (mysql.user), password hashes, and other sensitive metadata. For most use cases, granting on specific databases is safer.

Read-Only Access to Specific Tables

When even database-level access is too broad — for example, a support user who should see the orders and customers tables but not the payments or api_keys tables:

GRANT SELECT ON your_database.orders TO 'readonly'@'%';
GRANT SELECT ON your_database.customers TO 'readonly'@'%';
FLUSH PRIVILEGES;

Run one GRANT statement per table. The user will only see the tables you explicitly grant access to. If they try to query payments, they get an error:

ERROR 1142 (42000): SELECT command denied to user 'readonly'@'...' for table 'payments'

Table-level grants are the strictest form of mysql user permissions and the best choice for external contractors or any user who should see a subset of your data.

Read-Only Access with SHOW VIEW

If your database uses views and the read-only user needs to inspect view definitions (not just query them), add the SHOW VIEW privilege:

GRANT SELECT, SHOW VIEW ON your_database.* TO 'readonly'@'%';
FLUSH PRIVILEGES;

Without SHOW VIEW, the user can query the view (since SELECT is sufficient for that) but cannot run SHOW CREATE VIEW to see how it is defined. Add this only if the user needs to understand the view's logic, such as a developer debugging a query.

Step 3: Verify Permissions with SHOW GRANTS

Always verify. Run SHOW GRANTS for the user to confirm that only the intended privileges were applied:

SHOW GRANTS FOR 'readonly'@'%';

Expected output for a single-database grant:

+----------------------------------------------------------+
| Grants for readonly@%                                    |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'readonly'@'%'                     |
| GRANT SELECT ON `your_database`.* TO 'readonly'@'%'      |
+----------------------------------------------------------+

The GRANT USAGE ON *.* line always appears — it means "this user exists but has no global privileges." The second line shows the SELECT grant on the specific database.

If you see anything beyond SELECT — like INSERT, UPDATE, DELETE, or ALL PRIVILEGES — revoke the extra permissions immediately (see the section below).

Test the user by logging in as them:

mysql -u readonly -p

Then try a write operation to confirm it fails:

USE your_database;
INSERT INTO some_table (id) VALUES (1);
-- ERROR 1142 (42000): INSERT command denied to user 'readonly'@'...'

This error is exactly what you want to see.

How to Create a Read-Only User in phpMyAdmin

If you prefer a GUI over writing SQL, phpMyAdmin provides a User Accounts panel for managing mysql user permissions visually:

1. Log in to phpMyAdmin as a user with admin privileges (typically root).
2. Click the User accounts tab in the top navigation bar.
3. Click Add user account near the bottom of the page.
4. Fill in the form:
5. User name: readonly (or your preferred name)
6. Host name: select "Any host" for %, or "Use text field" to enter a specific IP
7. Password: enter and confirm a strong password
8. Do not check any boxes under "Global privileges." Leave all privileges unchecked.
9. Click Go to create the user.
10. Now find the new user in the list and click Edit privileges.
11. Click Database in the privilege editor.
12. Select the target database from the dropdown and click Go.
13. On the database-specific privileges page, check only SELECT under the Data section.
14. Click Go to save.

That is it. The user now has read-only access to the selected database through phpMyAdmin's interface. You can verify by clicking Edit privileges again and confirming that only SELECT is checked.

If you do not have phpMyAdmin installed locally, DBEverywhere gives you the same phpMyAdmin interface in your browser without any setup. Connect to your database server, navigate to the User accounts tab, and follow the same steps above. The free tier (5 sessions/month) is enough to set up user accounts.

How to Revoke Permissions or Drop the User

People leave projects, contractors finish their work, and reporting tools get decommissioned. When a read-only user is no longer needed, remove the access.

Revoke specific privileges without deleting the user:

REVOKE SELECT ON your_database.* FROM 'readonly'@'%';
FLUSH PRIVILEGES;

Remove the user entirely:

DROP USER 'readonly'@'%';

DROP USER removes the account and all associated privileges in one step. No FLUSH PRIVILEGES needed.

A 2024 survey by Dimensional Research found that 74% of organizations have experienced a breach tied to excessive or unused access privileges. Cleaning up stale accounts is as important as creating them correctly.

Common Mistakes to Avoid

1. Granting ALL PRIVILEGES and then trying to revoke specific ones. This sounds logical but is fragile. If you run GRANT ALL PRIVILEGES ON db.* TO 'readonly'@'%' and then REVOKE INSERT, UPDATE, DELETE ON db.* FROM 'readonly'@'%', you still leave dozens of other privileges granted — CREATE, DROP, ALTER, INDEX, REFERENCES, CREATE VIEW, and more. Always start from zero and grant only SELECT.

2. Forgetting the host portion. MySQL treats 'readonly'@'localhost' and 'readonly'@'%' as two completely separate accounts. If you create the user with '%' but grant privileges to 'localhost', the remote user has no privileges. Match the host in both the CREATE USER and GRANT statements.

3. Using weak passwords because "it is read-only." A read-only user can still exfiltrate every row in your database. The 2025 OWASP Top 10 continues to list broken access control as the number one web application security risk. Treat read-only credentials with the same care as any other database password.

4. Not testing the account. Run SHOW GRANTS and then log in as the user and attempt an INSERT. It takes 30 seconds to verify and saves you from discovering the mistake in production.

FAQ

Can a read-only MySQL user see all databases on the server?

It depends on the scope of the GRANT. If you grant SELECT ON *.*, the user can see and query all databases including system databases like mysql and performance_schema. If you grant SELECT ON specific_database.*, the user can only see that database. With table-level grants, they can only see the granted tables. The SHOW DATABASES output respects grant scope — users only see databases where they have at least one privilege.

Does a read-only user consume a MySQL connection slot?

Yes. Every connected user, regardless of their privilege level, uses one connection from the max_connections pool. The default max_connections value is 151 in MySQL 8.0. For reporting tools that keep persistent connections open, consider setting a MAX_USER_CONNECTIONS limit when creating the user: ALTER USER 'readonly'@'%' WITH MAX_USER_CONNECTIONS 5; This prevents the read-only user from exhausting the connection pool.

How do I create a read-only user on Amazon RDS or other managed databases?

The SQL syntax is identical. Connect to your RDS instance as the master user (the admin account you created with the RDS instance) and run the same CREATE USER and GRANT SELECT statements. The only difference is that RDS does not give you SUPER privilege, so you cannot grant privileges you do not have. The master user has sufficient privileges to create users and grant SELECT. The same applies to DigitalOcean Managed Databases, Google Cloud SQL, Azure Database for MySQL, and PlanetScale. If your managed database provider restricts direct MySQL connections, DBEverywhere can connect from its static IP — just add the IP to your database's allow list.

What is the difference between SELECT and USAGE privileges?

SELECT allows the user to read data from tables. USAGE means "no privileges" — it is a placeholder that MySQL displays when a user exists but has no global-level grants. Every user has GRANT USAGE ON *.* by default. It does not grant any access to data. Think of it as the "this account exists" marker, not an actual permission.

Can I create a read-only user that can also run SHOW PROCESSLIST?

Yes. SHOW PROCESSLIST requires the PROCESS privilege. Grant it alongside SELECT:

GRANT SELECT, PROCESS ON *.* TO 'readonly'@'%';
FLUSH PRIVILEGES;

Note that PROCESS is a global privilege (it applies to *.*, not a specific database), so you must grant it at the global level even if the user only has SELECT on a single database. Be aware that SHOW PROCESSLIST exposes all running queries from all users, which may include queries with sensitive data in WHERE clauses.

Conclusion

Creating a read-only MySQL user is one of the fastest security wins available: two SQL statements, under a minute, and you have eliminated the risk of accidental writes from anyone who does not need write access. Start with CREATE USER, scope the host to the minimum required, grant only SELECT on the specific databases or tables needed, verify with SHOW GRANTS, and test by logging in as the user.

For QA engineers, support teams, contractors, and reporting tools, read-only access is not a limitation — it is the correct level of access. The principle of least privilege exists for a reason, and it is one of the cheapest controls you can implement.

If you need to run these commands quickly and do not have a MySQL client installed, DBEverywhere gives you a phpMyAdmin session in your browser in seconds — no setup, no Docker, no SSH tunnel required.

Create a read-only user from any browser with DBEverywhere