Incorrect MySQL user grant statement

20 views
Skip to first unread message

Darren Hennessy

unread,
Jul 7, 2021, 1:33:34 AMJul 7
to DB Solo
Hi,
I have noticed when performing a schema compare of two MySQL databases the resulting synchronisation script adds incorrectly formatted GRANT statements. Below is an example of the output:

GRANT SELECT,INSERT,UPDATE,DELETE ON data_level_max_id TO 'localhost'@'local_unit';
GRANT SELECT,INSERT,UPDATE,DELETE ON data_level_max_id TO '192.168.1.230'@'local_unit';
GRANT SELECT,INSERT,UPDATE,DELETE ON data_level_max_id TO '192.168.2.230'@'local_unit';

As you can see the user is specified as <host>@<username> instead of <username>@<host>

I am using the current preview version as you fixed another bug I was seeing.
regards,
Darren

DB Solo Admin

unread,
Jul 7, 2021, 5:54:50 PMJul 7
to DB Solo
Which version of MySQL are you using?

Marko

Darren Hennessy

unread,
Jul 7, 2021, 6:20:45 PMJul 7
to DB Solo
The MySQL server version is 5.7.21

DB Solo Admin

unread,
Jul 8, 2021, 5:57:34 PMJul 8
to DB Solo
I will investigate and get back to you.

Marko

DB Solo Admin

unread,
Jul 21, 2021, 8:06:33 PM (7 days ago) Jul 21
to DB Solo
I created the following table

source
---
create table test(i int);

grant select on test to 'root'@'localhost';

destination
------------------
create table test(i int);

And the generated sync script seems to be correct

GRANT SELECT ON TABLE test TO `root`@`localhost`;


thanks
marko


Darren Hennessy

unread,
Jul 26, 2021, 12:21:10 AM (3 days ago) Jul 26
to DB Solo
If I re-create the same test scenario as you have above I get the correct grant statement as well. However when i look as some of the synchronisation scripts i have I seem to see a pattern:

When the grant statement is created with one permission per statement it looks ok (this is a table):

REVOKE ALL PRIVILEGES ON TABLE data_operations_plot FROM `local_unit`@`192.168.2.230`;
GRANT SELECT ON TABLE data_operations_plot TO `local_unit`@`192.168.2.230`;
GRANT INSERT ON TABLE data_operations_plot TO `local_unit`@`192.168.2.230`;
GRANT UPDATE ON TABLE data_operations_plot TO `local_unit`@`192.168.2.230`;
GRANT DELETE ON TABLE data_operations_plot TO `local_unit`@`192.168.2.230`;

but when the permissions are concatenated together in one statement they are wrong (this one is a view):

GRANT SELECT,INSERT,UPDATE,DELETE ON level_report_one_row TO 'localhost'@'local_unit';
GRANT SELECT,INSERT,UPDATE,DELETE ON level_report_one_row TO '192.168.1.230'@'local_unit';
GRANT SELECT,INSERT,UPDATE,DELETE ON level_report_one_row TO '192.168.2.230'@'local_unit';

regards,
Reply all
Reply to author
Forward
0 new messages