MySQL Study--mysql Ordinary users cannot log on locally
After the installation has completed MySQL, we usually add 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 MySQL client can log on.
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 '--
---Can log in without a password, but no access, should be anonymous user identity!
Remote Login:
---remote login success!
one, after login to view the situation of the Mysql.user table
as you can see, there are rose users and anonymous user localhost in my database;
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 the machine with Rose user login, found that no password can be logged 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 logged on.
The current_user () function returns which user in the MySQL Use authorization table authenticates your login request.
It is found here that I use the ' Rose ' @ ' localhost ' account to log in to the database (because the host is not specified on the local login), but the database uses the ' @ ' localhost ' account for login authentication, and ' @ ' localhost ' This anonymous user does not have a password, so I entered a blank password to login successfully. However, after logging in, the corresponding user's anonymous user.
generally in MySQL after installation, we use mysql_install_db this script to generate authorization table, will default to create ' @ ' localhost ' this anonymous user. It is because of this anonymous user that it affects the authentication of other users who log on locally.
So how does MySQL perform user authentication?
When a user requests a login from a client, MySQL compares the entries in the authorization table with the entries provided by the client, including the user's user name, password, and host. The host field in the authorization table can be matched using wildcards as a pattern, such as test.example.com,%.example.com,%.com, and% can all match test.example.com. The user field in the authorization table does not allow pattern matching, but you can have a null character that represents an anonymous user, and an empty string can match all user names, 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, according to a certain sequence of rules, authenticates the client by the entry of the first matching client user name and host name read by the collation.
Second, collation: For the host field, sorted by the exact degree of matching, the more accurate the ordering, for example, when matching test.example.com this host,%.example.com than%.com more accurate, And test.example.com is more accurate than%.example.com. For the user field, a non-empty string user name is sorted more forward than an empty string that matches the user name. Both the user and host fields have multiple matching values, and MySQL uses the host name to sort the first entry, and then selects the entry with the user name sorted before the host name field is the same. Therefore, if both the user and host fields have multiple matching values, the entries with the most exact match for the host name are authenticated by the user.
Knowing the authentication process, you know why the server login failed.
when using game to log on to the data, do not specify the-h parameter defaults to localhost login, and there are two matching entries in MySQL: ' Rose ' @ '% ' and ' @ ' localhost '
an anonymous user can match the reason above said that an empty string can match all user names, just like a wildcard character.
according to the collation of MySQL authentication, the first one is to sort the user name more, the second one is more accurate, and the other is sorted before.
MySQL would prefer to use the first name in order to authenticate the user, so ' @ ' localhost is authenticated by the client. Therefore, only the empty password of the anonymous user is used to log in to the database. There will be a situation like the following.
Workaround: Delete anonymous users (this is necessary only for security)
Why does the root user not be affected, and only ordinary users cannot log on locally?
because the mysql_install_db script generates a ' root ' @ ' localhost ' account in the authorization table. Similarly, when using root to log in to MySQL, ' root ' @ ' localhost ' and ' @ ' localhost ' can match the logged-in account, but according to the collation, the hostname is the same, and the user name is not empty string first, so ' root ' @ ' localhost ' This entry 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:
[ro[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!
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL study--mysql Ordinary users cannot log on locally
