enterprise-support team mailing list archive

[Simon J Mudd <1485989@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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';

** Affects: mysql-sandbox
     Importance: Undecided
         Status: New


** Tags: grant option with

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1485989

Title:
  No MySQL Sandbox user seems to have WITH GRANT OPTION

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-sandbox/+bug/1485989/+subscriptions