Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve MYSQL database management rights management, concise and easy to understand, absolutely can make you shine, through the detailed introduction of this article I hope you can gain something.

MYSQL permissions introduction

The simple understanding of mysql's permissions is that mysql allows you to do things within your rights, not beyond them. For example, if you are only allowed to perform select operations, then you cannot perform update operations. You are only allowed to connect to mysql from one machine, so you cannot connect to mysql from any other machine than that machine.

So how are MYSQL permissions implemented? This brings us to mysql's two-phase validation, which is described in detail below: Phase 1: The server first checks whether you are allowed to connect. Because host restrictions are imposed when creating users, you can limit the cost, IP, IP segment, and anywhere, and only allow you to log in from the specified place configured. Later in the actual combat will be detailed about the host restrictions. Phase 2: If you can connect, MYSQL checks 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 will check whether you have permissions on which table or column. For example, if you want to run a stored procedure, MYSQL will check whether you have execution permissions on the stored procedure.

What exactly are the privileges? Copy a table from the official website to see:

Permission Permission level Permission description CREATE database, table, or index Create database, table, or index Permission DROP database or table Delete database or table Permission GRANT OPTION Database, table, or saved program Grant permission options REFERENCES Database or table ALTER table Change table, such as add field, index, etc. Delete table delete data permissions INDEX table index permissions INSERT table insert permissions SELECT table query permissions UPDATE table update permissions CREATE VIEW view create view permissions SHOW VIEW view permissions ALTER ROUTINE stored procedure change stored procedure permissions CREATE ROUTINE Stored Procedure Create Stored Procedure Permission EXECUTE Stored Procedure Execute Stored Procedure Permission FILE Server File Access on Host File Access Permission CREATE TEMPORARY TABLES Server Administration Create Temporary Table Permission LOCK TABLES Server Administration Lock Table Permission CREATE USER Server Administration Create User Permission PROCESS Server Administration View Process Permission RELOAD

server administration permissions to execute flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload, etc. REPLICATION CLIENT server administration replication permissions REPLICATION SLAVE server administration replication permissions SHOW ASES server administration viewing database permissions SHUTDOWN server administration closing database permissions SUPER server administration executing kill thread permissions

How permissions are distributed, that is, what permissions can be set for tables, what permissions can be set for columns, etc. This can be explained from a table in the official document:

Permission Distribution Permission table of possible settings Permission 'Select',' Insert','Update',' Delete','Create',' Drop','Grant',' References','Index','Alter'Column Permission' Select','Insert',' Update','References' Procedure Permission 'Execute','Alter Routine','Grant'

For the permissions section, the most important thing is to know how MYSQL is validated (two-stage validation), what mysql permissions are used for, and where those permissions are used (tables or columns?). If these grasp the MYSQL permissions for you is a piece of cake, as long as you look at the back of the rights management can be integrated.

MYSQL Permission Rule of thumb

Permission control is primarily for security reasons, so a few rules of thumb need to be followed:

Grant only the minimum permissions needed to prevent users from doing bad things. haha. For example, if the user only needs to query, then only select permission can be given, do not give the user update, insert or delete permission.

When creating a user, limit the user's login host, generally limited to a specified IP or intranet IP segment.

Delete users without passwords when initializing the database. After installing the database, some users will be automatically created, and these users will not have passwords by default.

Set a password for each user that meets the password complexity.

Regularly clean up unwanted users. Reclaim permissions or delete users.

MYSQL permissions

GRANT command instructions

Let's look at an example first. Create a superuser feihong that only allows local login, and allow permissions to be granted to other users. The password is test@feihong.111

GRANT ALL PRIVILEGES ON *.* TO feihong@'localhost' IDENTIFIED BY 'test@feihong.111' WITH GRANT OPTION;

Command Description:

ALL PRIVILEGES means all permissions, you can also use select, update and other permissions mentioned permissions.

ON is used to specify which libraries and tables permissions are for.

*.* The leading * in is used to specify the database name, and the trailing * is used to specify the table name.

TO means to grant permissions to a user.

feihong@'localhost' means feihong user,@ followed by restricted host, which can be IP, IP segment, domain name and %, % means anywhere. Note: here % some version does not include local, previously encountered to a certain user set % allow any place to log in, but in the local login can not, this and version has a relationship, encountered this problem and add a localhost user can.

IDENTIFIED BY Specifies the login password for the user.

WITH GRANT OPTION This option indicates that the user can delegate the permissions he has to others. Note: It is not uncommon for someone to create an operating user without specifying the WITH GRANT OPTION option, resulting in that user not being able to create users or authorize other users later using the GRANT command.

Note: You can use GRANT to repeatedly add permissions to the user, permission superposition, for example, 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.

Create a super user

Create a superuser feihong that only allows local login, and allow permissions to be granted to other users, password test@feihong.111

GRANT ALL PRIVILEGES ON *.* TO feihong@'localhost' IDENTIFIED BY 'test@feihong.111' WITH GRANT OPTION;

Create a website user (program user)

Create a general program user, this user may only need SELECT, INSERT, UPDATE, Delete, CREATE TEMPORARY TABLES and other permissions If there is a stored procedure, you also need to add EXECUTE permission, generally specifying the intranet segment 192.168.100.

GRANT USAGE,SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ,CREATE TEMPORARY TABLES,EXECUTE ON `test`.* TO webuser@'192.168.100.% ' IDENTIFIED BY'test@feihong.111';

Create a normal user (query only)

GRANT USAGE,SELECT ON `test`.* TO public@'192.168.100.% ' IDENTIFIED BY'public@feihong.111';

Refresh permissions

Use this command to make permissions effective, especially if you update or delete the permissions tables user, db, host, etc. Previously encountered after the use of grant permissions have not been updated, we can form a habit, as long as the permissions have been changed to use the FLUSH PRIVILEGES command to refresh permissions.

FLUSH PRIVILEGES;

view permission

You can easily view the permissions of a user by using the following command:

SHOW GRANTS FOR 'webuser'@'192.168.100.% ';

Reclaim permissions

To recover the Delete permission of the webuser user created earlier, use the following command

REVOKE DELETE ON test.* FROM 'webuser'@'192.168.100.% ';

delete user

Note: Do not delete a user directly by using Delete, because the user's permissions are not deleted after deletion, and the previous permissions will be inherited after creating a new user with the same name. The correct way to delete a user is to use the DROP USER command, for example to delete 'webuser'@'192.168.100.% 'The user uses the following command:

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

You can use the pt-show-grants tool in the percona-toolkit tool to assist in managing mysql permissions.

The above content is MYSQL database management in how to achieve permission management, you learned knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.