MySQL两种删除用户语句的区别(delete user和drop user)

Tip：

在MySQL中，我们经常需要创建用户和删除用户，创建用户时，我们一般使用create user或者grant语句来创建，create语法创建的用户没有任何权限，需要再使用grant语法来分配权限，而grant语法创建的用户直接拥有所分配的权限。在一些测试用户创建完成之后，做完测试，可能用户的生命周期就结束了，需要将用户删除，而删除用户在MySQL中一般有两种方法，一种是drop user，另外一种是delete from mysql.user，那么这两种方法有什么区别呢？我们这里通过例子演示。

delete from mysql.user

首先，我们看看delete from mysql.user的方法。我们创建两个用户用来测试，测试环境是MySQL5.5版本，用户名分别为yeyz@'%'和yeyz@'localhost',创建用户的语法如下：

mysql 15:13:12>>create user yeyz@'%' identified by '123456';Query OK, rows affected (. sec)mysql 15:20:01>>grant select,create,update,delete on yeyz.yeyz to yeyz@'%';Query OK, rows affected (. sec)mysql 15:29:48>>GRANT USAGE ON yeyz.yeyz TO 'yeyz'@localhost IDENTIFIED BY '123456';Query OK, rows affected (. sec)mysql--dba_admin@127...1:(none) 15:20:39>>show grants for yeyz@'%';+-----------------------------------------------------------------------------------------------------+| Grants for yeyz@%                                          |+-----------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'yeyz'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' || GRANT SELECT, UPDATE, DELETE, CREATE ON `yeyz`.`yeyz` TO 'yeyz'@'%'                 |+-----------------------------------------------------------------------------------------------------+

mysql 15:20:43>>delete from mysql.user where user='yeyz';Query OK, rows affected (. sec)mysql 15:21:40>>select user,host from mysql.user;+------------------+-----------------+| user       | host      |+------------------+-----------------+| dba_yeyz     | localhost    || root       | localhost    || tkadmin     | localhost    |+------------------+-----------------+ rows in set (. sec)

mysql 15:24:21>>show grants for yeyz@'%';+-----------------------------------------------------------------------------------------------------+| Grants for yeyz@%                                          |+-----------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'yeyz'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' || GRANT SELECT, UPDATE, DELETE, CREATE ON `yeyz`.`yeyz` TO 'yeyz'@'%'                 |+-----------------------------------------------------------------------------------------------------+ rows in set (0.00 sec)

mysql ::>>GRANT USAGE ON yeyz.yeyz TO 'yeyz'@localhost IDENTIFIED BY '123456';Query OK, rows affected (. sec)

[dba_mysql@tk-dba-mysql-stat-- ~]$ /usr/local/mysql/bin/mysql -uyeyz --socket=/data/mysql_4306/tmp/mysql.sock --port= -p -hlocalhostEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is Server version: 5.5.-log MySQL Community Server (GPL)Copyright (c) , , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql--yeyz@localhost:(none) 15:31:05>>select * from yeyz.yeyz;+------+| id  |+------+|  3 ||  4 ||  5 |+------+ rows in set (. sec)mysql--yeyz@localhost:(none) 15:31:16>>delete from yeyz.yeyz where id=;Query OK, row affected (. sec)mysql--yeyz@localhost:(none) 15:31:32>>select * from yeyz.yeyz;+------+| id  |+------+|  3 ||  4 |+------+ rows in set (. sec)

再开看看drop的方法删除用户

首先，我们删除掉刚才的那两个用户，然后使用show grants for语句查看他们的权限：

mysql ::>>drop user yeyz@'%';Query OK, rows affected (0.00 sec)mysql ::>>drop user yeyz@'localhost';Query OK, rows affected (0.00 sec)mysql ::>>mysql ::>>show grants for yeyz@'%';ERROR (): There is no such grant defined for user 'yeyz' on host '%'mysql ::>>show grants for yeyz@'localhost';ERROR (): There is no such grant defined for user 'yeyz' on host '192.168.18.%'

mysql ::>>GRANT SELECT ON *.* TO 'yeyz'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';Query OK, rows affected (. sec)

[dba_mysql@tk-dba-mysql-stat-10-104 ~]$ /usr/local/mysql/bin/mysql -uyeyz --socket=/data/mysql_4306/tmp/mysql.sock --port=4306 -p -hlocalhostEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is Server version: 5.5.19-log MySQL Community Server (GPL)Copyright (c) , , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql ::>>select * from yeyz.yeyz;+------+| id  |+------+|   ||   ||   |+------+ rows in set (0.00 sec)mysql ::>>update yeyz.yeyz set id= where id=;ERROR (): UPDATE command denied to user 'yeyz'@'localhost' for table 'yeyz'mysql ::>>create table test (id int);ERROR (D000): No database selectedmysql ::>>create table yeyz.test (id int);ERROR (): CREATE command denied to user 'yeyz'@'localhost' for table 'test'

结论：

当我们想要删除一个用户的时候，尽量使用drop user的方法删除，使用delete方法可能埋下隐患，下次如果创建同名的用户名时，权限控制方面存在一定的问题。

这个演示也解决了一些新手朋友们的一个疑问：为什么我的用户只有usage权限，却能访问所有数据库，并对数据库进行操作？这个时候，你需要看看日志，查询自己有没有进行过delete from mysql.user的操作，如果有，这个问题就很好解释了。

更多相关文章

随机推荐