21.6.13 Distributed Privileges Using Shared Grant Tables

NDB Cluster supports distribution of MySQL users and privileges across all SQL nodes in an NDB Cluster. This support is not enabled by default; you should follow the procedure outlined in this section in order to do so.

Normally, each MySQL server's user privilege tables in the mysql database must use the MyISAM storage engine, which means that a user account and its associated privileges created on one SQL node are not available on the cluster's other SQL nodes. An SQL file ndb_dist_priv.sql provided with the NDB Cluster distribution can be found in the share directory in the MySQL installation directory.

The first step in enabling distributed privileges is to load this script into a MySQL Server that functions as an SQL node (which we refer to after this as the target SQL node or MySQL Server). You can do this by executing the following command from the system shell on the target SQL node after changing to its MySQL installation directory (where options stands for any additional options needed to connect to this SQL node):

$> mysql options -uroot < share/ndb_dist_priv.sql

Importing ndb_dist_priv.sql creates a number of stored routines (six stored procedures and one stored function) in the mysql database on the target SQL node. After connecting to the SQL node in the mysql client (as the MySQL root user), you can verify that these were created as shown here:

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 named mysql_cluster_move_privileges creates backup copies of the existing privilege tables, then converts them to NDB.

mysql_cluster_move_privileges performs the backup and conversion in two steps. The first step is to call mysql_cluster_backup_privileges, which creates two sets of copies in the mysql database:

• A set of local copies that use the MyISAM storage engine. Their names are generated by adding the suffix _backup to the original privilege table names.

• A set of distributed copies that use the NDBCLUSTER storage engine. These tables are named by prefixing ndb_ and appending _backup to the names of the original tables.

After the copies are created, mysql_cluster_move_privileges invokes mysql_cluster_move_grant_tables, which contains the ALTER TABLE ... ENGINE = NDB statements that convert the mysql system tables to NDB.

Normally, you should not invoke either mysql_cluster_backup_privileges or mysql_cluster_move_grant_tables manually; these stored procedures are intended only for use by mysql_cluster_move_privileges.

Although the original privilege tables are backed up automatically, it is always a good idea to create backups manually of the existing privilege tables on all affected SQL nodes before proceeding. You can do this using mysqldump in a manner similar to what is shown here:

$> mysqldump options -uroot \
    mysql user db tables_priv columns_priv procs_priv proxies_priv > backup_file

To perform the conversion, you must be connected to the target SQL node using the mysql client (again, as the MySQL root user). Invoke the stored procedure like this:

mysql> CALL mysql.mysql_cluster_move_privileges();
Query OK, 0 rows affected (22.32 sec)

Depending on the number of rows in the privilege tables, this procedure may take some time to execute. If some of the privilege tables are empty, you may see one or more No data - zero rows fetched, selected, or processed warnings when mysql_cluster_move_privileges returns. In such cases, the warnings may be safely ignored. To verify that the conversion was successful, you can use the stored function mysql_cluster_privileges_are_distributed as shown here:

mysql> SELECT CONCAT(
    ->    'Conversion ',
    ->    IF(mysql.mysql_cluster_privileges_are_distributed(), 'succeeded', 'failed'),
    ->    '.')
    ->    AS Result;
+-----------------------+
| Result                |
+-----------------------+
| Conversion succeeded. |
+-----------------------+
1 row in set (0.00 sec)

mysql_cluster_privileges_are_distributed checks for the existence of the distributed privilege tables and returns 1 if all of the privilege tables are distributed; otherwise, it returns 0.

You can verify that the backups have been created using a query such as this one:

mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
    ->     WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME LIKE '%backup'
    ->     ORDER BY ENGINE;
+-------------------------+------------+
| TABLE_NAME              | ENGINE     |
+-------------------------+------------+
| db_backup               | MyISAM     |
| user_backup             | MyISAM     |
| columns_priv_backup     | MyISAM     |
| tables_priv_backup      | MyISAM     |
| proxies_priv_backup     | MyISAM     |
| procs_priv_backup       | MyISAM     |
| ndb_columns_priv_backup | ndbcluster |
| ndb_user_backup         | ndbcluster |
| ndb_tables_priv_backup  | ndbcluster |
| ndb_proxies_priv_backup | ndbcluster |
| ndb_procs_priv_backup   | ndbcluster |
| ndb_db_backup           | ndbcluster |
+-------------------------+------------+
12 rows in set (0.00 sec)

Once the conversion to distributed privileges has been made, any time a MySQL user account is created, dropped, or has its privileges updated on any SQL node, the changes take effect immediately on all other MySQL servers attached to the cluster. Once privileges are distributed, any new MySQL Servers that connect to the cluster automatically participate in the distribution.

Note

For clients connected to SQL nodes at the time that mysql_cluster_move_privileges is executed, you may need to execute FLUSH PRIVILEGES on those SQL nodes, or to disconnect and then reconnect the clients, in order for those clients to be able to see the changes in privileges.

All MySQL user privileges are distributed across all connected MySQL Servers. This includes any privileges associated with views and stored routines, even though distribution of views and stored routines themselves is not currently supported.

In the event that an SQL node becomes disconnected from the cluster while mysql_cluster_move_privileges is running, you must drop its privilege tables after reconnecting to the cluster, using a statement such as DROP TABLE IF EXISTS mysql.user mysql.db mysql.tables_priv mysql.columns_priv mysql.procs_priv. This causes the SQL node to use the shared privilege tables rather than its own local versions of them. This is not needed when connecting a new SQL node to the cluster for the first time.

In the event of an initial restart of the entire cluster (all data nodes shut down, then started again with --initial), the shared privilege tables are lost. If this happens, you can restore them using the original target SQL node either from the backups made by mysql_cluster_move_privileges or from a dump file created with mysqldump. If you need to use a new MySQL Server to perform the restoration, you should start it with --skip-grant-tables when connecting to the cluster for the first time; after this, you can restore the privilege tables locally, then distribute them again using mysql_cluster_move_privileges. After restoring and distributing the tables, you should restart this MySQL Server without the --skip-grant-tables option.

You can also restore the distributed tables using ndb_restore --restore-privilege-tables from a backup made using START BACKUP in the ndb_mgm client. (The MyISAM tables created by mysql_cluster_move_privileges are not backed up by the START BACKUP command.) ndb_restore does not restore the privilege tables by default; the --restore-privilege-tables option causes it to do so.

You can restore the SQL node's local privileges using either of two procedures. mysql_cluster_restore_privileges works as follows:

1. If copies of the mysql.ndb_*_backup tables are available, attempt to restore the system tables from these.

2. Otherwise, attempt to restore the system tables from the local backups named *_backup (without the ndb_ prefix).

The other procedure, named mysql_cluster_restore_local_privileges, restores the system tables from the local backups only, without checking the ndb_* backups.

The system tables re-created by mysql_cluster_restore_privileges or mysql_cluster_restore_local_privileges use the MySQL server default storage engine; they are not shared or distributed in any way, and do not use NDB Cluster's NDB storage engine.

The additional stored procedure mysql_cluster_restore_privileges_from_local is intended for the use of mysql_cluster_restore_privileges and mysql_cluster_restore_local_privileges. It should not be invoked directly.

Important

Applications that access NDB Cluster data directly, including NDB API and ClusterJ applications, are not subject to the MySQL privilege system. This means that, once you have distributed the grant tables, they can be freely accessed by such applications, just as they can any other NDB tables. In particular, you should keep in mind that NDB API and ClusterJ applications can read and write user names, host names, password hashes, and any other contents of the distributed grant tables without any restrictions.