MySQL 9.1 Release Notes
Privilege synchronization is the mechanism used by NDB Cluster for
sharing and synchronizing users, roles, and privileges between SQL
nodes. 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, as shown here:
mysql> SHOW GRANTS for 'jon'@'localhost';
+---------------------------------------------------+
| Grants for jon@localhost |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `jon`@`localhost` |
| GRANT NDB_STORED_USER ON *.* TO `jon`@`localhost` |
+---------------------------------------------------+You can also verify that privileges are shared for this account using the ndb_select_all utility supplied with NDB Cluster, like this (some output wrapped to preserve formatting):
$> ndb_select_all -d mysql ndb_sql_metadata | grep '`jon`@`localhost`'
12 "'jon'@'localhost'" 0 [NULL] "GRANT USAGE ON *.* TO `jon`@`localhost`"
11 "'jon'@'localhost'" 0 2 "CREATE USER `jon`@`localhost`
IDENTIFIED WITH 'caching_sha2_password' AS
0x2441243030352466014340225A107D590E6E653B5D587922306102716D752E6656772F3038512F
6C5072776D30376D37347A384B557A4C564F70495158656A31382E45324E33
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT"
12 "'jon'@'localhost'" 1 [NULL] "GRANT NDB_STORED_USER ON *.* TO `jon`@`localhost`"
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.
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.
You should keep in mind that, because shared schema change
operations are performed synchronously, the next shared schema
change following a change to any shared 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.
Note
NDB Cluster 9.1 does not support distribution of
MySQL users and privileges across SQL nodes in an NDB Cluster by
altering the MySQL privilege tables such that they use the
NDB storage engine as was done in older
releases (NDB 7.6 and earlier—see
Distributed Privileges Using Shared Grant Tables).