How Mysql permission system works _ MySQL

How Mysql permission system works MySQL permission system ensures that all users can strictly perform what they are supposed to be permitted to do. When you connect to a MySQL server, your identity is determined by the host you connect to and the user name you specify. The system grants permissions based on your identity and what you want to do.

MySQL considers your host name and username in identity authentication because there is a small reason for assuming that a given user belongs to the same person on the Internet. For example, the user's bill from whitehouse.gov does not have to be the same person as the bill from mosoft.com. MySQL allows you to differentiate users with the same name on different hosts for processing: you can assign a permission set to bill from the whitehouse.gov connection, assign a different permission set for the connection from microsoft.com.

MySQL access control includes two phases:

Phase 1: The server checks whether you allow connection.

Phase 2: If you can connect, the server checks every request you send. Check whether you have sufficient permissions to implement it. For example, if you select a row from a table in the database or discard a table from the database, the server determines that you have the select permission on the table or drop permission on the database.

The server uses the user, db, and host tables in the mysql database in two phases of access control. The fields in these authorization tables are as follows:

Table name: user db host
Range field Host
User Db
Password User
Permission field Select_priv
Insert_priv
Update_priv
Delete_priv
Index_priv
Alter_priv
Create_priv
Drop_priv
Grant_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv

For the second stage of access control (request validation), if the request involves a table, the server can also refer to the tables_priv and columns_priv tables. The fields of these tables are as follows:

Table name tables_priv columns_priv
Range field Host
Db
User
Table_name
Column_name
Permission field Table_priv Column_priv
Column_priv
Other fields: Timestamp
Grantor

Each authorization table contains the range Field and permission field.

The range field determines the scope of each entry in the table, that is, the context of the entry. For example, if the Host and user values of a User table entry are ''Thomas .loc.gov ''and ''bob'', it is used to confirm the connection of bob from the Host thomas.loc.gov to the server. Similarly, the Host, User, and db fields of a Db table entry are set to ''Thomas .loc.gov '', ''bob'', and ''report'', which will be used by bob to connect thomas.loc.gov to access reports from the Host. database. The tables_priv and columns_priv tables contain a range field, indicating the table or table/column combination applicable to each entry.

For the purpose of checking access, comparing Host values is case-insensitive. User, Password, Db, and Table_name values are case sensitive. The Column_name value is case-insensitive in MySQL3.22.12 or later versions.