How to create a read-only MySQL user?

Last updated on September 14th, 2020 at 05:11 am

Sometimes you need to create a read-only MySQL user credentials for a new team mate to prevent accidental queries being fired into your database. Here’s how you can create a read-only MySQL user.

1. Log into MySQL as an admin

Run the MySQL command-line program by doing one of the following:

a. At a UNIX prompt, run the MySQL command-line program, and log in as an administrator by typing the following command

        mysql -u root -p

b. At a Windows command prompt, run the MySQL command-line program, and log in as an administrator by typing the following command from the MySQL root folder (e.g Program Files\MySQL\MySQL Server 5.5\bin):

       mysql -u root -p

c. Run any database shell that works with MySQL. E.g, Heidisql and MySQL Workbench.

Type the password for root account.

2. Create a new MySQL user

Copy the following command and paste them into a MySQL shell. Replace $database_name, $user, and $password below         with the values you will be using.

CREATE USER ‘$user‘@’127.0.0.1’ IDENTIFIED BY ‘$password‘;

3. Grant read-only permission to the MySQL user

Copy the following command and paste them into a MySQL shell.

GRANT SELECT, SHOW VIEW ON $database_name.* TO $user@’127.0.0.1′ IDENTIFIED BY ‘$password‘;

FLUSH PRIVILEGES;

If you want to use SSL connection, you can use the following instead

GRANT SELECT, SHOW VIEW ON $database_name.* TO $user@’127.0.0.1′ IDENTIFIED BY ‘$password‘ REQUIRE SSL;

FLUSH PRIVILEGES;

This creates a new read-only MySQL local user that can access your databases. If you want to create a remote user to access your database you can replace 127.0.0.1 with Remote IP. Also, you will need to enable remote access to your database. Please note, it is not a secure practice.

You can read more about how to create MySQL user here