Mysql read only User

Create a Mysql user with read only permissions

1. If we need to create a Mysql User account with read only permission which can ready all tables in specified Database:

• Connect to your database as root:

  mysql -u root -p

Enter password if needed or press enter if there is no password used.

• Then add your new user like so

  CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

or

  CREATE USER 'username'@'%' IDENTIFIED BY 'password';

The % here means the user 'username' connecting from any host, you can place a network hostname here instead if you want to restrict access further.

Example (allows connection only from IP: 11.22.33.44):

CREATE USER 'username'@'11.22.33.44' IDENTIFIED BY 'password';

Naturally you will also want to substitute password with something a little bit stronger.

• Now run the following to grant the SELECT privilege to the new user on all databases:

  GRANT SELECT ON *.* to 'username'@'localhost';

Or if you want to restrict access to only one database:

  GRANT SELECT ON database.* to 'username'@'localhost';

2. Confirming that it worked:

  mysql -u username -pPassword

• If we try to use the Database to which SELECT for that user was not granted, we get something like this:

  mysql> use mor_test;
  ERROR 1044 (42000): Access denied for user 'username'@'localhost' to database 'database'

• If we try to update anything on the Database, to which SELECT is granted for that user, we get something like this:

  mysql> update users set password = 123 where id=4;
  ERROR 1142 (42000): UPDATE command denied to user 'username'@'localhost' for table 'users'

After all, type quit to leave the SQL shell or, if using a SQL client, close the SQL client.