关于ERROR 1142 (42000): CREATE command denied to user ‘xxx‘@‘yyy‘ for table ‘zzz‘的一个奇怪解决方法

今天遇到了一个奇怪的问题，
给 一个用户 赋 create 的权限，
mysql> grant create on schoolDB.* to 'zhang'@'192.168.231.%';
Query OK, 0 rows affected (0.00 sec)

登录，创建表出错，
[root@localhost ~]# mysql -u zhang -h 192.168.231.129 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.

mysql> create table t3(s int,sname char(10));
ERROR 1142 (42000): CREATE command denied to user ‘zhang’@‘192.168.231.129’ for table ‘t3’
尝试多次，一直报错，

于是退出 MySQL ，重新登录后，再创建表就没有问题了，不知道问题出在哪里。个人猜测，是登录后的用户权限保存在当前会话中，如果权限发生了变化，必须退出当前会话，再次登录才能生效，
mysql> create table t4(sid int,sname char(10));
Query OK, 0 rows affected (0.05 sec)

特此记录一下，
补充，刚才又做了2个实验，验证了，给一个用户赋予权限后，需要这个用户退出MySQL 并重新登录，才能使获得的权限生效，

例子一如下（不带时间戳的），
mysql> drop table t3;
ERROR 1142 (42000): DROP command denied to user ‘zhang’@‘192.168.231.129’ for table ‘t3’
mysql> select * from db\G
*************************** 1. row ***************************
Host: 192.168.231.%
Db: schoolDB
User: zhang
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: N

mysql> grant drop on schoolDB.* to 'zhang'@'192.168.231.%';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from db\G
*************************** 1. row ***************************
Host: 192.168.231.%
Db: schoolDB
User: zhang
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t3;
ERROR 1142 (42000): DROP command denied to user ‘zhang’@‘192.168.231.129’ for table ‘t3’
mysql> exit
Bye
[root@localhost ~]# mysql -u zhang -h 192.168.231.129 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| schoolDB |
±-------------------+
2 rows in set (0.00 sec)
mysql> use schoolDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> drop table t3;
Query OK, 0 rows affected (0.02 sec)

例子二，带时间戳和会话名称，

会话一：

mysql> create table w(sin int,sname char(1));
Query OK, 0 rows affected (0.03 sec)
mysql> describe w;
±------±--------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±--------±-----±----±--------±------+
| sin | int(11) | YES | | NULL | |
| sname | char(1) | YES | | NULL | |
±------±--------±-----±----±--------±------+
2 rows in set (0.00 sec)
mysql> drop table w;
ERROR 1142 (42000): DROP command denied to user ‘zhang’@‘192.168.231.129’ for table ‘w’
mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2022-01-28 13:17:17 |
±--------------------+
1 row in set (0.00 sec)

会话二：

mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2022-01-28 13:17:30 |
±--------------------+
1 row in set (0.00 sec)
mysql> select * from db\G
*************************** 1. row ***************************
Host: 192.168.231.%
Db: schoolDB
User: zhang
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: N
mysql> grant drop on schoolDB.* to 'zhang'@'192.168.231.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from db\G
*************************** 1. row ***************************
Host: 192.168.231.%
Db: schoolDB
User: zhang
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2022-01-28 13:25:58 |
±--------------------+
1 row in set (0.00 sec)

切到
会话一：

mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2022-01-28 13:27:01 |
±--------------------+
1 row in set (0.00 sec)
mysql> drop table w;
ERROR 1142 (42000): DROP command denied to user ‘zhang’@‘192.168.231.129’ for table ‘w’
mysql> exit
Bye
[root@localhost ~]# mysql -u zhang -h 192.168.231.129 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.1.66 Source distribution

Copyright © 2000, 2012, 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> use schoolDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table w;
Query OK, 0 rows affected (0.02 sec)

January the 28th 2022 Friday