MySQL Bugs: #118907: The privileges of a user persisted by NDB_STORE_USER may be corrupted after upgrading from 8.0 to 8.4.

Bug #118907	The privileges of a user persisted by NDB_STORE_USER may be corrupted after upgrading from 8.0 to 8.4.
Submitted:	28 Aug 8:09	Modified:	9 Sep 13:11
Reporter:	ZHAO SONG	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	8.4.X	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL deprecated the SET_USER_ID privilege starting in 8.4, and this change conflicts with NDB’s current implementation of NDB_STORE_USER.

NDB implements NDB_STORE_USER by storing the corresponding plain SQL statements (such as CREATE USER, GRANT, etc.) in an internal table. On startup, MySQL replays these SQL statements to restore the users.

The issue occurs if a user is granted ALL (or explicitly SET_USER_ID) along with NDB_STORE_USER. In this case, the persisted SQL in the internal table includes SET_USER_ID. After upgrading to MySQL 8.4, the new version no longer recognizes this privilege and returns a Syntax error when applying the SQL. As a result, the persisted privileges cannot be restored.

During the upgrade process, the new mysqld binary does perform conversion of the DD and system tables, but these changes are local only. The persisted information in NDB remains incorrect. This means that any newly added mysqld instance, or any reinitialized mysqld, will fail to restore the grant information from NDB—defeating the purpose of using NDB_STORE_USER.

How to repeat:
1. Create a user and grant 'ALL' (which includes NDB_STORE_USER and SET_USER_ID) to it on MySQL 8.0.

2. Upgrade the cluster to MySQL 8.4.

3. Initialize mysqld and connect it to NDB.

4. Observe that the restored user from NDB_STORE_USER has incorrect privileges.

Suggested fix:
The quick solution is to handle compatibility when parsing the plain SQL. However, this isn’t 100% safe—you must ensure that the persisted privileges exactly match those supported by the corresponding MySQL version.

Thank you for the report.

Hi Zhao, thanks for the bug report.

Most likely something like below should take care of the problem, leaving the privileges in NDB as they are and just fixing up the SQL for "GRANT .. SET_USER_ID". You have some concern with that approach, can you describe more?

       case TYPE_GRANT:
+        // Handle upgrade of the SET_USER_ID privilege which has been split into
+        // ALLOW_NONEXISTENT_DEFINER and SET_ANY_DEFINER, then subsequently
+        // removed in 8.4.
+        {
+          static constexpr std::string_view target = "SET_USER_ID";
+          static constexpr std::string_view replacement =
+              "ALLOW_NONEXISTENT_DEFINER,SET_ANY_DEFINER";
+          size_t pos = 0;
+          while ((pos = statement.find(target, pos)) != std::string::npos) {
+            statement.replace(pos, target.length(), replacement);
+            pos += replacement.length();
+            ndb_log_info("Upgraded 'SET_USER_ID' privilege");
+          }
+        }

Hi Magnus,

My concern is that it’s hard to ensure the SQL stored in NDB always matches what’s in MySQL’s local DD. As far as I know, in MySQL 8.4 the 'ALL' privilege set also gained new privileges such as FLUSH_PRIVILEGES, OPTIMIZE_LOCAL_TABLE, and TRANSACTION_GTID_TAG. A proper fix would need to add and apply these when restoring from NDB_STORE_USER.
The risk is that future versions may add more privileges, making it difficult to keep the fix 100% consistent.

There seem to be two different concerns:

1) The first is that GRANT ALL is saved as the expanded set of provilges given by the version where the GRANT ALL was issued. Have compared to how GRANT ALL are stored in binlog and there it seems to be stored as GRANT ALL. Not sure wheter it would be correct to do that same thing for synchronized privileges.

2) Second case is GRANT <keyword> (for example SET_USER_ID) which is or has been removed in higher versions. Causes syntax error. Same problem applies for replication, when user specifies GRANT SET_USER_ID it is written to binlog and fails to replicate.

Suggested patch primarily fixes problem 2) which for synchronizd privileges is caused by the GRANT ALL being expaned (as desribed in case 1).

Hi Magnus,

I think the most reliable fix would be to apply the same upgrade logic to NDB_STORE_USER as MySQL does for its local privilege tables. The full privilege upgrade flow in MySQL is recorded in the generated mysql_fix_privilege_tables_sql.h file. When a new MySQL version starts up and performs an upgrade, it applies the rules from this .h file via the upgrade_system_schemas() → fix_mysql_tables() flow.

I believe NDB could reuse this .h file by adding a conversion layer to extract the relevant 'global_grants' table upgrade rules. Then, in the part of the code you mentioned earlier, those rules could be applied to overwrite the contents of NDB_STORE_USER. This would ensure that, regardless of how MySQL privileges evolve in future versions, the privileges stored in NDB would remain consistent and compatible with the upgraded MySQL version.

Hi Zhao,

The mysql_fix_privilege_tables_sql.h file is the "compiled" version of the SQL found in the scripts/mysql_system_tables_fix.sql, reading the original .sql file directly makes it easier to see how thing are fixed. 

There are a lot of fixes done by all those SQL commands and like you say some of them should be applied to the privileges stored in NDB.  

Unfortunately it does not really feel like attempting to run all those SQL commands by including the .h file is a viable solution.

For now, I'm focusing on handling the removed SET_USER_ID in a graceful way. Then we'll see how to tackle the other things.