Incorrect MySQL user grant statement

[Darren Hennessy]

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]

Which version of MySQL are you using?

Marko

[Darren Hennessy]

The MySQL server version is 5.7.21

[DB Solo Admin]

I will investigate and get back to you.

Marko

[DB Solo Admin]

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]

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,

[DB Solo Admin]

What happens if you modify the example I gave earlier

source

---

create table test(i int);

grant select, insert, update on test to 'root'@'localhost';

destination

------------------

create table test(i int);

Is the sync script incorrect?

Marko

[Darren Hennessy]

That works as well as your script still includes the permissions one per statement for the table:

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

GRANT INSERT ON TABLE test TO `root`@`localhost`;
GRANT UPDATE ON TABLE test TO `root`@`localhost`;

But as i pointed out in my last response, the error occurs when syncing a view. So i added a view to the database and the grant error occurs again in the sync script:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testview` AS select `test`.`i` AS `i` from `test`;

GRANT SELECT,INSERT,UPDATE ON testview TO 'localhost'@'root';

[DB Solo Admin]

This should be fixed now. You can find the new version here

dbsolo.com/preview

thanks

marko

[Darren Hennessy]

Yep that appears to be correct now.

thanks,

Darren