The purpose of dynamic privileges is not clear from your text, but it is to *replace* the SUPER user with fine grained privileges. SUPER itself is deprecated, but for backward compatibility is still granted by GRANT ALL. You’ve trimmed the output of your SQL statements, but you will actually get a warning when doing this:

mysql [localhost:8024] {root} ((none)) > revoke SUPER on *.* from secure_test@’localhost’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost:8024] {root} ((none)) > show warnings;
+———+——+———————————————-+
| Level | Code | Message |
+———+——+———————————————-+
| Warning | 1287 | The SUPER privilege identifier is deprecated |
+———+——+———————————————-+
1 row in set (0.00 sec)

The problem is the semantic “ALL”:
a) Should it allocate only the “new privileges”?
b) Should it allocate only the “old privileges”?
c) Should it allocate both types of privileges?

I don’t think the answer is that straight forward. In the end the MySQL team chose (c) for 8.0, and actually for TiDB we chose (b), but there were other factors that led to our decision. I assume later down the road we will both switch to (a).

One of the subtleties I like about dynamic privileges is that the names of the privileges are already “role-oriented”. So typically you can allocate just one privilege to a user (such as CLONE_ADMIN) and not have to figure out from a long list what is required. This helps prevent over-allocating privileges, which is a real issue in the privilege design of MySQL. It’s not always clear from error messages what privileges will be required for a certain operation.