MySQL Bugs: #98895: mysql 8 ndbcluster distributed privileges failure

Bug #98895	mysql 8 ndbcluster distributed privileges failure
Submitted:	10 Mar 2020 17:28	Modified:	6 Nov 2020 5:02
Reporter:	no no	Email Updates:
Status:	Closed	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

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

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]$

Hi,

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

Good health
Bogdan

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?

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

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

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".

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>

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

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>

In the end it turned out that NDB and MySQL were using different data directories
By specifying the DataDir parameter for the ndbd nodes to match the MySQL data directory this resovled the issue