0

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
  • Do you want to modify host name of your existing user ? – Ankit Kapoor Jun 28 '17 at 18:11
  • @AnkitKapoor, not sure what you mean. All I need is to allow a user to connect from any machine that has an IP in a range of 192.168.1.1 - 192.168.1.254. As it stands right now this user (root) can connect only from localhost and 192.168.1.3 machines only. – Igor Jun 28 '17 at 18:15
  • Please paste output of show grants for user@hostname – Ankit Kapoor Jun 28 '17 at 18:17
  • @AnkitKapoor, what is hostname? I get an error when executing this command: There is no such grant identified for user 'root' on host 'hostname' – Igor Jun 28 '17 at 22:12
  • Show grants for root@localhost;. AND show grants for root@192.168.1.3. moreover you cannot define define wild card in host name . Either your can give specific IP or wild character . Giving % will allow to connect root from anywhere – Ankit Kapoor Jun 29 '17 at 1:58
2

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

  1. 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;

  • did you read my question in its entirety? I need to connect to mySQL from any machine whose IP address is in the range 192.168.1.1-192.168.1.254. It is explained in the first EDIT. Please change your answer accordingly. Also I believe this is kind of workaround. Is it possible to not to use SQL? – Igor Jun 29 '17 at 3:29
  • Yes i understand your requirements . Have you read my comments ? This is not possible​ to give a range in my SQL. As i said either you need to create same user on different IP. you need to repeat same for every IP – Ankit Kapoor Jun 29 '17 at 3:56
  • do you have a solution which does not involve SQL? – Igor Jun 29 '17 at 4:09
  • What do you mean by solution which doesn't involve SQL ? Do you mean via some shell script ? – Ankit Kapoor Jun 29 '17 at 4:11
  • See my Update section in answer – Ankit Kapoor Jun 29 '17 at 4:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.