MySQL permissions and delete MySQL's anonymous account

How the MySQL Privilege system works

the MySQL privilege system is certified through the following two phases:

(1) authenticating the user to theconnection, the legitimate user passes the authentication, the illegal user refuses the connection;

(2) to the legitimate users through authentication to give the appropriate permissions, the user can be within these permissions to the database to do the corresponding operation.

for identity authentication, MySQL is confirmed by a federated IP address and user name, such as a user created by default after MySQL installation [email protected] indicates user root Only local (localhost) connections can be authenticated, and this user's connection to the database from any other host is denied. That is, the same user name, if it comes from a different IP address, MySQL treats it as a different user.

MySQL 's permission table is loaded into memory when the database is started, and when the user is authenticated, the corresponding permission is accessed in memory, so that the user can do various operations within the scope of the database. Therefore, after the user has made the modification operation, flush privileges, before it will take effect.

In the two process of access, the system will use the "MySQL" database (created when MySQL was installed, the database name is "MySQL") in the user, host and DB of the 3 most important permissions table. of the 3 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 divided into 4 main sections: User columns, permission columns, security columns, and resource control columns.

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

• The connection's IP, user name, and password are determined from the 3 fields in the user table, whether they exist in the table, or if they are authenticated, otherwise they are denied.

• If authenticated, the database permissions are obtained in the order of the following permission tables: User->db->tables_priv->columns_priv.

In these permissions tables, the permission ranges are decremented, and the global permissions override the local permissions.

• The host value can be either a hostname or an IP number, or "localhost" indicates the local host.

• can be in The Host column value uses a wildcard character of "%" and "_".

• host host "match

if the host in the permission table has both "thomas.loc.gov" and "%", then the connection comes from the host thomas.loc.gov . Obviously,the two records in the User table match the matching criteria, which one will be selected by the system?

If there are multiple matches, the server must choose which entry to use. To address the following principles:

l The server is sorted after it is read into the user table at startup ;

l then, when the user tries to connect, browse the entries in sorted order;

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

When the server reads the table, it first sorts with the most specific host value. The host name and IP number are the most specific. "%" means "any host" and is the most non-specific. Entries with the same host value are first sorted by the most specific user value (the null user value means "any user" and is the least specific).

Before sorting:  +-----------+----------+-| host      | user     | ...+-----------+----------+-|%         |  root     | ...| %         | jeffrey  | ...| localhost | root     | ...| localhost |          | ...+-----------+----------+ + After sorting:  +-----------+----------+-| host      | user     | ...+-----------+-------- --+-|localhost | root     | ... ...| localhost |          | ......| %         |jeffrey  | ... ...| %         |root     | ... ...+-----------+----------+ + 

To delete an anonymous user:

MySQL version 5.6.18

View User

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                |+------+---- -------+-----------------------+-------------------------------------------+-----------------------+----------- -------+

You can also log in to MySQL using a non-existent user

[[email protected] 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 User blank

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 on to MySQL

[Email protected] mysql5.6.18]#/bin/mysql-uaerror 1045 (28000): Access denied for user ' a ' @ ' localhost ' (using password : NO)

Reference: "In layman's MySQL"

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1879606

MySQL permissions and delete MySQL's anonymous account