MySQL: creating a user that can connect from multiple hosts

Solution 1:

If you want to restrict to host and do not want to specify based on a subnet or wildcard using %, that's the only way to do it. More details are available in the MySQL documentation.

I am still trying to find ways to eliminate overhead when managing authentication to large MySQL installations and have yet to find a perfect solution.

Solution 2:

Let's start by making a new user called "chaminda" within the MySQL shell:

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

The first thing to do is to provide the user with necessary permission and here I have given all permission to the particular user.

GRANT ALL PRIVILEGES ON * . * TO 'chaminda'@'%';

Reload all the privileges.

FLUSH PRIVILEGES;

If you want to allow range of IPs to a particular user use as follows 10.1.1.%

GRANT ALL PRIVILEGES ON * . * TO 'chaminda'@'10.1.1.%';

Note: Here host Name = % and that means you can access this database server from any host. Granting all privileges to the user is a big risk and that's not a best practice. Further you can replace user 'chaminda' to 'bob'.