Access control scenario analysis of MySQL Server

I have previously introduced the MySQL Authorization Table Overview and access control for MySQL servers. Next I will continue to introduce "Access Control for MySQL servers ". The following is a specific scenario analysis.

Scenario 1: failed connection attempts

The user alessia will be rejected when connecting to the server. Because the host, user, and/or password do not match in the user table, user requests are rejected.

Scenario 2: The database permission in the user table is N, and the database permission in the db table is Y.

1. the user wj will be successful when trying to connect.

2. the user wj tries to execute the Select command on the database oats.

3. Check the user table on the server. The value of N corresponding to the Select command indicates that the table is rejected.

4. The server then views the db table. The value of the table item corresponding to the Select command is Y, that is, allow.

5. The request is successfully executed because the SELECT field value in the user's db table is Y.

Scenario 3: the database permission in the user table is Y, and the database permission in the db table is N.

1. the user wj will be successful when trying to connect.

2. the user wj tries to execute the Select command on the database oats.

3. Check the user table on the server. The value of the table item corresponding to the Select command is Y, that is, allow. Because the permissions granted in the user table are global, the request is successfully executed.

Scenario 4: The database permission in the user table is N, and the database permission in the db table is N.

1. the user wj will be successful when trying to connect.

2. the user wj tries to execute the Select command on the database oats.

3. Check the user table on the server. The value of the table item corresponding to the Select command is N, which indicates that the table is rejected.

4. The server will now view the db table. The value of the table item corresponding to the Select command is N, which indicates that the table is rejected.

5. The server will now look for the tables_priv and columns_priv tables. If the user's request meets the permissions granted in the table, access is allowed. Otherwise, the access will be rejected.

The tables_priv and columns_priv tables will be further detailed later.

Scenario 5: Let's assume that the following conditions are true:

The host field value of user wj in the user table is %.
The host field value corresponding to the user wj In the db table is null.
What will happen at this time?

1. the user wj tries to connect through a given host.

2. if the password is correct, the connection will be successful, because the user table indicates that as long as the user name wj is used to connect to the password, any (meaning represented by the character %) all Hosts are allowed.

3. the MySQL server will search for the db table. However, no host is specified here.

4. the MySQL server will now view the host table. If the database to be connected by the user and the host name of the user when the connection is established are in the host table, the user can execute commands according to the permissions listed in the host table. Otherwise, the user will not be able to execute the command, but will actually be unable to connect.

I believe that the above knowledge can give you a general understanding of the database authorization system, which also lays a solid foundation for more in-depth study of the database system, I hope this article will help you.