Mysql read only User
From Kolmisoft Wiki
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.