Go deep into MySQL permission system (1)

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. We hope that you can better operate and use the system.

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. We hope that you can better operate and use the system.

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 be using an old MySQL version that does not support these statements (these statements did not work 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 during the first MySQL installation (run the mysql_install_db script ). 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

498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'style = "WIDTH: pixel PX; HEIGHT: 408px "height = 438 alt =" "src =" http://www.68idc.cn/help/uploads/allimg/150511/1120046020-0.jpg "width = 492 border = 0>

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 (Super user) permissions they have. 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 (the same as the GRANT Statement ). 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

You can delete (discard) databases and tables, but cannot 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.