As discussed in a previous article about MySQL NDB Cluster, it is a database cluster setup based on the shared-nothing clustering and database sharding architecture. It is built on the NDB or NDBCLUSTER storage engine for MySQL DBMS. Also the 2nd article about MySQL NDB Cluster Installation outlined installation of the cluster through setting up its nodes – SQL node, data nodes and management node. This article discusses how to secure your MySQL NDB Cluster through the below perspectives:
1. Network security 2. Privilege system 3. Standard security procedures for the NDB cluster.Detailed information is available at MySQL Development Documentation about NDB Cluster Security
NDB Cluster Network Security
Cluster communication can be divided into client to cluster and cluster node to node categories. Node to node (between SQL, Data and Management nodes) communication are not recommended to be through SSL or using any other encryption protocols. This is because of the extra overhead due to network lag and latency that can affect the cluster performance. A network related security concern that needs to be addressed for the NDB Cluster is specified below.
For the config.ini file of an NDB Cluster, if the HostName parameter under [mysqld el_id=’59ad103a65a56′] is blank or absent, or if there are empty slots under the [mysqld el_id=’59ad103a65a93′] section, then the management node will not check about from which host, an SQL/API node or MySQL Server is attempting a connection to it. In such a case an SQl/API node can connect to the management node and access its data. To prevent this, specify a HostName parameter for all [mysqld el_id=’59ad103a65acc’] and [api] sections in the config.ini file. Additionally, in such a scenario and management node client (ndb_mgm client) that knows the management node’s host address can connect to it and execute administrative commands to control the cluster.
The above issue can be resolved by many ways. One is by isolating cluster node network from external public network from which connections to the cluster is initiated. This can be done by placing a hardware or network firewall between the public network and SQL nodes.
Though costly, this setup is dependable and fault proof. It does not allow any access to data and management nodes from outside or any communication from the data and management nodes to the public network. Obviously the SQL nodes need to be secured against hacking.
Another option is to use software firewalls on all host nodes and configure them to block data packets that need not come or go outside the cluster.
The firewall on the data and management nodes hosts allow connection from SQL nodes only. Outside applications or clients can only communicate with cluster through SQL nodes. Each host machine will allow and deny traffic as specified below:
SQL node: Allows traffic from management and data nodes from within the cluster network that’s IP address is allowed, allows traffic from within the network through the assigned port and denies all other traffic.
Management and Data nodes: Allows traffic from management and data nodes, allows traffic from SQL nodes through the allowed IP address and denies all other traffic.
A combination of hardware and software firewall (network and host based) also can be used to secure the cluster.
This setup keeps the cluster behind the network firewall, but allows traffic between the management/data nodes and SQL nodes.
MySQL Privileges and NDB Cluster Security
Like any other MySQL storage engines, user privileges information for NDB storage engine tables are stored in mysql system database. Since MySQL grant tables use the MyISAM storage engine, those tables are not duplicated or shared across the SQL nodes in the NDB cluster. So changes in users and privileges made in one SQL node are not propagated to other SQL nodes by default. To enable this propagation, or distributed privileges across all SQL nodes, following steps need to be implemented:
On each of the SQL node MySQL server, import the script share/ ndb_dist_priv.sql available inside the MySQL installation directory.
shell> mysql options -uroot < share/ndb_dist_priv.sql
This script will create a number of stored routines on the SQL node. To verify this, use the below query to get the output as shown:
mysql> SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE -> FROM INFORMATION_SCHEMA.ROUTINES -> WHERE ROUTINE_NAME LIKE 'mysql_cluster%' -> ORDER BY ROUTINE_TYPE; +---------------------------------------------+----------------+--------------+ | ROUTINE_NAME | ROUTINE_SCHEMA | ROUTINE_TYPE | +---------------------------------------------+----------------+--------------+ | mysql_cluster_privileges_are_distributed | mysql | FUNCTION | | mysql_cluster_backup_privileges | mysql | PROCEDURE | | mysql_cluster_move_grant_tables | mysql | PROCEDURE | | mysql_cluster_move_privileges | mysql | PROCEDURE | | mysql_cluster_restore_local_privileges | mysql | PROCEDURE | | mysql_cluster_restore_privileges | mysql | PROCEDURE | | mysql_cluster_restore_privileges_from_local | mysql | PROCEDURE | +---------------------------------------------+----------------+--------------+
7 rows in set (0.01 sec)
The stored procedure mysql_cluster_move_privileges creates 2 sets of copies of the mysql database, one as MyISAM and other as NDBCLUSTER storage engine. Then the script invokes mysql_cluster_move_grant_tables that contain ALTER TABLE statements to convert the mysql system tables to NDB storage engine.
Once this conversion to distributed privileges is made at each SQL nodes, any change in users or privileges at any one SQL node will be distributed to all SQL nodes in the cluster.
As stated above in Cluster Network Security, it is important to secure the users in any SQL Node, so that a compromised node is not used to access and corrupt data in other SQL nodes. So ensure that the config.ini does not contain an empty [mysqld] block or use the HostName parameter. Also a firewall based security system is necessary to segregate the cluster from public network to ensure proper security.
Standard Security Procedures for NDB Cluster
The security procedures applicable to a standard MySQL Server is also valid for a MySQL NDB Cluster.
- Always run the MySQL server (mysqld) as the mysql system user, that is part of the mysql user group.
- Ensure that the system’s MySQL data directory is owned by the mysql user.
- Ensure that the my.cnf of each SQL node contains user=mysql in the [mysqld] section.
- Never run mysqld as the system root user.
- Delete the anonymous user account created during installation.
- Always keep in mind that the different nodes inside the cluster have high mutual transparency and hence it is highly recommended to secure the cluster through tight user privilege management and network security.