mysql-authentication process for user rights (RPM)

Knowledge pointsBecause MySQL uses the user and host two fields to determine the identity of users, this raises the question of which host a client belongs to.

• If a client matches several hosts at the same time, the user's determination is sorted by the following priority
  • 基本观点The more accurate the match the more first
  • Host column, the more determined the host the more first, [localhost, 192.168.1.1, wiki.yfang.cn] takes precedence over [192.168.%,%.yfang.cn], takes precedence over [192.%,%.cn], takes precedence over [%]
  • On the user column, the explicit username takes precedence over the empty username. (empty username matches all user names, that is, anonymous users match all users)
  • Host column takes precedence over user column considerations
  After you log in to the MySQL server, you can use User () and current_user () to check your logged in users.
  • User () returns the users and hosts you specified when you connected to the server
  • Current_User () returns the users and hosts that match in the Mysql.user table, which determines your permissions in the database
  When you log in to the server and execute the MySQL command, the system checks to see if your current user (Current_User) has permission to perform the current operation.
  • First, check the global permissions in the user table and perform the action if the condition is met
  • If the above fails, check that the Mysql.db table has permissions that meet the conditions, and if it does, perform the action
  • If the above fails, check the Mysql.table_priv and Mysql.columns_priv (if the stored procedure action checks Mysql.procs_priv) and if satisfied, perform the action
  • If all of the above checks fail, the system refuses to perform the operation.
  The test process creates 3 user names that are the same, host and permissions are different for user

• Mysql> Grant SELECT On * * to "@ '% ' identified by ' 123 '; Query OK, 0 rows Affected (0.00 sec) Mysql> Grant Select,createon * * to ' Bruce ' @ ' 10.20.0.232 ' identified by ' 123 '; Query OK, 0 rows affected (0.01 sec) mysql> Grant Select,create,deleteon * * to ' Bruce ' @ ' percent ' identified by ' 123 '; Query OK, 0rows Affected (0.00 sec)

  Landing from another machine.

• [[email protected] ~]# mysql-ubruce-p-h10.20.0.231enter password:welcome to the MariaDB Monitor. Commands End With; or \g.your MySQL connection ID is 5Server version:5.5.20-log mysql Community Server (GPL) This software comes with ABSOLUT ELY NO WARRANTY. This was free Software,and welcome tomodify and redistribute it under the GPL v2 licensetype ' help; ' or ' \h ' for He Lp. Type ' \c ' to clear the current inputstatement. MySQL [(None)]> show grants;+--------------------------------------------------------------------------------- ----------------------------------------+|                                                                                            Grants for [email protected] |+------------------------------------------------------------------------------------------------------------- ------------+| GRANT SELECT, Createon * * to ' Bruce ' @ ' 10.20.0.232 ' identified by PASSWORD ' *23ae809ddacaf96af0fd78ed04b6a265e05aa257 ' | +-------------------------------------------------------------------------------------------------------------------------+1 Row Inset (0.00 sec) MySQL [( None)]> Select User (), Current_User (), +-------------------+-------------------+| User () | Current_User () |+-------------------+-------------------+| [Email protected] | [email protected] |+-------------------+-------------------+1 row in Set (0.03 sec)

  Clear User,host, accurate match, find the user for ' Bruce ' @ ' 10.20.0.232 ' Delete this user and then login

• mysql> Delete from mysql.userwhereuser= ' Bruce ' andhost= ' 10.20.0.232 '; Query OK, 1row Affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec) [[email protected] ~]# mysql-ubruce-p-h10.20.0.231enter password:welcome to T  He MariaDB Monitor. Commands End With; or \g.your MySQL connection ID is 6Server version:5.5.20-log mysql Community Server (GPL) This software comes with ABSOLUT ELY NO WARRANTY. This was free Software,and welcome tomodify and redistribute it under the GPL v2 licensetype ' help; ' or ' \h ' for He Lp. Type ' \c ' to clear the current inputstatement. MySQL [(None)]>show grants;+--------------------------------------------------------------------------------- --------------------------------------+|                                                                                                    Grants for [email protected]% |+-----------------------------------------------------------------------------------------------------------------------+| GRANT SELECT, DELETE, createon*.* to ' Bruce ' @ '% ' identified Bypassword ' *23ae809ddacaf96af0fd78ed04b6a265e05aa257 ' |+-- --------------------------------------------------------------------------------------------------------------- ------+1 Row Inset (0.00 sec) MySQL [(None)]> Select User (), Current_User (), +-------------------+----------------+| User () | Current_User () |+-------------------+----------------+| [Email protected] | [email protected]% |+-------------------+----------------+1 row in Set (0.00 sec)

  This time the matching user is [email protected]%
  And then delete this user, and then log in

• [[email protected] ~]# mysql-ubruce-p-h10.20.0.231enter password:welcome to the MariaDB Monitor. Commands End With; or \g.your MySQL connection ID is 8Server version:5.5.20-log mysql Community Server (GPL) This software comes with ABSOLUT ELY NO WARRANTY. This was free Software,and welcome tomodify and redistribute it under the GPL v2 licensetype ' help; ' or ' \h ' for He Lp. Type ' \c ' to clear the current inputstatement. MySQL [(None)]> show grants;+--------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -----+|                                                                                                                                                                                       Grants for @% |+---------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------+|                                                                                                    GRANT SELECT on*.* to "@ '% ' identified by PASSWORD ' *23ae809ddacaf96af0fd78ed04b6a265e05aa257 ' ||  GRANT SELECT, INSERT, UPDATE, DELETE, create, DROP, REFERENCES, INDEX, ALTER, create temporary TABLES, LOCK TABLES, create View, SHOW view, Createroutine, EVENT, TRIGGER on ' test '. * to ' @ '% ' | | GRANT SELECT, INSERT, UPDATE, DELETE, create, DROP, REFERENCES, INDEX, ALTER, Createtemporary TABLES, LOCK TABLES, create View, SHOW view, Createroutine, EVENT, TRIGGER on ' test\_% '. * to ' @ '% ' |+---------------------------------------------- --------------------------------------------------------------------------------------------------------------- ----------------------------------------+mysql [(None)]> Select User (), Current_User (), +-------------------+--- -------------+| User () | Current_User () |+-------------------+----------------+| [Email protected] | @% |+-------------------+----------------+1 row in Set (0.00 sec)

  At this point, the match is ' @ '% ' user
• 
  For empty users, the default has permissions for databases that begin with test or test

Http://www.cnblogs.com/zuoxingyu/p/4935428.html

mysql-authentication process for user rights (RPM)