normal user with create user privilege is able to drop system user (user with system_user).
Init and log in as root.
> create user `x`@`localhost` identified by 'x';
> grant create user on *.* to `x`@`localhost`;
> show grants for `x`@`localhost`;
+---------------------------------------------+
| Grants for x@localhost |
+---------------------------------------------+
| GRANT CREATE USER ON *.* TO `x`@`localhost` |
+---------------------------------------------+
> create user `y`@`localhost` identified by 'y';
> grant all on *.* to `y`@`localhost` with grant option;
> show grants for `y`@`localhost`;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for y@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 `y`@`localhost` |
| 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,INNODB_REDO_LOG_ENABLE,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,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `y`@`localhost` |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Then log in as x.
> drop user `y`@`localhost`;
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
> select user, host from mysql.user;
ERROR 1047 (08S01): WSREP has not yet prepared node for application use
Checking mysql error logs
[Note] [MY-000000] [Galera] Member 2(testroot-pxc-db-pxc-0) initiates vote on b06d7b05-78f3-11ec-b0a1-cbf142c465be:55,b03376314e1a8256: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation, Error_code: 1227;
2022-01-19T08:04:27.498737-00:00 0 [Note] [MY-000000] [Galera] Votes over b06d7b05-78f3-11ec-b0a1-cbf142c465be:55:
b03376314e1a8256: 1/3
Waiting for more votes.
2022-01-19T08:04:27.501355-00:00 0 [Note] [MY-000000] [Galera] Member 0(testroot-pxc-db-pxc-2) responds to vote on b06d7b05-78f3-11ec-b0a1-cbf142c465be:55,0000000000000000: Success
2022-01-19T08:04:27.501424-00:00 0 [Note] [MY-000000] [Galera] Votes over b06d7b05-78f3-11ec-b0a1-cbf142c465be:55:
0000000000000000: 1/3
b03376314e1a8256: 1/3
Waiting for more votes.
2022-01-19T08:04:27.501464-00:00 0 [Note] [MY-000000] [Galera] Member 1(testroot-pxc-db-pxc-1) responds to vote on b06d7b05-78f3-11ec-b0a1-cbf142c465be:55,0000000000000000: Success
2022-01-19T08:04:27.501504-00:00 0 [Note] [MY-000000] [Galera] Votes over b06d7b05-78f3-11ec-b0a1-cbf142c465be:55:
0000000000000000: 2/3
b03376314e1a8256: 1/3
Winner: 0000000000000000
2022-01-19T08:04:27.501613-00:00 742 [ERROR] [MY-000000] [Galera] Inconsistency detected: Inconsistent by consensus on b06d7b05-78f3-11ec-b0a1-cbf142c465be:55
and pxc node will restart with IST.
Checking from other pxc node, user y@localhost has indeed been dropped. Similarly when IST on original node has complete, y@localhost cannot be found and has been dropped.
drop user `y`@`localhost`
can be found in binlog too.