How to create a read-only MySQL user?


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

Please share your thoughts as comments.

About Sreeram Sreenivasan

Sreeram Sreenivasan has worked with various Fortune 500 Companies in areas of Business Growth, Sales & Marketing Strategy. He’s the Founder of Ubiq BI, a cloud-based BI Platform for SMBs & Enterprises.