MySQL Learning Note (13) Rights Management

Learning content:

1. Rights Management:

The simple understanding of MySQL permissions is that MySQL allows you to do things within your rights and not to cross the border. For example, if you are only allowed to perform a select operation, you cannot perform an update operation. Only allow you to connect to MySQL from a machine, then you cannot connect to MySQL from other machines except that one.

So how is MySQL's permissions implemented? This is about the two-phase verification of MySQL:

First stage: The server will first check if you allow the connection. Because a host limit is added when creating a user, you can limit the cost, an IP, an IP segment, and any place, and only allow you to log in from the specified location of the configuration. Later in the actual combat will be detailed about the limitations of the host.

Second stage: If you can connect, MySQL will check every request you make to see if you have sufficient permissions to implement it. For example, if you want to update a table or query a table, MySQL checks to see if you have permissions on which table or column. For example, if you run a stored procedure, MySQL checks to see if you have execute permissions on the stored procedure.

MySQL Permissions:

There are many permissions on the official web that involve MySQL .... Here is a simple introduction to the classification ...

Distribution of permissions	Possible settings for permissions
Table Permissions	' Select ', ' Insert ', ' Update ', ' Delete ', ' Create ', ' Drop ', ' Grant ', ' References ', ' Index ', ' Alter '
Column permissions	' Select ', ' Insert ', ' Update ', ' References '
Process permissions	' Execute ', ' Alter Routine ', ' Grant '

The main thing is how to verify the two permissions of MySQL ... And familiar with the MySQL permissions is what to do, then this knowledge is easy to understand ...

2.MYSQL Authority Experience principle

Permissions control is primarily for security reasons, so you need to follow a few rules of thumb:

I. Grant only the minimum permissions that satisfy the needs, such as the user simply needs to query, then only give SELECT permission, do not give the user update, insert or delete permissions.

Ii. when creating a user, restrict the user's login host, which is usually restricted to the designated IP or intranet IP segment.

Iii. when initializing the database, delete the user without the password.

Iv. Some users are automatically created when the database is installed, and these users do not have a password by default. Set a password that satisfies the complexity of the password for each user.

V. Regular cleanup of unwanted users. Reclaim permissions or delete users.

3. How do I create a user? And how do you set permissions for a user?

GRANT  All Privileges  on *. *  to [Email protected] ' localhost '  by ' 49681888 '  with GRANT OPTION;

Explain what the above statement means:

Grant keyword all privileges give all permissions

*. * indicates (database. Table name) For example, if we want to create a super-privileged user in the shop table in the SAMP database, then *. * can be written as Samp.shop

To indicates that the permission is assigned to a user.

[email protected] ' localhost ' means clearlove This user @ can be followed by IP, or domain name, here is their own host ...

Identified by means to set a login password for the user: 49681888

With GRANT option means that you can assign your own permissions to others. This sentence is especially important ...

Note: You can use grant to repeatedly add permissions to the user, permissions overlay, such as when you first add a SELECT permission to the user, and then add an INSERT permission to the user, then the user has both select and insert permissions.

4. Create a Super User

Grant  All Privileges  on  to [Email protected] ' localhost '  by ' 49681888 '  with Grant option;

5. Create a regular user

Create a generic program user, this user may only need select, INSERT, UPDATE, DELETE, CREATE temporary tables such as permissions if there is a stored procedure also need to add execute permission, is usually specified intranet segment 192.168.100 network segment.

GRANT  USAGE,SELECTINSERTUPDATEDELETEVIEW ,CREATETemporary TABLES,EXECUTE on to [email protected]'192.168.100.% '  by  ' 49681888 ';

6. Create a site user

GRANT USAGE,SELECTon to public @'192.168.100.%'by   '49681888';

7. Refresh Permissions

Permission refresh generally means that when we change the permissions, we need to refresh the permissions to make the permissions we changed to take effect ...

privileges;

8. View permissions for any user

 for ' WebUser ' @'192.168.100.%';

9. Reclaim Permissions

REVOKE DELETE  on test. *  from ' WebUser ' @'192.168.100.%';

10. Delete a user

Note Delete users do not use Delete to delete directly, because the user's permissions are not deleted after using Delete, and the new user with the same name inherits the previous permissions. It is a good practice to use the drop user command to delete users, such as to remove the ' webuser ' @ ' 192.168.100.% ' user with the following command ...

DROP USER ' WebUser ' @'192.168.100.%';

Own authority management is a basic learning, but also simply introduced a few basic things ... Rights management is generally used in large projects to get ... There is no more swim here ....

MySQL Learning Note (13) Rights Management