Connecting to MySQL from Two Different Machines

MySQLpermissionsremote

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

Best Answer

So what you can do :

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

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;

Related Solutions

MySQL Permissions – How to Grant Additional Permissions

The fundamental problem you have is this:

You cannot grant another user a privilege which you yourself do not have; the GRANT OPTION privilege enables you to assign only those privileges which you yourself possess.

^{— http://dev.mysql.com/doc/refman/5.6/en/grant.html}

This makes sense, since if it were otherwise, then you could just grant privileges to yourself.

There is one way around this, depending on how much flexibility you have with the calling code -- stored procedures can run with the credentials of the defining user (as opposed to the invoking user). If the calling system could call a procedure instead of using GRANT ..., then you can create a procedure that is executable by the restricted user, granting other privileges as appropriate.

MySQL – How to Give root@localhost Permission to Grant Privileges

I have dealt with this issue before.

When you ran

select count(1) UserTableColumnCount from information_schema.columns
where table_schema='mysql' and table_name='user';

you should have gotten 42. That's how many columns MySQL 5.5 has for mysql.user. Since you got 39, that means you must have upgraded from MySQL 5.1. That has 39 columns.

I wrote an earlier post about the number of columns in mysql.user in different versions : MySQL service stops after trying to grant privileges to a user

Here is the post where I dealt with this : mysql: Restore All privileges to admin user

Hopefully you could run

# mysql_upgrade --upgrade-system-tables

to realign mysql.user and have it autofill missing permissions with Y.

Give it a Try !!!

If you want to try to fix the mysql.user manually, here are the steps:

#
# Backup the mysql.user table
#
CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
#
# Add Missing Columns
#
ALTER TABLE mysql.user 
    ADD COLUMN Create_tablespace_priv enum('N','Y') NOT NULL DEFAULT 'N'
    AFTER Trigger_Priv
;
ALTER TABLE mysql.user ADD COLUMN plugin char(64);
ALTER TABLE mysql.user ADD COLUMN authentication_string text DEFAULT NULL;
#
# Give root user all privileges
#
UPDATE mysql.user SET
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',
Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',
Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',
Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',
Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',
Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',
Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',
Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user='root';
FLUSH PRIVILEGES;

That's it !!!

Best Answer

Related Solutions

MySQL Permissions – How to Grant Additional Permissions

MySQL – How to Give root@localhost Permission to Grant Privileges

Related Question