<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="mysql-cluster-privilege-synchronization">
      </a>
      25.6.13 Privilege Synchronization and NDB_STORED_USER
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045091344576">
  </a>
  <a class="indexterm" name="idm46045091343536">
  </a>
  <p>
   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
   <a class="link" href="privileges-provided.html#priv_ndb-stored-user">
    <code class="literal">
     NDB_STORED_USER
    </code>
   </a>
   privilege. See the
      description of the privilege for usage information.
  </p>
  <p>
   <code class="literal">
    NDB_STORED_USER
   </code>
   is printed in the output of
   <a class="link" href="show-grants.html" title="15.7.7.22 SHOW GRANTS Statement">
    <code class="literal">
     SHOW GRANTS
    </code>
   </a>
   as with any other
      privilege, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa89672665"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">mysql&gt;</span><span class="token command"> SHOW</span> GRANTS for <span class="token atrule">'jon'</span>@<span class="token atrule">'localhost'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Grants for jon@localhost                          <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT USAGE ON *.* TO `jon`@`localhost`           <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT NDB_STORED_USER ON *.* TO `jon`@`localhost` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   You can also verify that privileges are shared for this account
      using the
   <a class="link" href="mysql-cluster-programs-ndb-select-all.html" title="25.5.25 ndb_select_all — Print Rows from an NDB Table">
    <span class="command">
     <strong>
      ndb_select_all
     </strong>
    </span>
   </a>
   utility supplied with
      NDB Cluster, like this (some output wrapped to preserve
      formatting):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa401141"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">ndb_select_all</span> <span class="token property">-d</span> mysql ndb_sql_metadata | grep <span class="token atrule">'`jon`@`localhost`'</span>
12      <span class="token atrule">"'jon'@'localhost'"</span>     0       <span class="token property"><span class="token punctuation">[</span>NULL<span class="token punctuation">]</span></span>  <span class="token atrule">"GRANT USAGE ON *.* TO `jon`@`localhost`"</span>
11      <span class="token atrule">"'jon'@'localhost'"</span>     0       2       "CREATE USER `jon`@`localhost`
IDENTIFIED WITH <span class="token atrule">'caching_sha2_password'</span> AS
0x2441243030352466014340225A107D590E6E653B5D587922306102716D752E6656772F3038512F
6C5072776D30376D37347A384B557A4C564F70495158656A31382E45324E33
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT"
12      <span class="token atrule">"'jon'@'localhost'"</span>     1       <span class="token property"><span class="token punctuation">[</span>NULL<span class="token punctuation">]</span></span>  <span class="token atrule">"GRANT NDB_STORED_USER ON *.* TO `jon`@`localhost`"</span></code></pre>
  </div>
  <p>
   <code class="literal">
    ndb_sql_metadata
   </code>
   is a special
   <code class="literal">
    NDB
   </code>
   table that is not visible using the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   or other MySQL client.
  </p>
  <p>
   A statement granting the
   <a class="link" href="privileges-provided.html#priv_ndb-stored-user">
    <code class="literal">
     NDB_STORED_USER
    </code>
   </a>
   privilege, such as
   <code class="literal">
    GRANT NDB_STORED_USER ON *.* TO
      'cluster_app_user'@'localhost'
   </code>
   , works by directing
   <code class="literal">
    NDB
   </code>
   to create a snapshot using the queries
   <code class="literal">
    SHOW CREATE USER cluster_app_user@localhost
   </code>
   and
   <code class="literal">
    SHOW GRANTS FOR cluster_app_user@localhost
   </code>
   ,
      then storing the results in
   <code class="literal">
    ndb_sql_metadata
   </code>
   .
      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
   <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
    <code class="literal">
     CREATE USER
    </code>
   </a>
   and
   <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
    <code class="literal">
     GRANT
    </code>
   </a>
   statements as part of the
      cluster schema synchronization process.
  </p>
  <p>
   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
   <code class="literal">
    NDBCLUSTER
   </code>
   storage engine; this
      is done within a security environment equivalent to that of the
      MySQL replication replica applier thread.
  </p>
  <p>
   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.
  </p>
  <p>
   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
   <a class="link" href="drop-database.html" title="15.1.24 DROP DATABASE Statement">
    <code class="literal">
     DROP DATABASE
    </code>
   </a>
   statement follows a
   <a class="link" href="drop-user.html" title="15.7.1.5 DROP USER Statement">
    <code class="literal">
     DROP USER
    </code>
   </a>
   of a distributed user,
      the drop of the database cannot take place until the drop of the
      user has completed on all SQL nodes.
  </p>
  <p>
   In the event that multiple
   <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
    <code class="literal">
     GRANT
    </code>
   </a>
   ,
   <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
    <code class="literal">
     REVOKE
    </code>
   </a>
   , 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
   <code class="literal">
    GRANT NDB_STORED_USER
   </code>
   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.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    NDB Cluster 8.4 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
    <code class="literal">
     NDB
    </code>
    storage engine as was done in older
        releases (NDB 7.6 and earlier—see
    <a class="ulink" href="/doc/refman/5.7/en/mysql-cluster-privilege-distribution.html" target="_top">
     Distributed Privileges Using Shared Grant Tables
    </a>
    ).
   </p>
  </div>
 </div>
 <br/>
</div>
