MYSQL how to create read only database user with example

Database for an application is most important thing and for some security reasons we can not give database details to all the developers who are working on the application. So here we will learn hot to create read only database user in MYSQL. As we can not rely on all developers for database access.

So we will create a new database user with read only access and we can distrbute to the team if they need to debug the application for a particular data related issue.

If you can root access of the database then first thing you will need to logged in into the database using this command

mysql -u root -p

it will ask for database root user’s password. After exection of this command you will enter into the mysql prompt.

Then you can do the following things –

  1. If you want to create a user access to the database from any host then you will need to execute the following query:
grant select on database_name.* to 'read_only_user_name'@'%' identified by 'password';

2. If you want to give the access only from the host on which mysql server exists thaen run the following query:

grant select on database_name.* to 'read_only_user_name' identified by 'password';

3. And in the third case if you want to give the access for a particular IP or host name that can only access to the DB with correct credentials that run this query.

grant select on database_name.* to 'read_only_user_name'@'host_name or IP_address' identified by 'password';

Please do not forget to replace database_name, read_only_user_name, host_name or ip and password from your db name and desired user details.