Nuances of MySQL Fabric User GRANTs

MySQL Fabric is a new product and some folks are running into issues when trying to deploy test instances that grow beyond a single server.

The Fabric documentation has an example GRANT statement illustrating the simple localhost use case:

The example shown above belies the more complex way that Fabric uses the fabric login.

The Fabric config file has 3 sections that deal with user and password values, I will only focus on the [servers] section of the configuration file.  The user and password defined in this section is used by the mysqlfabric utility to connect to managed servers and set up replication between managed servers in HA groups.

Fabric has two different types of HA group, the global group and shard groups.  The global group is used to store tables which are part of the Fabric deployment but are not sharded.  These are typically small tables or tables that are needed for joins on each shard.  The global HA group is similar to the shard HA groups, there is a master and one or more slaves.  The topology begins to get a little more complex because the global group is replicated to each shard group.  Each shard group contains a complete copy of the data stored in the global group so that joins can be performed locally on each shard.  This creates a GRANT dependency whereby the Fabric user must be able to connect as a replication slave to every other MySQL instance in the Fabric deployment.

Here is a simple topology relationship between the global group and shard groups:

Simple Fabric topology diagram

The diagram has a total of 9 servers, each of which can become a master or a slave through the promote or demote mysqlfabric commands.

To accommodate all possible master/slave combinations in the fabric deployment you would need the following example GRANTs:

The dynamic master/slave relationship between each MySQL instance means that you must have these GRANTs on each server.  A simpler approach is to use the MySQL wildcard capabilities to define a single user GRANT.

Here is an example GRANT statement which would allow all servers on the same subnet to authenticate using the same account:

Using wildcards can be dangerous without following a few best practices:

  • Never use ‘%’ as the host, this can permit any client IP address to connect with that account.  MySQL does a good job of extending the typical user/password security model by requiring the host to match too.  You should not allow any client IP to connect to a single account; client IP addresses should be whitelisted whenever possible.
  • All of your Fabric hosts should be on a logically isolated VLAN or network.  Best performance is achieved when the network path between servers is short and fast.  Security and performance both have the same requirement:  a dedicated network segment.  You can use a VLAN to isolate your MySQL servers from outside traffic and make it easier to manage physical servers.  If the IP network the MySQL servers are using is part of a larger routable IP space, rogue users could potentially break-in to your network and use their access to compromise data on your MySQL servers.  Many companies are subject to compliance requirements which favor such isolation.
  • Traffic to and from your MySQL servers should be managed by a router, firewall, unified threat management device, or load balancer.  This isn’t a strict rule, but it is a good practice to implement.  Your MySQL servers contain business critical data and should be protected by more than one layer of security.  Many UTM devices can detect attacks that happen via your user applications.  You might have a security vulnerability in your application which is undiagnosed and the UTM can alert you to this and block attacks before they compromise your data.
  • The skip-name-resolve option should be enabled on production servers to reduce the DNS lookup overhead and prevent reverse DNS spoofing.

The TL;DR is that you need to ensure each MySQL instance in your Fabric deployment has the necessary permissions to act as a replication slave to every other MySQL instance and allow the Fabric node to access your instances, this requires individual GRANTs or a wildcard GRANT for the user defined in the [servers] section your fabric.cfg file.


Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.