MySQLStudy-MySQL common users cannot log on locally. after installing MySQL, we usually add common users with corresponding permissions to access the database. When a user logs on to the database locally, he or she often fails to log on to the database. However
MySQL Study-MySQL common users cannot log on locally
After installing MySQL, we usually add common users with corresponding permissions to access the database. When you log on to the database locally, you can log on from other mysql clients.
Fault symptom:
[Root @ mysrv ~] # Mysql-u root-poracle
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 10
Server version: 5.6.25-enterprise-internal cial-advanced-log MySQL Enterprise Server-Advanced Edition (commercial)
Copyright (c) 2000,201 2, 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 version () \ g
+ ------------------------------------------- +
| Version () |
+ ------------------------------------------- +
| 5.6.25-enterprise-defined cial-advanced-log |
+ ------------------------------------------- +
1 row in set (0.00 sec)
Create and authorize a user:
Mysql> grant all on prod. * to 'Rose '@' % 'identified by 'Rose ';
Query OK, 0 rows affected (0.01 sec)
Mysql> show grants for rose;
+ Shards +
| Grants for rose @ % |
+ Shards +
| Grant usage on *. * TO 'Rose '@' % 'identified by password' * 86f57026c60b8ce1038efb3b9383ec574509a7bd' |
| Grant all privileges on 'prod'. * TO 'Rose '@' % '|
+ Shards +
2 rows in set (0.00 sec)
Mysql> select user, host from user;
+-------+-----------+| user | host |+-------+-----------+| jerry | % || rose | % || tom | % || tom1 | % || tom2 | % || 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:
[Root @ mysrv ~] # Mysql-u rose-prose
ERROR 1045 (28000): Access denied for user 'Rose '@ 'localhost' (using password: YES) --- login failed!
[Root @ mysrv ~] # Mysql-u rose-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 22
Server version: 5.6.25-enterprise-internal cial-advanced-log MySQL Enterprise Server-Advanced Edition (commercial)
Copyright (c) 2000,201 2, 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> use prod;
ERROR 1044 (42000): Access denied for user ''@ 'localhost' to database' prod '--
--- You can log on without a password, but you do not have the permission to access it. it should be the identity of an anonymous user!
Remote login:
--- Remote login successful!
1. view the mysql. user table after logon
We can see that there are rose users and anonymous users localhost in my database;
Mysql> show grants for rose;
+ Shards +
| Grants for rose @ % |
+ Shards +
| Grant usage on *. * TO 'Rose '@' % 'identified by password' * 86f57026c60b8ce1038efb3b9383ec574509a7bd' |
| Grant all privileges on 'prod'. * TO 'Rose '@' % '|
+ Shards +
2 rows in set (0.00 sec)
2. log on with the rose user in the machine and find that you can log on without a password;
[Root @ mysql01 ~] # Mysql-urose-p
Enter password:
Mysql> select user (), current_user ();
+ ---------------- +
| User () | current_user () |
+ ---------------- +
| Rose @ localhost |
+ ---------------- +
1 row in set (0.00 sec)
After successful logon, use the USER () and CURRENT_USER () functions to view the users used.
The USER () function returns the USER name and host name you specified when logging on to the client.
The CURRENT_USER () function returns the user in the MySQL authorization table to authenticate your login request.
I found that I used the account 'Rose '@ 'localhost' to log on to the database (because the host is not specified during local login, the default logon is localhost ), however, the database uses the account ''@ 'localhost' for logon authentication, while the anonymous user'' @ 'localhost' does not have a password, therefore, I entered an empty password to log on successfully. But the anonymous user of the corresponding user after logon.
Generally, after MySQL is installed, we use the script mysql_install_db to generate an authorization table. by default, the anonymous user ''@ 'localhost' is created. It is precisely because this anonymous user affects the authentication of other users logging on from the local device.
So how does MySQL perform user identity authentication?
1. when a user requests a login from the client, MySQL compares the entries in the authorization table with the entries provided by the client, including the user name, password, and host. The Host field in the authorization table can be matched using wildcards as the mode. for example, test.example.com, % .example.com, %. com, and % can both match the Host test.example.com. The User field in the authorization table does not allow pattern matching. However, a username with null characters can represent anonymous users, and a null string can match all usernames, just like wildcards. When the Host and user values in the User table match the Host and user names provided by the client, MySQL reads the user table into the memory and sorts them according to certain rules, the first entry that matches the client user name and host name read by the sorting rule is used to authenticate the client.
II. sorting rules: for the Host field, sort by matching precision. the more accurate the field is, the more accurate the field is. for example, when the Host test.example.com is matched, the ratio of % .example.com to %. com is more accurate, while test.example.com is more accurate than % .example.com. For the User field, the User names of non-empty strings are sorted before the User names matching null strings. The User and Host fields have multiple matching values. MySQL uses the Host name to sort the first entry, and selects the username to sort the previous entry at the same time as the Host name field. Therefore, if the User and Host fields both have multiple matching values, the entries with the most precise matching Host name are authenticated by the User.
After learning about the authentication process, you can see why the server logon fails.
When using GaMe to log on to the local machine, if you do not specify the-h parameter, the default value is localhost. However, there are two matching entries in MySQL: 'rose '@' % 'and' @ 'localhost'
As mentioned above, an empty string can match all user names, just like wildcards.
According to the sorting rules during MySQL authentication, the first user name is sorted before, the second host name is more accurate, and the sorting is performed before.
MySQL uses the first entry in the list of hostnames for identity authentication, so ''@ 'localhost' is used to authenticate the client. Therefore, you can only log on to the database using an anonymous user's empty password. The following situations will occur.
Solution: delete anonymous users (only for security purposes)
Why can't root users be affected, but normal users cannot log on from the local device?
Because the mysql_install_db script will generate the 'root' @ 'localhost' account in the authorization table. Similarly, when using root to log on to MySQL, both 'root' @ 'localhost' and '@ 'localhost' can match the logon account, but according to the sorting rules, the host name is the same, the username is not a null string, so 'root' @ 'localhost' is sorted first. Root local logon is not overwritten by anonymous users.
Solution:
Authorize the rose user to log on locally:
Mysql> grant all on prod. * to 'Rose '@ 'localhost' identified by 'Rose ';
Query OK, 0 rows affected (0.01 sec)
Log on locally:
[Root @ mysrv ~] # 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-internal cial-advanced-log MySQL Enterprise Server-Advanced Edition (commercial)
Copyright (c) 2000,201 2, 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> 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)
--- Login successful!
