0

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 '$A$005$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.

4
  • ok, finally getting somewhere, looked in /var/log/mysqld.log and the CREATE USER is failing on SQL Node 02 `` 2020-03-09T22: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 Commented Mar 10, 2020 at 17:04
  • 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. Commented Mar 10, 2020 at 17:19
  • 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. Commented Mar 10, 2020 at 21:56
  • 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 Commented Mar 24, 2020 at 7:09

1 Answer 1

0

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 '$A$005$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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.