Documentation Home
MySQL NDB Cluster 8.0
Related Documentation Download this Excerpt
PDF (US Ltr) - 5.2Mb
PDF (A4) - 5.2Mb

MySQL NDB Cluster 8.0  /  Management of NDB Cluster  /  Distributed MySQL Privileges with NDB_STORED_USER

6.12 Distributed MySQL Privileges with NDB_STORED_USER

NDB 8.0 introduces a new mechanism for sharing and synchronizing users, roles, and privileges between SQL nodes connected to an NDB Cluster. This can be enabled by granting the NDB_STORED_USER privilege. See the description of the privilege for usage information.

NDB_STORED_USER is printed in the output of SHOW GRANTS as with any other privilege. To verify that privileges are shared, use the ndb_select_all utility supplied with the NDB Cluster distribution, as shown here (some output wrapped to preserve formatting):

$> ndb_select_all -d mysql ndb_sql_metadata
type    name    seq     note    sql_ddl_text
11      "'jon'@'localhost'"     0       4       "CREATE USER 'jon'@'localhost'
IDENTIFIED WITH 'caching_sha2_password' AS
'$A$005${B];3!?tI\".EFy\ZA5K5DQHrWiBvuRNYTIMeO0YeBlPpZotFRPjVTYzTA5b0' REQUIRE
12      "'jon'@'localhost'"     0       [NULL]  "GRANT USAGE ON *.* TO `jon`@`localhost`"
12      "'jon'@'localhost'"     3       [NULL]  "GRANT ALL PRIVILEGES ON `test`.* TO `jon`@`localhost`"
12      "'jon'@'localhost'"     2       [NULL]  "GRANT ALL PRIVILEGES ON `mydb`.* TO `jon`@`localhost`"
12      "'jon'@'localhost'"     1       [NULL]  "GRANT NDB_STORED_USER ON *.* TO `jon`@`localhost`"
5 rows returned

ndb_sql_metadata is a special NDB table that is not visible using the mysql or other MySQL client.

A statement granting the NDB_STORED_USER privilege, such as GRANT NDB_STORED_USER ON *.* TO 'cluster_app_user'@'localhost', works by directing NDB to create a snapshot using the queries SHOW CREATE USER cluster_app_user@localhost and SHOW GRANTS FOR cluster_app_user@localhost, then storing the results in ndb_sql_metadata. Any other SQL nodes are then requested to read and apply the snapshot. Whenever a MySQL server starts up and joins the cluster as an SQL node it executes these stored CREATE USER and GRANT statements as part of the cluster schema synchronization process.

Whenever an SQL statement is executed on an SQL node other than the one where it originated, the statement is run in a utility thread of the NDBCLUSTER storage engine; this is done within a security environment equivalent to that of the MySQL replication replica applier thread.

Beginning with NDB 8.0.27, an SQL node performing a change to user privileges takes a global lock before doing so, which prevents deadlocks by concurrent ACL operations on different SQL nodes. Prior to NDB 8.0.27, changes to users with NDB_STORED_USER were distributed completely asynchronously, without any locks being taken.

You should keep in mind that, because distributed schema change operations are performed synchronously, the next distributed schema change following a change to any distributed user or users serves as a synchronization point. Any pending user changes run to completion before the schema change distribution can begin; after this the schema change itself runs synchronously. For example, if a DROP DATABASE statement follows a DROP USER of a distributed user, the drop of the database cannot take place until the drop of the user has completed on all SQL nodes.

In the event that multiple GRANT, REVOKE, or other user administration statements from multiple SQL nodes cause privileges for a given user to diverge on different SQL nodes, you can fix this problem by issuing GRANT NDB_STORED_USER for this user on an SQL node where the privileges are known to be correct; this causes a new snapshot of the privileges to be taken and synchronized to the other SQL nodes.

NDB Cluster 8.0 does not support distribution of MySQL users and privileges across SQL nodes in an NDB Cluster by converting the MySQL privilege tables to use the NDB storage engine, as implemented in NDB 7.6 and earlier releases (see Distributed Privileges Using Shared Grant Tables). For information about the impact of this change on upgrades to NDB 8.0 from a previous release, see Section 3.7, “Upgrading and Downgrading NDB Cluster”.