No MySQL Sandbox user seems to have WITH GRANT OPTION

[Simon J Mudd]

I'm looking at The MySQL Sandbox, version 3.0.66 and notice the grants for make_replication_sandbox

which are under .../rsandbox_mysql-5_6_26/node1/grants.mysql show:

...
grant all on *.* to msandbox@'127.%' identified by 'msandbox';
grant all on *.* to msandbox@'localhost' identified by 'msandbox';
...

This is the "super" user.

However, I notice that there is now WITH GRANT OPTION specified.

This means I am unable to add grants for new user users as part of a test infrastructure with MaxScale and MySQL as new grants are needed and I want to apply them on the master and let them replicate down to the slaves. This does not work with the current setup.

The error I get when I try this is:

$ ~/sandboxes/rsandbox_mysql-5_6_26/m
master [localhost] {msandbox} ((none)) > create user 'some_user'@'127.%' identified by 'something';
Query OK, 0 rows affected (0.01 sec)

master [localhost] {msandbox} ((none)) > grant select on test.* to 'some_user'@'127.%';
ERROR 1044 (42000): Access denied for user 'msandbox'@'localhost' to database 'test'
master [localhost] {msandbox} ((none)) > show grants;
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for msandbox@localhost |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'localhost' IDENTIFIED BY PASSWORD '*6C387FC3893DBA1E3BA155E74754DA6682D04747' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

master [localhost] {msandbox} ((none)) > select @@version;
+------------+
| @@version |
+------------+
| 5.6.26-log |
+------------+
1 row in set (0.00 sec)

My normal root type user (not using MySQL sandbox) has grants like this:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'xxxx' WITH GRANT OPTION;

Note: My intention here is to _replicate_ grants as this works fine and avoids me setting up grants on the master + 2 slaves in this case.

So please consider changing the grants file along these lines:

[~/sandboxes/rsandbox_mysql-5_6_26/master]$ diff -u grants.mysql{.orig,}
--- grants.mysql.orig 2015-08-18 13:08:20.000000000 +0200
+++ grants.mysql 2015-08-18 13:08:52.000000000 +0200
@@ -1,8 +1,8 @@

 use mysql;
 set password=password('msandbox');
-grant all on *.* to msandbox@'127.%' identified by 'msandbox';
-grant all on *.* to msandbox@'localhost' identified by 'msandbox';
+grant all on *.* to msandbox@'127.%' identified by 'msandbox' with grant option;
+grant all on *.* to msandbox@'localhost' identified by 'msandbox' with grant option;
 grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,
     SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE
     on *.* to msandbox_rw@'localhost' identified by 'msandbox';

[Giuseppe Maxia (giuseppe-maxia)]

This is not a bug. You can already run the grant command using the root user, which has the grant privilege:

~/sandboxes/rsandbox_mysql-5_6_26/m -u root

master [localhost] {root} ((none)) > show grants for root@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
2 rows in set (0.00 sec)

master [localhost] {root} ((none)) > show grants for msandbox@'127.%'\G
*************************** 1. row ***************************
Grants for msandbox@127.%: GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'127.%'
1 row in set (0.00 sec)

master [localhost] {root} ((none)) > show grants for msandbox_rw@'127.%'\G
*************************** 1. row ***************************
Grants for msandbox_rw@127.%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* TO 'msandbox_rw'@'127.%'
1 row in set (0.00 sec)

master [localhost] {root} ((none)) > show grants for msandbox_ro@'127.%'\G
*************************** 1. row ***************************
Grants for msandbox_ro@127.%: GRANT SELECT, EXECUTE ON *.* TO 'msandbox_ro'@'127.%'
1 row in set (0.00 sec)

Similarly, you can use a less privileged user

~/sandboxes/rsandbox_mysql-5_6_26/m -u msandbox_rw

~/sandboxes/rsandbox_mysql-5_6_26/m -u msandbox_ro