软件测试 | DROP TABLE命令并不回收以前的相关访问授权

更多学习资料戳！！！

DR,OP 表的时候，其他用户对此表的权限并没用被收回，这样导致重新创建同名的表时，以前其他用户对此表的权限会自动赋予，进而产生 权限外流。因此，在删除表时，要同时取消其他用户在此表上的相应权限。

下面的例子说明了不收回相关访问授权的隐患。

（1）用root创建用户z1，授权对test1下所有表的select权限：

mysql> grant select on test1.* to z1@localhost;
Query OK, 0 rows affected (0.00 sec) 
mysql> show grants for z1@localhost; 
+-----------------------------------------------+ 
| Grants for z1@localhost | 
+-----------------------------------------------+ 
| GRANT USAGE ON *.* TO 'z1'@'localhost' | 
| GRANT SELECT ON `test1`.* TO 'z1'@'localhost' | 
+-----------------------------------------------+ 
2 rows in set (0.00 sec)

（2）z1登录，测试权限：

[root@localhost test1]# mysql -uz1 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 43 
Server version: 5.0.41-community-log MySQL Community Edition (GPL) 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
mysql> use test1 
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> show tables; 
+-----------------+ 
| Tables_in_test1 | 
+-----------------+ 
| t1 | 
| t12 | 
| t2 | 
+-----------------+ 
3 rows in set (0.00 sec)

（3）root 登录，删除表 t1：

[root@localhost test1]# mysql -uroot 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 
[root@localhost test1]# mysql -uroot -p123 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 45 
Server version: 5.0.41-community-log MySQL Community Edition (GPL) 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
mysql> use test1 
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 t1; 
Query OK, 0 rows affected (0.00 sec) 
mysql> exit 
Bye

（4）z1登录，再次测试权限:

[root@localhost test1]# mysql -uz1 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 46 
Server version: 5.0.41-community-log MySQL Community Edition (GPL) 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
mysql> use test1 
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> show tables; 
+-----------------+ 
| Tables_in_test1 | 
+-----------------+ 
| t12 | 
| t2 | 
+-----------------+ 
2 rows in set (0.00 sec)

（5）此时 t1 表已经看不到了。

mysql> show grants for z1@localhost; 
+-----------------------------------------------+ 
| Grants for z1@localhost | 
+-----------------------------------------------+ 
| GRANT USAGE ON *.* TO 'z1'@'localhost' | 
| GRANT SELECT ON `test1`.* TO 'z1'@'localhost' | 
+-----------------------------------------------+ 
2 rows in set (0.00 sec)

权限仍然显示对test1下所有表的SELECT（安全漏洞）。

（6）root再次登录，创建t1条：

[root@localhost test1]# mysql -uroot -p123 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 48 
Server version: 5.0.41-community-log MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
mysql> create table t1(id int); 
Query OK, 0 rows affected (0.03 sec) 
mysql> exit

（7）z1登录，对t1权限依旧存在：

[root@localhost test1]# mysql -uz1 test1 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 49 
Server version: 5.0.41-community-log MySQL Community Edition (GPL) 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
mysql> show tables; 
+-----------------+ 
| Tables_in_test1 | 
+-----------------+ 
| t1 | 
| t12 | 
| t2 | 
+-----------------+ 
3 rows in set (0.00 sec) 
mysql> select * from t1; 
Empty set (0.00 sec)

注意：对表做删除后，其他用户对此表的权限不会自动收回，一定记住要手工收回。