Shulou(Shulou.com)06/01 Report--

The following mainly brings you the permission principle of MySQL and how to delete the MySQL anonymous account. I hope the permission principle of MySQL and how to delete the MySQL anonymous account can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

The working principle of MySQL privilege system

The MySQL permission system is authenticated through the following two stages:

(1) authenticate the connected users. Legitimate users pass authentication, while illegal users refuse to connect.

(2) the legitimate users who have passed the authentication are given corresponding permissions, and the users can do the corresponding operations on the database within the scope of these permissions.

For identity authentication, MySQL is confirmed by a combination of IP address and user name. For example, the user root@localhost created by default after MySQL installation indicates that user root can only be authenticated by connecting locally (localhost), and the connection made by this user to the database from any other host will be denied. That is, if the same user name comes from a different IP address, MySQL treats it as a different user.

The permission table of MySQL is loaded into memory when the database is started, and when the user is authenticated, the corresponding permissions are accessed in memory, so that the user can do all kinds of operations within the scope of authority in the database. Therefore, flush privileges; will not take effect until a modification has been made to the user.

In the two processes of permission access, the system will use the three most important permission tables, user, host and db, in the "mysql" database (created when installing MySQL and the database name is "mysql"). Of the three tables, the most important table is the user table, followed by the db table, and the host table is not used in most cases. The columns in user are mainly divided into four parts: user column, permission column, security column, and resource control column.

When the user connects, the access process of the permission table has the following two stages.

First, from the host, user and password fields in the user table, determine whether the connected IP, user name and password exist in the table, and if so, pass authentication, otherwise reject the connection.

If authenticated, the database permissions are obtained in the following order: user- > db- > tables_priv- > columns_priv.

In these permission tables, the scope of permissions decreases in turn, and the global permissions cover the local permissions.

The Host value can be the hostname or IP number, or "localhost" indicates the local host.

You can use the wildcard characters "%" and "_" in Host column values.

The Host value'% 'matches any hostname, and an empty Host value is equivalent to'%'. They have the same meaning as the pattern matching operation of the LIKE operator. For example, the host value of "%" matches all hostnames, while ".mysql.com" matches all hosts in the mysql.com domain.

If the host in the permissions table has both "thomas.loc.gov" and "%", the connection comes from the host thomas.loc.gov. Obviously, the two records in the user table match the criteria, so which one will the system choose?

If there are multiple matches, the CVM must choose which entry to use. Solve the problem in accordance with the following principles:

L the CVM sorts after reading the user table at startup

L then browse the entries in sorted order when the user tries to connect

The CVM uses the first line that matches the client and user name.

When the cloud server reads the table, it is first sorted by the most specific host value. The hostname and IP number are the most specific. "%" means "any host" and is the least specific. Entries with the same host value are sorted first by the most specific user value (an empty user value means "any user" and is the least specific).

Before sorting: +-| Host | User | … +-|% | root |... |% | jeffrey | … | | localhost | root | … | | | localhost | | … | +-sort: +-| Host | User | … +-| localhost | root | … ... | localhost | |... ... |% | jeffrey |... ... |% | root |... ... +-

Delete anonymous users:

Mysql version 5.6.18

View users

Mysql > select user,host,plugin,password,authentication_string,password_expired from mysql.user +- -+ | user | host | plugin | password | authentication_string | password_expired | +-+- -+-- +-+ | root | localhost | | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | N | | root | rhel7 | | | | N | | root | 127.0.0.1 | | N | | root |:: 1 | | N | | localhost | NULL | N | rhel7 | | | NULL | N | | zx |% | mysql_native_password | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | N | +-| -+

Users who do not exist can also log in to MySQL

[root@rhel7 mysql5.6.18] #. / bin/mysql-uaWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 16Server version: 5.6.18-enterprise-commercial-advanced MySQL Enterprise Server-Advanced Edition (Commercial) Copyright (c) 2000, 2014, 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 >

Delete a user whose user is empty

Mysql > delete from mysql.user where user='';Query OK, 2 rows affected (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec) mysql > select user,host,plugin,password,authentication_string,password_expired from mysql.user +- -+ | user | host | plugin | password | authentication_string | password_expired | +-+- -+-- +-+ | root | localhost | | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | N | | root | rhel7 | | | | N | | root | 127.0.0.1 | | N | | root |:: 1 | | N | | zx |% | mysql_native_password | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | N | +-| -+-+-- +-+ 5 rows in set (0.00 sec)

Users who do not exist can no longer log in to MySQL

[root@rhel7 mysql5.6.18] #. / bin/mysql-uaERROR 1045 (28000): Access denied for user'a beautiful localhost'(using password: NO)

For the above about the principle of MySQL permissions and how to delete MySQL anonymous account, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.