Tuesday, January 15, 2013

Allowing remote users on MySQL

Recently I needed to add a user account that could access a MySQL database remotely. Listed below are the steps to create a user named bob who only has read only access.

Create the user account for bob if it doesn't already exist:
create user bob; 
Set the password test123* to bob's account:
set password for bob = password('test123*');
Grant privileges to bob to run the select statement from his home computer with the IP 210.22.2.123 for all tables on the schedule database:
grant select on schedule.* for 'bob'@'210.22.2.123' with grant option; 
The with grant option is required for remote access. This item tripped me up for several minutes.

Additionally, the mysqldump command issues the lock table command which is not granted with the above command.  Lock tables prevents rows from inserted or altered.  The --lock-tables=false flag prevents the lock tables command from being issue.

For further documentation check out the MySQL documentation on user accounts at http://dev.mysql.com/doc/refman/5.5/en//adding-users.html