MySQL has an advanced but non-standard security/authorization system. mastering its authorization mechanism is the first step to start operating MySQL databases. For a person familiar with basic SQL operations, it is also a hard part of all MySQL knowledge. This section introduces the operating mechanism of its authorization system and hopes that you can better operate and use this excellent database system.
The MySQL security system is flexible and allows you to set user permissions in multiple ways. Generally, you can use the standard SQL statement GRANT and REVOKE to modify the authorization table for controlling customer access. However, you may have an earlier MySQL version that does not support these statements before 3.22.11.) or you may find that user permissions do not seem to work in the way you want. In this case, it is helpful to understand the structure of the MySQL authorization table and how the server uses them to determine access permissions, this understanding allows you to add, delete, or modify user permissions by directly modifying the authorization table. It also allows you to diagnose permission issues when checking these tables.
Structure of the MySQL authorization table
Access to the MySQL database by customers who connect to the server over the network is controlled by the authorization table content. These tables are located in the mysql database and initialized to run the mysql_install_db script during the first MySQL installation ). There are five authorization tables: user, db, host, tables_priv, and columns_priv.
Structure and Function of the authorization table user, db, and host
Table 1 Authorization Table user, db, and host structure
The authorization table has the following functions:
· User table
The user table lists the users that can connect to the server and their passwords, and specifies which global superusers they have) permissions. All permissions enabled in the user table are global permissions and apply to all databases. For example, if you have enabled the DELETE permission, the users listed here can DELETE records from any table, so you should consider it carefully before doing so.
· Db table
The database table lists the databases, and the user has the permission to access them. The permission specified here applies to all tables in a database.
· Host table
The host table and db table are used in combination to control the database access permissions of a specific host at a good level, which may be better than using the database separately. This table is not affected by the GRANT and REVOKE statements, so you may find that you are not using it at all.
Structure and Function of the tables_priv and columns_priv authorization tables
Table 2 authorization table tables_priv and columns_priv
Authorization TableTables_priv |
Authorization TableColumns_priv |
Scope Column |
Host |
Host |
Db |
Db |
User |
User |
Table_name |
Table_name |
|
Column_name |
Permission Column |
Table_priv |
Column_priv |
Other Columns |
Timestamp |
Timestamp |
Grantor |
|
MySQL does not have a rows_priv table because it does not provide record-level permissions. For example, you cannot restrict the rows in a table that contain specific column values. If you really need this capability, you must use application programming to provide it. If you want to execute the recommended record-Level Lock, you can use the GET_LOCK () function.
The authorization table has the following functions:
· Tables _ priv table
The tables_priv table specifies table-level permissions. The specified Permission applies to all columns in a table.
· Columns _ priv table
The columns_priv table specifies the column-level permission. The specified Permission applies to specific columns of a table.
The tables_priv and columns_priv tables are introduced in MySQL 3.22.11 with the GRANT Statement at the same time ). If you have an earlier version of MySQL, your mysql database will only have user, db, and host tables. If you upgrade from an earlier version to 3.22.11 or update without the tables_priv and columns_priv tables, run the mysql_fix_privileges_tables script to create them.
User Permissions
Permission information is stored in the mysql database using the user, db, host, tables_priv, and columns_priv tables ). When MySQL is started and when 7.5 permission changes take effect, the server reads the contents of these database tables.
Database and table Permissions
The following permissions apply to database and table operations.
· SELECT
You can use the SELECT statement to retrieve data from a table. SELECT statements require select permission only when they actually retrieve rows from a table. You can execute a SELECT statement without any permission to access anything in the database on the server. For example, you can use a mysql client as a simple calculator:
Mysql> SELECT 1 + 1;
Mysql> select pi () * 2;
· UPDATE
Allow you to modify existing records in the table.
· INSERT
Allows you to insert records into a table.
· DELETE
Allows you to delete existing records from a table.
· ALTER
Allow you to use the alter table statement. This is actually a simple first-level permission. You must have other permissions. This depends on what operations you want to perform on the database.
· CREATE
You can create databases and tables, but cannot create indexes.
· DROP
Allows you to delete discarded databases and tables, but does not allow you to delete indexes.
Note: If you grant the drop permission of the mysql database to a user, the user can discard the database that stores the MySQL access permission!
· INDEX
Allows you to create and delete indexes.
· REFERENCES
No.