mysql 8 ndbcluster distributed privileges

Question

I've setup a mysql 8 ndbcluster but cannot get the distributed privileges working.

following how to setup distributed privileges using NDB_STORED_USER (https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-privilege-distribution.html), i've verified the following:

• SHOW ENGINES lists the value of "DEFAULT" for the Support column on the ndbcluster (Engine) row.
• Both SQL nodes have "default-storage-engine=ndbcluster" in their my.cnf

• I created a user on SQL node 1 and then added them to NDB_STORED_USER.

  mysql> GRANT NDB_STORED_USER ON *.* TO 'admin'@'%';
  Query OK, 0 rows affected (0.26 sec)
  

• I verified I could see the respective GRANTS in ndb_sql_metadata from BOTH SQL Nodes

  type name seq note sql_ddl_text
  11 "'admin'@'%'" 0 2 "CREATE USER 'admin'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A005ドル$MGBmokOj
   m!<=P9q7BhmH0ANKg1Fs3JGEv.ITwLdXa7KC0n2GOogn6Xc/' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT"
  12 "'admin'@'%'" 1 [NULL] "GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,NDB_STORED_USER,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `admin`@`%`"
  12 "'admin'@'%'" 0 [NULL] "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`%`"
  3 rows returned```
  

• I've restarted SQL Node 2, but I still do not see the new user (admin)

   mysql> select host, user from mysql.user;
  +-----------+------------------+
  | host | user |
  +-----------+------------------+
  | localhost | mysql.infoschema |
  | localhost | mysql.session |
  | localhost | mysql.sys |
  | localhost | root |
  +-----------+------------------+
  4 rows in set (0.00 sec)```
  

Any other suggestions? this is my first time setting up a mysql cluster.

• carefreepineapple: ok, finally getting somewhere, looked in /var/log/mysqld.log and the CREATE USER is failing on SQL Node 02 `` 2020年03月09日T22:00:32.889976Z 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
• carefreepineapple: Im not sure why as the password is more than complex enough, 30 characters, alphanumeric with symbols. If I were to take the log as it is written, it looks like the CREATE USER statement is incomplete causing the password policy error.
• carefreepineapple: so what I think is happening is that SQL node 02 is stripping everything after the IDENTIFIED BY in the CREATE USER command, so it then tries to create a user without a password, which isn't allowed.
• Ted Wennmark: I can not reproduce your problem, can you please share some more information? I would like to know how your have initialized your mysql servers, please share exact commands for initialization, my.cnf and start command. Also can you share the the command you used to create the initial "admin" user? Many thanks, Ted

Answer 1

I had same issue on 8.0.21 NDB Cluster. I was looking at the error log and trying to figure out what is wrong, and I saw this

NDB: Query 'CREATE USER IF NOT EXISTS 'admin'@'localhost'' failed, error: 1819: Your password does not satisfy the current policy requirements
NDB: Query 'REVOKE ALL ON *.* FROM 'admin'@'localhost'' failed, error: 1269: Can't revoke all privileges for one or more of the requested users
NDB: Query 'ALTER USER 'admin'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_USER_CONNECTIONS 0' failed, error: 1819: Your password does not satisfy the current policy requirements
NDB: Query 'ALTER USER 'admin'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A005ドル$Dzd+M c5Cnthnjcc^hEvdg6M.d3j9TWVcO6ze/6DBrNnW3SB/Qh7wn19XGI7' 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 'admin'@'localhost'

When I first create user on SQL Node 1 and grant NDB_STORED_USER to new user the second node has this log. So basically when new user created on one of the node the other nodes are trying to create it with different query. As you can see the log the second node tries to create admin@localhost user and then it tries to set password using ALTER USER. And due to validate_password policy plugin the second node cannot create user without password.

So I have disabled the validate_password plugin and created user again. After disabling the validate_password plugin the user created without any error. And the password privileges have been set on second node.