MySQL database Security Configuration Guide (2) [figure] _ MySQL

MySQL database Security Configuration Guide (2) [figure] 2) MySQL authorization table running mechanism

MySQL has two steps:

A) the server checks whether the user is allowed to connect.

B) if the user has the right to connect, the server will also check whether each of its requests has sufficient permissions. For example, if you want to retrieve a table in a database, you must have the select permission for the database. to delete a table in the database, you must have the drop permission for the database.

The user, db, and host tables in the authorization table use these two steps. tables_priv and columns_priv tables only use step 2 (check the request ). Each authorization table contains a range column that determines when a permission is applied and a permission column that determines which permission is granted.

The range column specifies when permissions in the table are applied. Each authorization table entry contains the User and Host columns to specify when permissions are applied to a connection from a given User to a given Host. Other tables contain additional range columns. for example, a db table contains a Db column to indicate which database the permission applies. Similarly, tables_priv and columns_priv tables contain a range Field to narrow down to a specific table in a database or a specific column in a table.

The following are examples of the combination of the Host field and user field in the User table:

Figure 9

The SQL string wildcard % indicates matching any character. it can be 0 characters, and the wildcard _ indicates matching a character.

The permission column specifies the permissions of the user specified in the range column. This table uses the permission name of the GRANT statement. The names of most permission columns in the user, db, and host tables are obviously related to the GRANT statement. For example, Select_priv corresponds to SELECT permission.

3) example of using the authorization table

Grant is used to add and create permissions for users, and revoke is used to delete user permissions.

Here are some examples of using grant to add users and create permissions:

Mysql> grantallprivilegeson *. * totest @ localhostidentifiedby 'test' withgrantoption;

Add a test user (super user) with all local permissions, and the password is test. *. * In the ON clause indicates "all databases, all tables ". With grant option indicates that it has the grant permission.
Mysql> grantselect, insert, update, delete, create, dropprivilegesontest. * totest1 @ '192. 168.1.0/255.255.255.0 'identifiedby 'test ';

This statement adds a test1 user with the password test, but it can only be connected from the class C subnet 192.168.1. for the test database, select, insert, update, delete, create, drop operation permission.