Bug #98895 mysql 8 ndbcluster distributed privileges failure
Submitted: 10 Mar 17:28 Modified: 25 Apr 12:29
Reporter: no no Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:mysql-8.0.19 ndb-8.0.19 OS:CentOS (7)
Assigned to: MySQL Verification Team CPU Architecture:Any

[10 Mar 17:28] no no
Description:
This may be a documentation issue or something else.

After following https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-privilege-distribution.html, I attempted to setup distributed privileges as recommended with:
GRANT NDB_STORED_USER ON *.* TO 'admin'@'%'

This created an entry in the ndb_sql_metadata of:
11	"'admin'@'%'"	0	2	"CREATE USER 'admin'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$MGBmokOj

But when the 2nd SQL node attempts to sync the privileges, it encounters an error:
2020-03-09T15:46:20.046387Z 2 [ERROR] [MY-010865] [Server] NDB: Query 'CREATE USER IF NOT EXISTS 'admin'@'%'' failed, error: 1819: Your password does not satisfy the current policy requirements

How to repeat:
create a basic cluster and then follow the steps listed: https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-privilege-distribution.html

Suggested fix:
unknown
[23 Mar 20:32] MySQL Verification Team
node1 [localhost:28920] {root} ((none)) > select @@version;
+----------------+
| @@version      |
+----------------+
| 8.0.19-cluster |
+----------------+
1 row in set (0.00 sec)

node1 [localhost:28920] {root} ((none)) > CREATE USER 'bugtest'@'localhost'
    -> IDENTIFIED WITH 'caching_sha2_password' BY 'K0mp!3xPa55W0Rd'
    -> REQUIRE NONE
    -> PASSWORD EXPIRE DEFAULT
    -> ACCOUNT UNLOCK
    -> PASSWORD HISTORY DEFAULT
    -> PASSWORD REUSE INTERVAL DEFAULT
    -> PASSWORD REQUIRE CURRENT DEFAULT;
Query OK, 0 rows affected (0.01 sec)

node1 [localhost:28920] {root} ((none)) > GRANT ALL PRIVILEGES ON test.* to 'bugtest'@'localhost';
Query OK, 0 rows affected (0.00 sec)

node1 [localhost:28920] {root} ((none)) > GRANT NDB_STORED_USER ON *.* to 'bugtest'@'localhost';
Query OK, 0 rows affected (0.14 sec)

node1 [localhost:28920] {root} ((none)) > \q
Bye
[arhimed@localdev ndb_msb_ndb8_0_19]$ ./n1 -ubugtest -p test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.19-cluster MySQL Cluster Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

node1 [localhost:28920] {bugtest} (test) > \q
Bye
[arhimed@localdev ndb_msb_ndb8_0_19]$ ./n2 -ubugtest -p test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19-cluster MySQL Cluster Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

node2 [localhost:28921] {bugtest} (test) > \q
Bye
[arhimed@localdev ndb_msb_ndb8_0_19]$ ./n3 -ubugtest -p test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19-cluster MySQL Cluster Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

node3 [localhost:28922] {bugtest} (test) > \q
Bye
[arhimed@localdev ndb_msb_ndb8_0_19]$
[23 Mar 20:33] MySQL Verification Team
Hi,

Please use valid password and do not use sample, public, password from the documentation. 

Good health
Bogdan
[24 Mar 14:54] no no
Hello Bogdan,

I tried replicating your steps, but got the same issue.

--------------------
mysql node 1:

mysql> CREATE USER 'artifactory'@'%' IDENTIFIED WITH 'caching_sha2_password' BY 'K0mp!3xPa55W0Rd';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE DATABASE artifactorydb;
Query OK, 1 row affected (0.29 sec)

mysql> GRANT ALL PRIVILEGES ON artifactorydb.* to 'artifactory'@'%';
Query OK, 0 rows affected (0.06 sec)

mysql> GRANT NDB_STORED_USER ON *.* to 'artifactory'@'%';
Query OK, 0 rows affected (0.34 sec)

---------------------
mysql node2:

~$ tail -n 50 /var/log/mysqld.log

