Mysql read only User

From Kolmisoft Wiki
Revision as of 14:49, 28 May 2014 by Mindaugas (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigationJump to search

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.