How to Create a Read Only User in MySQL

To create a read only user in MySQL, use CREATE USER to add the user, then grant only SELECT privileges with GRANT SELECT ON database.* TO 'user'@'host'. This limits the user to read data without making changes.

Syntax

First, create the user with CREATE USER. Then, grant read-only access using GRANT SELECT. Finally, apply changes with FLUSH PRIVILEGES.

• CREATE USER 'username'@'host' IDENTIFIED BY 'password'; creates the user.
• GRANT SELECT ON database.* TO 'username'@'host'; gives read-only access to all tables in the database.
• FLUSH PRIVILEGES; reloads the privilege tables.

sql

CREATE USER 'readonlyuser'@'localhost' IDENTIFIED BY 'password123';
GRANT SELECT ON mydatabase.* TO 'readonlyuser'@'localhost';
FLUSH PRIVILEGES;

Example

This example creates a user named readonlyuser who can only read data from the mydatabase database. The user cannot insert, update, or delete data.

sql

CREATE USER 'readonlyuser'@'localhost' IDENTIFIED BY 'password123';
GRANT SELECT ON mydatabase.* TO 'readonlyuser'@'localhost';
FLUSH PRIVILEGES;

-- Test the user by logging in and running a SELECT query
-- mysql -u readonlyuser -p
-- Enter password123
-- USE mydatabase;
-- SELECT * FROM tablename LIMIT 5;

Output

Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

-- After login, SELECT queries return data
-- INSERT/UPDATE/DELETE queries fail with permission denied

Common Pitfalls

Common mistakes include:

• Granting more privileges than needed, like ALL PRIVILEGES, which allows data changes.
• Not specifying the correct host, causing login failures.
• Forgetting to run FLUSH PRIVILEGES after changes.
• Granting privileges on the wrong database or tables.

Always double-check the username, host, and database names.

sql

/* Wrong: Grants all privileges (not read-only) */
GRANT ALL PRIVILEGES ON mydatabase.* TO 'readonlyuser'@'localhost';

/* Right: Grants only SELECT privilege */
GRANT SELECT ON mydatabase.* TO 'readonlyuser'@'localhost';

Quick Reference

Command	Purpose
CREATE USER 'user'@'host' IDENTIFIED BY 'password';	Create a new user with password
GRANT SELECT ON database.* TO 'user'@'host';	Give read-only access to all tables in a database
FLUSH PRIVILEGES;	Reload privilege tables to apply changes
REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'host';	Remove all privileges from a user

Key Takeaways

Create a user with CREATE USER and set a password.

Grant only SELECT privilege to make the user read-only.

Always specify the correct host for the user.

Run FLUSH PRIVILEGES to apply changes immediately.

Avoid granting ALL PRIVILEGES if you want read-only access.