Difference between revisions of "Mysql read only User"

From Kolmisoft Wiki
Jump to navigationJump to search
(Created page with '=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:''' *…')
 
Line 2: Line 2:


'''1. If we need to create a Mysql User account with read only permission which can ready all tables in specified Database:'''
'''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, then add your new user like so:
* 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'''';
   CREATE USER ''''username''''@'localhost' IDENTIFIED BY ''''password'''';
'''or'''
'''or'''

Revision as of 14:46, 28 May 2014

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. Naturally you will also want to substitute password with something a little 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.