MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
NDB 8.0.18 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):
shell> 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
NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD
REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT"
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.
You should be aware that changes to users with
NDB_STORED_USER
are distributed
asynchronously. 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 23.2.7, “Upgrading and Downgrading NDB Cluster”.