2020-03-24T14:49:13.458063Z 2 [ERROR] [MY-010865] [Server] NDB: Query 'CREATE USER IF NOT EXISTS 'artifactory'@'%'' failed, error: 1819: Your password does not satisfy the current policy requirements
2020-03-24T14:49:13.459309Z 2 [ERROR] [MY-010865] [Server] NDB: Query 'ALTER USER 'artifactory'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005^DbEC^Rx^D8x^Su\r3b:*qrz32jmkkQyiDlQiiJtvnSoK3D6hBqY6CImnOTBwKr8D' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT' failed, error: 1396: Operation ALTER USER failed for 'artifactory'@'%'
2020-03-24T14:49:13.459842Z 2 [ERROR] [MY-010865] [Server] NDB: Query 'GRANT USAGE ON *.* TO `artifactory`@`%`' failed, error: 1410: You are not allowed to create a user with GRANT
2020-03-24T14:49:13.460311Z 2 [ERROR] [MY-010865] [Server] NDB: Query 'GRANT NDB_STORED_USER ON *.* TO `artifactory`@`%`' failed, error: 1410: You are not allowed to create a user with GRANT
2020-03-24T14:49:13.460848Z 2 [ERROR] [MY-010865] [Server] NDB: Query 'GRANT ALL PRIVILEGES ON `artifactorydb`.* TO `artifactory`@`%`' failed, error: 1410: You are not allowed to create a user with GRANT

could you provide any suggestions?
[25 Mar 5:42] MySQL Verification Team
Hi,

This is weird and by investigating this we found a few other possible bugs.

Looks like "all privileges" automatically include the ndb_stored_user too.

I am still failing to reproduce this bug but our team did so... I'm verifying it

Thanks for the report.

good health
Bogdan
[25 Mar 12:29] MySQL Verification Team
Hi,

While we found some issues around this we still can't reproduce the original issue.

How did you install and configure the SQL nodes?
Are you sure your SQL nodes are using binaries from the NDB package?
What does the mmg show states:

