The difference between two MySQL delete user statements (delete user and drop user)

The difference between two MySQL delete user statements (delete user and drop user)

Tip:

In MySQL, we often need to create and delete users. When creating users, we generally use create user or grant statements to create them. Users created by create syntax do not have any permissions, and you need to use grant syntax to assign permissions. Users created by grant syntax directly have the assigned permissions. After some test users are created and tested, the user's life cycle may end and the user needs to be deleted. There are generally two ways to delete users in MySQL, one is drop user and the other is delete from mysql.user . So what is the difference between these two methods? We demonstrate this through examples here.

delete from mysql.user

First, let's look at delete from mysql.user method. We create two users for testing. The test environment is MySQL version 5.5. The user names are yeyz@'%' and yeyz@'localhost'. The syntax for creating users is as follows:

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)
[email protected]:(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'@'%' |
+---------------------------------------------------------------------------------------------------------------------+

At this point we manually delete the two users in the mysql.user table by using the delete method. When we check the user table, we find:

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)

There are no longer any yeyz users. We use the show grants for command to view the user we just deleted, and we find that the permission description of this user still exists:

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)

This means that although we have deleted this user from the mysql.user table, this user still exists in the db table and the permission table. To verify this conclusion, we recreate a user named yeyz@localhost. We only give this user usage permissions, and do not configure other permissions, as follows:

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

At this time, we use the user yeyz@localhost to log in to the database service, and then perform related update operations, as follows:

[dba_mysql@tk-dba-mysql-stat-- ~]$ /usr/local/mysql/bin/mysql -uyeyz --socket=/data/mysql_4306/tmp/mysql.sock --port= -p -hlocalhost
Enter 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 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--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)

The final result is predictable. A user with usage permissions performed an update operation on the database table, and it was successful. All this is thanks to our delete from mysql.user operation. Although this operation deletes the record from the user table, when the host of this record is %, if a new user with the same name is created, the new user will inherit the previous user permissions, making the user permission control invalid. This is a very dangerous operation and should be avoided as much as possible.

Open it again and see how to delete a user using the drop method

First, we delete the two users and then use the show grants for statement to view their permissions:

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.%'

As you can see, the permissions have been completely deleted. Now we recreate a user with only select permissions:

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

We use this user to log in to the database service, and then try to perform select, update, and create operations. The results are as follows:

[dba_mysql@tk-dba-mysql-stat-10-104 ~]$ /usr/local/mysql/bin/mysql -uyeyz --socket=/data/mysql_4306/tmp/mysql.sock --port=4306 -p -hlocalhost
Enter 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 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 ::>>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 selected
mysql ::>>create table yeyz.test (id int);
ERROR (): CREATE command denied to user 'yeyz'@'localhost' for table 'test'

It can be found that this user can only perform select operations. When we try to perform update and create operations, the system determines that there is no permission for such operations and directly rejects them. This means that when the drop user method is used to delete a user, the db table and the permission table will be cleared together. In other words, the deletion is relatively clean and will not have any impact on future users.

in conclusion:

When we want to delete a user, try to use the drop user method. Using the delete method may cause hidden dangers. If you create a user with the same name next time, there will be certain problems with permission control.

This demonstration also solves a question for some novice friends: Why can my user, who only has usage permissions, access all databases and perform operations on them? At this time, you need to check the log to see if you have performed the delete from mysql.user operation. If so, this problem is easy to explain.

The above is the detailed content of the difference between the two MySQL user deletion statements (delete user and drop user). For more information about MySQL user deletion, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • mysql creates root users and ordinary users and modify and delete functions
  • MySQL creates users, authorizes users, revokes user permissions, changes user passwords, and deletes users (practical tips)
  • mysql5.7 create user authorization delete user revoke authorization
  • MySql installation and configuration method (MySQL add users, delete users and authorization)
  • Initialize MySQL users (delete anonymous users)
  • User authorization and authorization deletion methods in MySQL
  • Create, authorize, delete, and modify passwords of mysql users in the WIN command prompt
  • Solve the mysql user deletion bug

<<:  UDP connection object principle analysis and usage examples

>>:  The specific use and difference between attribute and property in Vue

Recommend

javascript implements web version of pinball game

The web pinball game implemented using javeScript...

Summary of the differences between MySQL storage engines MyISAM and InnoDB

1. Changes in MySQL's default storage engine ...

Solve the MySQL login 1045 problem under centos

Since the entire application needs to be deployed...

MySQL export of entire or single table data

Export a single table mysqldump -u user -p dbname...

The concept of MySQL tablespace fragmentation and solutions to related problems

Table of contents background What is tablespace f...

MySQL 4G memory server configuration optimization

As the number of visits to the company's webs...

How to install Chrome browser on CentOS 7

This article introduces how to install Chrome bro...

Detailed explanation of monitoring NVIDIA GPU usage under Linux

When using TensorFlow for deep learning, insuffic...

Detailed process of modifying hostname after Docker creates a container

There is a medicine for regret in the world, as l...

Detailed explanation of simple html and css usage

I will use three days to complete the static page...

Parameters to make iframe transparent

<iframe src="./ads_top_tian.html" all...

Vue's Render function

Table of contents 1. Nodes, trees, and virtual DO...