Linux Learning Note: MySQL Licensing

Authorization, when the user is created, you need to authorize the user, MySQL is interesting in that it is for the user and host in pairs to authorize. That is, the same user is from a different host, and the permissions may be different.

GRANT SELECT, INSERT on ' mydb '. ' Student ' to ' coosh ' @ ' 192.168.5.0/255.255.255.0 ' identified by ' Coosh ';

Note that there is a space between the SELECT and the insert, as well as the wording of the IP network segment, which cannot be used in the 24 notation. The difference between this notation and 192.168.5.% is that% matches any, that is, 192.168.5.% equals 192.168.5.0/255.255.255.0, but if the subnet mask is not standard, For example 255.255.255.128, then the entire 192.168.5.0 will be divided into two network segments, then the use of 192.168.5.0/255.255.255.128 this writing, Can match to 192.168.5.1-192.168.5.126

After the authorization command above, look at the MySQL table

Mysql> select user,host,password from mysql.user where user= ' Coosh '; +-------+ ---------------------------+-------------------------------------------+| user  | host                        | password                                    |+-------+---------------------------+------------------------------------------ -+| coosh | 127.0.0.1                  | *94bdcebe19083ce2a1f959fd02f964c7af4cfc29 | |  coosh | 192.168.5.%                | *91a66b6c7d4fabdb9225374dc006bb98e6c6f0cb | |  coosh | 192.168.5.0/255.255.255.0 | *4747e0bbc01404d0d09bd8a9ba1ae4f5fc5fdc94 | +-------+---------------------------+-------------------------------------------+

3 Rows in Set (0.00 sec)

You can see that the Coosh user has several different passwords, and after testing, MySQL takes precedence over the lines that specify the subnet mask.

Connect MySQL with Coosh This user through another machine in LAN

mysql> INSERT into student, values (1, ' Mr coosh ', 100); Query OK, 1 row affected (0.02 sec) mysql> SELECT * from student;+----+----------+-------+| ID | name |  Score |+----+----------+-------+| 1 |   Mr Coosh | |+----+----------+-------+1 row in Set (0.00 sec) mysql> Delete from student where id=1; ERROR 1142 (42000): DELETE command denied to user ' coosh ' @ ' 192.168.5.141 ' for table ' student '

You can see that Coosh can insert a new record or query, but it cannot be deleted (prompting the command to be rejected)

Back to the server

[[email protected] ~]# mysql -u root -p -h 127.0.0.1 -p  3308enter password: type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> show grants for [email protected];+------------------------ --------------------------------------------------------------------------------------+| grants for  [email protected]                                                                                       |+---------------------------------------------------------------------------------------------------- ----------+| grant usage on *.* to  ' coosh ' @ ' 127.0.0.1 '  identified by  PASSWORD  ' *94bdcebe19083ce2a1f959fd02f964c7af4cfc29 '  | |  GRANT ALL PRIVILEGES ON  ' mydb ' .* to  ' coosh ' @ ' 127.0.0.1 '                                                          | |  GRANT ALL PRIVILEGES ON  ' test ' .* to  ' coosh ' @ ' 127.0.0.1 '                                                          |+------------------------------------------------------------------------------------------------ --------------+3 rows in set  (0.00 SEC)

You can see that if you use Coosh to log in on the server, you have all the command permissions

Test it.

[Email protected] ~]# mysql-ucoosh-p-H 127.0.0.1-p3308welcome to the MySQL monitor. Commands End With; or \g.mysql> delete from mydb.student where id=1; Query OK, 1 row Affected (0.00 sec)

Logging on the server can be successfully removed.

View authorizations

mysql> show grants for [email protected];+---------------------------------------- ----------------------------------------------------------------------+| grants for [email  protected]                                                                                       |+------------------------ --------------------------------------------------------------------------------------+| grant usage  ON *.* TO  ' Coosh ' @ ' 127.0.0.1 '  IDENTIFIED BY PASSWORD  ' * 1981b3d87f8e4997e444ac2f469a74d5145eeb75 '  | |  GRANT ALL PRIVILEGES ON  ' mydb ' .* to  ' coosh ' @ ' 127.0.0.1 '                                                          |+------------------ --------------------------------------------------------------------------------------------+2 rows  in set  (0.00 sec) mysql> show grants for [email protected] ' 192.168.5.0/255.255.255.0 '; +---------------------------------------------------------------------------------- --------------------------------------------+| grants for [email protected]/255.255.255.0                                                                                        |+------------------------------------------------------------ ------------------------------------------------------------------+| grant usage on *.*  TO  ' Coosh ' @ ' 192.168.5.0/255.255.255.0 '  IDENTIFIED BY PASSWORD  ' * 1981b3d87f8e4997e444ac2f469a74d5145eeb75 '  | |  GRANT SELECT, INSERT ON  ' mydb ' .* to  ' coosh ' @ ' 192.168.5.0/255.255.255.0 '                                                         |+------------------------------------------------------------------------------------------------ ------------------------------+2 rows in set  (0.00 SEC)

Linux Learning Note: MySQL Licensing