现象：

在最近测试中，发现即使用户没有super权限以及相应的动态权限（如SYSTEM_USER）,也可以通过grant语句为自己赋相应的动态权限。现象如下：

mysql> CREATE USER 'sf2'@'%' IDENTIFIED WITH mysql_native_password BY '123456';    # 创建一个新用户sf2
Query OK, 0 rows affected (0.01 sec)

mysql>  GRANT SELECT, INSERT, UPDATE, DELETE on *.* TO `sf2`@`%` WITH GRANT OPTION;   #为其赋全局的增删改查权限，注意此时并没有动态权限
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye
[sf@bssmysql028 ~]$ /data2/sf/mysql8/mysql-install/bin/mysql --socket=/tmp/mysql_8833.sock -usf2 -p   # 退出使用新用户sf登入
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18-teledb1.aarch64 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant SYSTEM_USER ON *.* to `sf2`@`%`;                       # 自己为自己赋没有的动态权限，成功
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.global_grants where USER ='sf2';         # 查看已经有了相应的动态权限
+---------------+-----------+----------------------------+-------------------+
| USER          | HOST      | PRIV                       | WITH_GRANT_OPTION |
+---------------+-----------+----------------------------+-------------------+
| sf2           | %         | SYSTEM_USER                | N                 |
+---------------+-----------+----------------------------+-------------------+
57 rows in set (0.00 sec)

这就非常违反常识也非常之危险了，采用最新的8.0.23现象依然存在。

分析：

下面是执行grant指令的调用堆栈

#0  validate_dynamic_privileges (this=0x7f4af8834020) at /data2/sf/mysql8/mysql_8.0.18_text/sql/auth/sql_authorization.cc:373
#1  validate (this=0x7f4af8834020) at /data2/sf/mysql8/mysql_8.0.18_text/sql/auth/sql_authorization.cc:474
#2  mysql_grant (thd=thd@entry=0x7f46600fca50, db=<optimized out>, list=..., rights=0, revoke_grant=<optimized out>, is_proxy=<optimized out>, dynamic_privilege=..., 
    grant_all_current_privileges=false, grant_as=0x7f4660058bf0) at /data2/sf/mysql8/mysql_8.0.18_text/sql/auth/sql_authorization.cc:3389
#3  0x0000000000e5f83d in mysql_execute_command (thd=thd@entry=0x7f46600fca50, first_level=first_level@entry=true) at /data2/sf/mysql8/mysql_8.0.18_text/sql/sql_parse.cc:3898
#4  0x0000000000e60170 in mysql_parse (thd=thd@entry=0x7f46600fca50, parser_state=parser_state@entry=0x7f4af8839510)
    at /data2/sf/mysql8/mysql_8.0.18_text/sql/sql_parse.cc:5259
#5  0x0000000000e62c72 in dispatch_command (thd=thd@entry=0x7f46600fca50, com_data=com_data@entry=0x7f4af8839c00, command=COM_QUERY)
    at /data2/sf/mysql8/mysql_8.0.18_text/sql/sql_parse.cc:1767
#6  0x0000000000e63868 in do_command (thd=thd@entry=0x7f46600fca50) at /data2/sf/mysql8/mysql_8.0.18_text/sql/sql_parse.cc:1275
#7  0x0000000000f7efd0 in handle_connection (arg=arg@entry=0x4cfd1c0) at /data2/sf/mysql8/mysql_8.0.18_text/sql/conn_handler/connection_handler_per_thread.cc:301
#8  0x00000000023d6ac5 in pfs_spawn_thread (arg=0x4b340f0) at /data2/sf/mysql8/mysql_8.0.18_text/storage/perfschema/pfs.cc:2854
#9  0x00007f4b0e47fdd5 in start_thread () from /lib64/libpthread.so.0
#10 0x00007f4b0c7a8ead in clone () from /lib64/libc.so.6

而动态权限的赋权检查是在validate_dynamic_privileges 函数中，通过分析该函数，可以看到如下代码：

/*
  Privilege to grant dynamic privilege to others is granted if the user
  either has super user privileges (currently UPDATE_ACL on mysql.*) or
  if the user has a GRANT_OPTION on the specific dynamic privilege he
  wants to grant.
  Note that this is different than the rules which apply for other
  privileges since for them the GRANT OPTION applies on a privilege
  scope level (ie global, db or table level).
  From a user POV it might appear confusing that some privileges are
  more strictly associated with GRANT OPTION than others, but this
  choice is made to preserve back compatibility while also paving way
  for future improvements where all privileges objects have their own
  grant option.
*/
if (check_access(m_thd, UPDATE_ACL, consts::mysql.c_str(), NULL, NULL, 1,
                 1) &&
    !sctx->has_global_grant(priv->str, priv->length).second) {
  my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "GRANT OPTION");
  return true;
}

从注释和代码都可可以看到，是否具有动态权限的赋权，主要看两个条件，满足任意一个都行。

有super权限（这里认为在mysql.*上有UPDATE_ACL就是拥有super权限了）

check_access(m_thd, UPDATE_ACL, consts::mysql.c_str(), NULL, NULL, 1,1)

用户具有相应动态权限的赋权能力

!sctx->has_global_grant(priv->str, priv->length).second)

所有这里可以看到在进行动态权限赋权检查时，具有super也可，而且super的判断是通过在mysql库上有update权限来进行的。分析到这里已经可以解释上述现象了。正式由于我们为新用户赋权了全局的update权限（包含mysql.*）,从而在动态权限检查时，认为已经具有super，从而grant语句执行成功。