Is there a way to GRANT ALL PRIVILEGES to the same user from multiple LAN addresses in a single command?

Question

I'm using the following command

GRANT ALL PRIVILEGES ON *.* TO 'user'@'ip' 
IDENTIFIED BY 'password' 
WITH GRANT OPTION;

To grant all privileges to a user. Is there a way I can make the ip a wildcard like 192.168.1.* so that I don't need to manually add each LAN ip I want give the user access to connect from?

Answer 1

Yes, use % in an address.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.1.%' 
IDENTIFIED BY 'password' 
WITH GRANT OPTION;

Or you can use less restrictive host name and allow user to connect from everywhere.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' 
IDENTIFIED BY 'password' 
WITH GRANT OPTION;

Answer 2

According to your requirement,

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'@'%' WITH GRANT OPTION;

Reload all the privileges.

FLUSH PRIVILEGES;

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