Unique MySQL user Mechanism

MySQL's unique user mechanism is confusing for beginners of MySQL. MySQL user names are not as simple as MS-SQL and Oracle databases. Similar to the firewall, it also includes the login limit mechanism. For example, for the following three MySQL user names: 1. dba @ 'localhost' -- dba can only log on from the Local Machine 2. dba @ '%' -- dba can access MySQL server from any machine in the network. 3. dba @ '2017. 168.1.201 '-- dba can access MySQL Server MySQL from 192.168.1.201! If we create a user: dba @ 'localhost' dba @ '192. 168.1.201 ', the dba user can only connect to the MySQL server from the local machine and the machine with the IP address 192.168.1.201. The two dba users can have different permissions and passwords. This is undoubtedly an important guarantee for MySQL database security. Note: 1. For create user dba, it is equivalent to create user dba @ '% '. Mysql> user mysql; mysql> select * from user; + ----------- + ---------- + login | Host | User | Password + ----------- + ---------- + login | localhost | root | * login + ----------- + ---------- + login mysql> create user dba identified by 'dbaany '; mysql> select * From user; + ----------- + ---------- + hosts | Host | User | Password + ----------- + ---------- + hosts | localhost | root | * drivers | % | dba | * supervisor + ----------- + ---------- + supervisor 2, the user dba @ 'localhost' and db exist simultaneously in the MySQL database. A @ '%' indicates that when accessing MySQL locally, the user should be dba @ 'localhost'; When remotely accessing MySQL, the user environment should be dba @ '%' mysql> create user dba @ 'localhost' identified by 'dbalocal'; mysql> select * from user; + ----------- + ---------- + ----------------------------------------- | Host | User | Password + ------------- + ---------- + login | localhost | root | * login | localhost | d Ba | * forbidden | % | dba | * forbidden + ----------- + ---------- + --------------------------------------------- C: \> mysql-u dba-p Enter password: dbaany ERROR 1045 (28000 ): access denied for user 'dba '@ 'localhost' (using password: YES) C: \> mysql-u dba-p Enter password: dbalocal Welcome to the MySQL monitor. commands end with; or \ g. mys Ql> 3. when accessing the MySQL database, you only need to specify the user name dba, instead of the user's full name dba @ 'localhost' or dba '% '. C: \> mysql-u dba @ 'localhost'-p Enter password: dbalocal ERROR 1045 (28000 ): access denied for user 'dba @ 'localhost' (using password: YES) C: \> mysql-u dba @ '%'-p Enter password: dbaany ERROR 1045 (28000): Access denied for user 'dba @ '% ''@ 'localhost' (using password: YES) C: \> mysql-u dba-p Enter password: dbalocal Welcome to the MySQL monitor. mysql> 4. Specify the host parameter when Remotely accessing the MySQL database. Mysql> create user dba @ '2017. 168.1.110 'identified by 'dba110 '; mysql> select * from user; + --------------- + ---------- + login | Host | User | Password + --------------- + ---------- + login | localhost | root | * login | localhost | dba | * login | 192.168.1.110 | Dba | * supervisor | % | dba | * supervisor + --------------- + ---------- + ----------------------------------------------- C :\> mysql-h 192.168.1.110-u dba-p Enter password: dba110 Welcome to the MySQL monitor. mysql> 5. Pay attention to mysql. the order of user DBAs in the user table, although the order in which we create dba users is: 1. dba @ '%' 2. dba @ 'localhost' 3. dba @ '2017. 168.1.110 '. However, MySQL uses different sequence when processing user login. + --------------- + ---------- + | Host | User | + ----------------- + ---------- + | localhost | dba | 192.168.1.110 | dba | % | dba | + --------------- + ---------- + If the dba User is on the local machine login, mySQL first tries to use the dba @ localhost user. If the user does not exist, MySQL selects the dba @ '%' user to complete the login. If the dba user logs on remotely, MySQL first tries to use dba @ '100. 168.1.110 'user to log on. If the ip address does not match, MySQL will use dba @ '%' to log on. Of course, if dba @ '%' does not exist, the logon fails.