How to Create Read Only MySQL User

MySQL is a popular database used by millions of websites, online stores and blogs all over the world. While working in a multi-user environment, database administrators need to often create read only users for MySQL database. This is commonly required in data analytics and reporting systems, business intelligence scenarios and also in cases where an app/service needs to be able to access your data. In this article, we will learn how to create a read only MySQL user.

What is a Read Only MySQL User

MySQL database allows you to create different types of users, with different levels of access and permissions. A read only MySQL user is able to read or view the MySQL data but not add, remove or modify data in your database. This is a very secure way to allow third-party or new users to access your system, protecting it from unauthorized changes.

MySQL allows you to easily control the access for a read only user. You can give read only access to one or more databases, without giving complete access. In this case, the user will not be able to even know that other databases exist in your system. You may even give read only access to one or more tables in a single database. In this case, the user will not be able to view other tables in your database. Please note, it is very easy to check if a user is a read only user, or to completely revoke read only permissions.

How to Create Read Only MySQL User

There are several ways to provide read only access to MySQL user. We will cover them one by one.

1. Log into MySQL Database as admin/root user

Only root and admin users have the permission to create new users in MySQL. So you will need to log in as one of them. Run the following command in terminal(Linux) or command prompt(Windows) to log in as root user. You will be prompted for password.

mysql -u root -p

2. Create New User

Run the following command to create a new user.

mysql> CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'password';

In the above command, please replace readonly_user and password with username and passwords of your choice. We have mentioned % in the above command to allow this user to be able to connect from any IP location. If you want to restrict this user to be able to connect from only specific IP address, then replace % with the IP address of your choice.

In the above query, please remember to mention username and password within quotes.

Please note, this new user will not have any permission. In other words, this user will be able to log into MySQL but not access any data. We will grant it privileges in the next step.

3. Grant Read-Only Privileges

MySQL allows you to grant privileges using GRANT command. You can assign read only privileges using GRANT SELECT command. In MySQL, you can grant read only privileges to all databases, a single database, or even a single table. Depending on your requirement, run any of the following command.

For All Databases

GRANT SELECT on *.* to 'readonly_user'@'%';

The above query will allow the new user to run SELECT queries on all databases running on your server. We have mentioned *.* to specify all databases and their tables.

For All Tables in One Database

If you want to give read only access to all tables of just 1 database, run the following query.

GRANT SELECT on database_name.* to 'readonly_user'@'%';

In the above query, replace database_name with the name of the database for which you want to give access. If you want to give access to multiple databases, just run the above query with separate database names.

GRANT SELECT on database1_name.* to 'readonly_user'@'%';
GRANT SELECT on database2_name.* to 'readonly_user'@'%';
GRANT SELECT on database3_name.* to 'readonly_user'@'%';

For One Table Only

If you want to give read only access to just one table, then run the following query. Replace database_name and table_name with names of database and table respectively.

GRANT SELECT on database_name.table_name to 'readonly_user'@'%';

For Specific Rows Only

If you want to allow users to read only specific rows in a table, then you need to create an SQL view that will fetch only those rows and then GRANT SHOW VIEW command for that database.

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;

Here is an example query to create a view.

CREATE VIEW sales_view AS
SELECT *
FROM sales_data
WHERE amount>200;

The above query will create a view named sales_view with rows from sales_data table where amount>200. Next, run GRANT SHOW VIEW command to allow user to access this view.

GRANT SHOW VIEW on database_name.* to 'readonly_user'@'%';

Please Note: GRANT SELECT query only allows users to run SELECT queries on the database tables. It does not allow them to query the SQL VIEWS related to them. To provide such access, you need to include SHOW VIEW in GRANT command.

GRANT SELECT, SHOW VIEW on database_name.* to 'readonly_user'@'%';

4. Apply Changes

You need to run the FLUSH PRIVILEGES query to apply changes.

FLUSH PRIVILEGES;

5. Exit MySQL

Run the following command to exit MySQL console.

exit

6. Test User

Log into MySQL as the new read only user to see if it is working properly.

mysql -u readonly_user -p

You will be prompted for password. Enter the password for this user to proceed.

Once you have logged in, run the following query to see which database you can access.

SHOW DATABASES;

The output will only list those databases where this user has access and not all databases. Thereafter, you can start running queries to read data, as per your requirement. If the output table does not list any databases, it means you still don’t have access to any database in system.

How to Check if MySQL User is Read Only

To check if a user has read only access, you need to first log into MySQL database as root or admin user. Then run the following SHOW GRANTS command for the said user.

SHOW GRANTS FOR 'username'@'host';

If the user is a read only user, then output of above query will say ‘GRANT SELECT ON …’. If the user has other privileges such as DELETE, INSERT, UPDATE, etc. then they will also be shown additionally.

How to Revoke Read Only Access for MySQL user

If you want to revoke read only permission for a given user then you need to use REVOKE SELECT ON query. You need to modify the query depending on your requirement. Here are some common use cases.

Here is the query to revoke read only access on all databases and their tables.

REVOKE SELECT ON *.* FROM 'username'@'hostname';

If you want to revoke read only access to only a single database, then use the following query instead.

REVOKE SELECT ON database_name.* FROM 'username'@'hostname';

If you want to revoke read only access to only a single table and not the entire database, then replace * in above command with the table name.

REVOKE SELECT ON database_name.table_name FROM 'username'@'hostname';

Conclusion

In this article, we have learnt what a read only MySQL user is, why they are needed and how to create one. We have also covered various use cases to control read only access to databases, tables and even specific rows. Then we learnt how to check if a specific MySQL user is read only or not. Lastly, we have learnt how to completely revoke read only access in MySQL.

Also read:
How to Increase Max Connections in MySQL
How to Unpivot Table in MySQL
How to Alter Column from NULL to NOT NULL