Connecting to mySQL from 2 different machines

Question

ALL,

Is it possible to modify the permissions of the MySQL DB or I will need to revoke it and grant it again?

My problem is as follows:

I'm running MySQL server on Gentoo Linux and I'm trying to connect remotely from 2 different Windows machine (all computers are on the same network in my home).

When I had trouble connecting I asked a question on Gentoo forum and got a suggestion to run:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION ;

My guess is that I used a static IP address of one of the machines because I can connect from one Windows machine but not from the other.

So now, my question would be - is it possible to issue something like:

ALTER PRIVILEGES ON.....;

Or maybe I can do some query to fix this?

Thank you.

BTW, a little off-topic: I couldn't find a tag for "remote-access". Could someone please create it? Or its not present for a reason?

---

EDIT:

I actually want the permissions to be like this:

GRANT ALL ON foo.* TO bar@'192.168.1.x' IDENTIFIED BY 'PASSWORD';

so that I can connect only from 192.168.1.1 - 192.168.1.254. I don't need this server to be publicly available.

---

EDIT2:

GRANT USAGE ON *.* TO 'root'@'192.168.1.4' IDENTIFIED BY PASSWORD '*DC365D603F605E79DE2F2B8DA969DBB8497BEC89'
GRANT ALL PRIVILEGES ON `draft`.* TO 'root'@'192.168.1.4' WITH GRANT OPTION

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*DC365D603F605E79DE2F2B8DA969DBB8497BEC89' WITH GRANT OPTION
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

---

EDIT3:

I just tried to execute following command:

GRANT ALL PRIVILEGES ON *.* TO 'root'@192.168.1.% IDENTIFIED BY '<my_password>' WITH GRANT OPTION;

and I got following response:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '% IDENTIFIED BY '<my_password>'' at line 1 0.00041 sec

Answer 1

So what you can do :

1. If you want to change IP from 192.168.1.4 to 192.168.1.3 then you can update mysql.user table

Update mysql.user set host =' 192.168.1.3 where host='192.168.1.3' ;

Flush privileges:

You cannot define a range of hostname in single query of creating user. Either you need to create same user for different IP or you can give % as hostname but i will not recommend this

2. If you want different user , create same user with required IP.

Let me know if need more clarity

EDIT

You cannot define a range for hostname in mysql.user table. To have this you can create same user with same role but on different IP. You have to do this for every IP individually.

UPDATE

Please try creating user :

grant select to 'root'@'192.168.1.%' on db.* Identified by 'pass' ;

UPDATE 2

This is because IP should be placed between '' as a string.

So here you go:

GRANT ALL PRIVILEGES ON db_name.table_name TO 'root'@'192.168.1.%' IDENTIFIED BY '' WITH GRANT OPTION;