ndb_mgm> show;
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @127.0.0.1  (mysql-8.0.19 ndb-8.0.19, Nodegroup: 0, *)
id=3    @127.0.0.1  (mysql-8.0.19 ndb-8.0.19, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @127.0.0.1  (mysql-8.0.19 ndb-8.0.19)

[mysqld(API)]   4 node(s)
id=4    @127.0.0.1  (mysql-8.0.19 ndb-8.0.19)
id=5    @127.0.0.1  (mysql-8.0.19 ndb-8.0.19)
id=6    @127.0.0.1  (mysql-8.0.19 ndb-8.0.19)
id=7 (not connected, accepting connect from localhost)

ndb_mgm>

What does status from mysqld show?

node1 [localhost:28920] {msandbox} ((none)) > status
--------------
/home/arhimed/opt/mysql/ndb8.0.19/bin/mysql  Ver 8.0.19-cluster for linux-glibc2.12 on x86_64 (MySQL Cluster Community Server - GPL)

Connection id:          10
Current database:
Current user:           msandbox@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.19-cluster MySQL Cluster Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /tmp/mysql_sandbox28920.sock
Binary data as:         Hexadecimal
Uptime:                 2 min 54 sec

Threads: 3  Questions: 61  Slow queries: 0  Opens: 159  Flush tables: 4  Open tables: 42  Queries per second avg: 0.350
--------------

node1 [localhost:28920] {msandbox} ((none)) >

Thanks
Bogdan
[26 Apr 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[7 May 10:30] Richard Cruise
Hi Bogdan,

I am encountering a similar issue when using the NBD_STORED_USER privilege on MySQL cluster. I have provided the information you requested below:

Description of cluster
3 Nodes, each runs as a management, data and MySQL API node
Node1 - 192.168.16.33
Node2 - 192.168.16.34
Node3 - 192.168.16.35

On Node 1:
mysql> create user 'joe'@'localhost' identified by 'Welcome1!';
Query OK, 0 rows affected (0.01 sec)

mysql> grant ndb_stored_user on *.* to 'joe'@'localhost';
Query OK, 0 rows affected (0.16 sec)

mysql>

On Node 3, I see the following error in the mysqld.log
2020-05-06T15:42:41.942878Z 2 [ERROR] [MY-010865] [Server] NDB: Query 'CREATE USER IF NOT EXISTS 'joe'@'localhost'' failed, error: 1819: Your password does not satisfy the current policy requirements
2020-05-06T15:42:41.943846Z 2 [ERROR] [MY-010865] [Server] NDB: Query 'ALTER USER 'joe'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$^%C`L\r@2h\ZFuV1lUXU1mtzH0KFo5Jrk8CaW7JO2liID19K9EbW7jTmjW8' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT' failed, error: 1396: Operation ALTER USER failed for 'joe'@'localhost'
2020-05-06T15:42:41.944499Z 2 [ERROR] [MY-010865] [Server] NDB: Query 'GRANT USAGE ON *.* TO `joe`@`localhost`' failed, error: 1410: You are not allowed to create a user with GRANT
2020-05-06T15:42:41.945250Z 2 [ERROR] [MY-010865] [Server] NDB: Query 'GRANT NDB_STORED_USER ON *.* TO `joe`@`localhost`' failed, error: 1410: You are not allowed to create a user with GRANT

The password requirements match across all 3 MySQL nodes and if I use the same password to setup the user on Node 3 it succeeds.

It seems that the NDB_STORED_USER privilege is failing due to some issue regarding password requirements:

MySQL Cluster version
[root@mysql-cluster-mn03 ~]# rpm -qa | grep mysql
mysql-cluster-community-server-8.0.19-1.el7.x86_64
mysql-cluster-community-client-8.0.19-1.el7.x86_64
mysql-cluster-community-libs-compat-8.0.19-1.el7.x86_64
mysql-cluster-community-libs-8.0.19-1.el7.x86_64
mysql-cluster-community-management-server-8.0.19-1.el7.x86_64
mysql-cluster-community-common-8.0.19-1.el7.x86_64
mysql-cluster-community-data-node-8.0.19-1.el7.x86_64
[root@mysql-cluster-mn03 ~]#

MySQL cluster config:
[root@mysql-cluster-mn03 ~]# cat /var/lib/mysql-cluster/config.ini
[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=3 # Number of replicas

[ndb_mgmd]
# Management process options:
HostName=192.168.16.33 # Hostname or IP address of MGM node
DataDir=/var/lib/mysql-cluster # Directory for MGM node log files
NodeId=1
[ndb_mgmd]
# Management process options:
HostName=192.168.16.34 # Hostname or IP address of MGM node
DataDir=/var/lib/mysql-cluster # Directory for MGM node log files
NodeId=2
[ndb_mgmd]
# Management process options:
HostName=192.168.16.35 # Hostname or IP address of MGM node
DataDir=/var/lib/mysql-cluster # Directory for MGM node log files
NodeId=3

[ndbd]
# Options for data node "A":
# # (one [ndbd] section per data node)
HostName=192.168.16.33 # Hostname or IP address
NodeId=16 # Node ID for this data node
DataDir=/usr/local/mysql/data # Directory for this data node's data files

[ndbd]
# Options for data node "B":
HostName=192.168.16.34 # Hostname or IP address
NodeId=17 # Node ID for this data node
DataDir=/usr/local/mysql/data # Directory for this data node's data files

[ndbd]
# Options for data node "C":
HostName=192.168.16.35 # Hostname or IP address
NodeId=18 # Node ID for this data node
DataDir=/usr/local/mysql/data # Directory for this data node's data files

[mysqld]
# SQL node options:
HostName=192.168.16.33 # Hostname or IP address
NodeId=32 # Node ID for this api node

[mysqld]
# SQL node options:
HostName=192.168.16.34 # Hostname or IP address
NodeId=33 # Node ID for this api node

[mysqld]
# SQL node options:
HostName=192.168.16.35 # Hostname or IP address
NodeId=34 # Node ID for this api node
[root@mysql-cluster-mn03 ~]#

MySQL Status Node 1

mysql> status
--------------
mysql  Ver 8.0.19-cluster for Linux on x86_64 (MySQL Cluster Community Server - GPL)

Connection id:		13
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.19-cluster MySQL Cluster Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/lib/mysql/mysql.sock
Binary data as:		Hexadecimal
Uptime:			19 hours 3 min 38 sec

Threads: 3  Questions: 23  Slow queries: 0  Opens: 165  Flush tables: 4  Open tables: 40  Queries per second avg: 0.000
--------------

mysql>
[7 May 14:33] MySQL Verification Team
Hi Richard,

I cannot reproduce this. I can't set that simple password in the first place. If you do relax the password policy differently on mysql servers weird things could happen but that I do not consider a bug.

in good health
Bogdan
[7 May 15:27] Richard Cruise
Hi Bogdan,

The password Welcome1! should satisfy the default requirements for MySQL server and the policy is the same on all 3 MySQL nodes. In any case we've tried more complex passwords without success

Node 1 where user was created:
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql> 

Nodes 2&3 where errors are occurring:

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

mysql>