MySQL study--mysql Ordinary users cannot log on locally

MySQL Study--mysql Ordinary users cannot log on locally
      after MySQL is installed, we usually join a regular user with the appropriate permissions to access the database. When using the user to log in to the database, often there is how to log in and cannot log in, but from other mysqlclient can log in.

failure Phenomena:
[email protected] ~]# mysql-u root-poracle
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is
Server version:5.6.25-enterprise-commercial-advanced-log MySQL Enterprise server-advanced Edition (commercial)
Copyright (c), Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
affiliates. Other names trademarks of their respective
owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> Select version () \g
+-------------------------------------------+
| version () |
+-------------------------------------------+
| 5.6.25-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in Set (0.00 sec)

create a user and authorize：
mysql> Grant all on prod.* to ' Rose ' @ ' percent ' identified by ' Rose ';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for Rose;
+-----------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE on * * to ' rose ' @ '% ' identified by PASSWORD ' *86F57026C60B8CE1038EFB3B9383EC573979A7BD ' |
| GRANT all Privileges "prod". * to ' rose ' @ '% ' |
+-----------------------------------------------------------------------------------------------------+
2 rows in Set (0.00 sec)

mysql> Select user,host from user;

+-------+-----------+| User  | host      |+-------+-----------+| Jerry |%         | | Rose  |% | |         Tom   |% |         | tom1  |%         | | t Om2  |%         | | root  | 127.0.0.1 | | root  |:: 1       | |       | localhost | | jerry | localhost | | root  | localhost | | Scott | localhost | | Tom   | localhost | |       | mysrv     | | root  | mysrv     |+-------+-----------+14 rows in Set (0.00 sec)

User login:
[email protected] ~]# mysql-u rose-prose
ERROR 1045 (28000): Access denied for user ' rose ' @ ' localhost ' (using Password:yes)---landing failed!
[email protected] ~]# mysql-u rose-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is
Server version:5.6.25-enterprise-commercial-advanced-log MySQL Enterprise server-advanced Edition (commercial)
Copyright (c), Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
affiliates. Other names trademarks of their respective
owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement. mysql> use prod;
ERROR 1044 (42000): Access denied for user "@ ' localhost ' to database ' prod '--

---without password to be able to login, but no permission to visit, should be the identity of anonymous users!

Remote Login:

---remote login success!
one, after login to view the situation of the Mysql.user table

can see that my database has rose user and anonymous user localhost;
Mysql> show grants for Rose;
+-----------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE on * * to ' rose ' @ '% ' identified by PASSWORD ' *86F57026C60B8CE1038EFB3B9383EC573979A7BD ' |
| GRANT all Privileges "prod". * to ' rose ' @ '% ' |
+-----------------------------------------------------------------------------------------------------+
2 rows in Set (0.00 sec)

second, in this machine with Rose user login. Found without password to be able to log in.
[email protected] ~]# mysql-urose-p
Enter Password:

Mysql> Select User (), current_user ();
+----------------+----------------+
| User () | Current_User () |
+----------------+----------------+
| [Email protected] | @localhost |
+----------------+----------------+
1 row in Set (0.00 sec)

login successful, use User () and current_user () two functions to see the users you are using.
the USER () function returns the username and hostname that you specified when the client landed.

The Current_User () function returns which user in the MySQL Use authorization table authenticates your login request.
I found that I used the ' rose ' @ ' localhost ' account to log into the database (because I didn't specify a host when I landed locally.) The default is to log in as localhost), but the database uses the ' @ ' localhost ' account for login authentication. and ' @ ' localhost ' This anonymous user is not password, so I entered the empty password login succeeded. However, after logging in, the corresponding user's anonymous user.

Generally in MySQL after the installation is complete, we use mysql_install_db this script to generate authorization table, will default to create ' @ ' localhost ' this anonymous user.

It is this anonymous user that affects the authentication of other users who log on locally.
So how does MySQL authenticate the user?

When a user requests a login from a client, MySQL will compare the entries in the authorization table with the entries provided by the client, including the user's Username,password and host. The host field in the authorization table is capable of matching using wildcard characters as a pattern. such as test.example.com,%.example.com,%.com and% are able to match test.example.com this host.

The user field in the authorization table does not agree with pattern matching, but a username that can have a null character represents an anonymous user. And an empty string can match all username. Just like a wildcard character.

When host and user in the user table have multiple values that match the host and username provided by the client, MySQL reads the user table into memory and sorts them according to certain rules. The client is authenticated according to the entry of the first matching clientusername and hostname read by the collation.

Second, collation: For the host field, according to the exact degree of matching to sort. The more precise the ordering is, the more accurate the%.example.com is than the%.com when it matches the test.example.com host, and test.example.com is more accurate than%.example.com. For the user field, a non-empty string username The username sort that matches the empty string.

Both the user and host fields have multiple matching values, and MySQL uses the host name to sort the first entry, and then select the username before the host name field.

Therefore, assuming that both the user and host fields have multiple matching values, the entries that match the hostname most precisely are authenticated by the user.

Knowing the authentication process, you know why the server login failed.
When using the game to log on to the data, do not specify the-H-parameter default to the localhost login, and there are two matching entries in MySQL: ' Rose ' @ '% ' and ' @ ' localhost '
Anonymous users can match the reason above said that an empty string can match all username, just like a wildcard character.
According to the collation of MySQL authentication, the first article username the order of the previous. The second article is more precise in the host name. Sort more before.
MySQL would prefer to use the first entry in the hostname sort to authenticate. Therefore, ' @ ' localhost ' is authenticated by the user to the client.

Therefore, only the empty password talent that uses anonymous users is logged into the database.

There will be a situation like the following.
Workaround: Delete the anonymous user (only for security and this is necessary)
Why is the root user not affected, and only ordinary users cannot log on locally?
Because the mysql_install_db script generates ' root ' @ ' localhost ' account in the authorization table.

The same. When you log in to MySQL with root, ' root ' @ ' localhost ' and ' localhost ' can match the logged-in account, but the host name is the same according to the collation. The username non-empty string takes precedence. So the ' root ' @ ' localhost ' item is sorted more forward.

Using root to log on locally is not obscured by anonymous users.

Workaround:
authorized rose users to log in locally:mysql> Grant all on prod.* to ' Rose ' @ ' localhost ' identified by ' Rose ';
Query OK, 0 rows affected (0.01 sec)
login from Local:[email protected] ~]# mysql-u rose-prose
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 26
Server version:5.6.25-enterprise-commercial-advanced-log MySQL Enterprise server-advanced Edition (commercial)
Copyright (c), Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.
mysql> use prod;
Database changed
Mysql> Show tables;
+----------------+
| Tables_in_prod |
+----------------+
| T1 |
+----------------+
1 row in Set (0.00 sec)

Mysql> select * from T1;
+------+-------+
| ID | name |
+------+-------+
| 10 | Tom |
| 20 | Jerry |
| 30 | Rose |
+------+-------+
3 Rows in Set (0.00 sec)

---landing success!

MySQL study--mysql Ordinary users cannot log on locally