How mysql removes users completely 01/17 Update SLTechnology News&Howtos

How mysql removes users completely

2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

Editor to share with you how to completely delete mysql users, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

I encountered a strange problem before. After deleting a user in mysql.user and flush privileges, after building the same user, I found that the permissions were incorrect. Only later did I find out. It's mysql.db.

Generally speaking, after deleting a user's insert permission to a library, delete the user, and after flush privileges, the user with the same name and host is given update permission to the same library or other libraries, and it is found that the previously deleted insert permission appears again.

The reason is that there is the operation right to store the database in mysql.db. Although the user is deleted in mysql.user, the permission is not deleted. So when mysql loads user permissions, it loads permissions that have not been deleted before. If it is given *. * permission. Will not record to the db table, do not understand why, continue to study.

A rough test was made. It is as follows:

If the authorization is *. *, it will not be recorded in the mysql.db table, as in the following example:

Mysql > grant select on *. * to tes222@'%' identified by '1111111'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from db where user = 'tes222'

Empty set (0.00 sec)

Mysql > grant delete on *. * to tes222@'%'

Query OK, 0 rows affected (0.05 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from db where user = 'tes222'

Empty set (0.00 sec)

If the authorization is a fixed library name, the following is recorded:

Mysql > grant delete on test.* to tes222@'%'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from db where user = 'tes222'

+- -+ -+

+- -+ -+

| |% | test | tes222 | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N |

+- -+ -+

1 row in set (0.00 sec)

Mysql > delete from mysql.user where user = 'tes222'

Query OK, 1 row affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from db where user = 'tes222'

+- -+ -+

| |% | test | tes222 | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N |

+- -+ -+

1 row in set (0.00 sec)

Mysql > grant insert on test.* to tes222@'%' identified by '111333'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql >

Mysql > select * from db where user = 'tes222'

+- -+ -+

| |% | test | tes222 | N | Y | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N |

+- -+ -+

1 row in set (0.00 sec)

Mysql > show grants for tes222@'%'

+-+

| | Grants for tes222@% |

+-+

| | GRANT USAGE ON *. * TO 'tes222'@'%' IDENTIFIED BY PASSWORD' * 5EB462FE941D41EF8FAB7467C66B5CEC646731A2' |

| | GRANT INSERT, DELETE ON `test`.* TO 'tes222'@'%' |

+-+

2 rows in set (0.00 sec)

After passing revoke, there is no data in mysql.db.

Mysql > show grants for tes222@'%'

+-+

| | Grants for tes222@% |

+-+

| | GRANT USAGE ON *. * TO 'tes222'@'%' IDENTIFIED BY PASSWORD' * 5EB462FE941D41EF8FAB7467C66B5CEC646731A2' |

| | GRANT INSERT, DELETE ON `test`.* TO 'tes222'@'%' |

+-+

2 rows in set (0.00 sec)

Mysql >

Mysql > revoke delete on test.* from tes222@'%'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from mysql.db where user = 'tes222'

+- -+ -+

| |% | test | tes222 | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N |

+- -+ -+

1 row in set (0.00 sec)

Mysql > revoke all privileges on test.* from tes222@'%'

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from mysql.db where user = 'tes222'

Empty set (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > show grants for tes222@'%'

+-+

| | Grants for tes222@% |

+-+

| | GRANT USAGE ON *. * TO 'tes222'@'%' IDENTIFIED BY PASSWORD' * 5EB462FE941D41EF8FAB7467C66B5CEC646731A2' |

+-+

So, personal advice is, if you want to completely delete user rights, you should first revoke, and then mysql.user in the delete user table.

The above is all the contents of the article "how to completely delete users from mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.