Incorrect MySQL user grant statement

36 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 PMJul 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 AMJul 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,

DB Solo Admin

unread,
Aug 1, 2021, 12:48:32 AMAug 1
to DB Solo
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

unread,
Aug 1, 2021, 8:08:57 PMAug 1
to DB Solo
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

unread,
Aug 15, 2021, 5:18:56 AMAug 15
to DB Solo
This should be fixed now. You can find the new version here


thanks
marko

Darren Hennessy

unread,
Aug 15, 2021, 9:49:37 PMAug 15
to DB Solo
Yep that appears to be correct now.

thanks,
Darren
Reply all
Reply to author
Forward
0 new messages