Rights Management of MySQL database management

Http://blog.chinaunix.net/uid-20639775-id-3475608.html

MySQL database management Rights Management

Often met with netizens in the QQ group or forum on the question of MySQL permissions, today to summarize about the use of MySQL in the past few years on the MySQL database Rights management experience, but also hope to use the MySQL users help!

I. Introduction to MySQL Privileges

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, described in detail below: First stage: The server will first check whether 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.

What permissions does MySQL have? Copy a table from the website to see:

Permissions	Permission levels	Permission Description
CREATE	Database, table, or index	Create database, table, or index permissions
DROP	Database or table	Delete database or table permissions
GRANT OPTION	A database, table, or saved program	Granting permission Options
REFERENCES	Database or table
Alter	Table	Change tables, such as adding fields, indexes, 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	View View Permissions
ALTER ROUTINE	Stored Procedures	Change stored procedure permissions
CREATE ROUTINE	Stored Procedures	Create Stored Procedure permissions
EXECUTE	Stored Procedures	Execute Stored Procedure permissions
FILE	File access on the server host	File access Permissions
CREATE Temporary TABLES	Server Management	Create temporary table permissions
LOCK TABLES	Server Management	Lock table Permissions
CREATE USER	Server Management	Create User Rights
PROCESS	Server Management	View Process Permissions
RELOAD	Server Management	Perform flush-hosts, Flush-logs, Flush-privileges, Flush-status, Flush-tables, flush-threads, refresh, reload, and so on command permissions
REPLICATION CLIENT	Server Management	Copy Permissions
REPLICATION SLAVE	Server Management	Copy Permissions
SHOW DATABASES	Server Management	View Database Permissions
SHUTDOWN	Server Management	Turn off database permissions
SUPER	Server Management	Execute Kill Thread Permissions

How MySQL's permissions are distributed is what permissions are set on the table, what permissions are set on the column, and so on, which can be explained in a table in the official documentation:

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 '

For permissions, the main thing is to know how MySQL is verified (two-phase verification), and what the MySQL permissions are for, and where the permissions are used (table or column?). )。 If these are grasped then MySQL permissions for you is a piece of cake, just look at the back of the Authority management can digest.

Second, the MySQL authority experience principle

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

1. Grant only the minimum permissions that are needed to prevent users from doing bad things. Ha ha. For example, users just need to query, then only give select permission on it, do not give the user update, insert or delete permissions.
2. When creating a user, restrict the user's login host, which is usually restricted to the designated IP or intranet IP segment.
3. Delete the user without the password when initializing the database. Some users are automatically created when the database is installed, and these users do not have a password by default.
4. Set a password that satisfies the complexity of the password for each user.
5. Regular cleanup of unwanted users. Reclaim permissions or delete users.

Third, MySQL access to combat

1. Instructions for using the GRANT command

Let's take a look at an example, create a super user Feihong that only allows logging on locally, and allow permissions to be assigned to other users, with the password [email protected]

GRANT all privileges on * * to [e-mail protected] ' localhost ' identified by ' [e-mail protected] ' with GRANT OPTION;

Description of the GRANT command:

All privileges is a representation of all permissions, and you can also use permissions mentioned by SELECT, Update, and so on.

On to specify which libraries and tables the permissions are for.

The * in front of * * is used to specify the database name, followed by the * number to specify the table name.

To indicates that the permission is assigned to a user.

[email protected] ' localhost ' means the Feihong user, at the end of the limited host, can be IP, IP segment, domain name, and%,% represent anywhere. Note: There are some versions of this is not included in the local, previously encountered to a user set the% allowed to log anywhere, but not on the local login, this and the version has a relationship with this problem plus a localhost user can be.

Identified by specifies the user's login password.

With GRANT option This option means that the user can delegate the permissions they own to others. Note: It is often not specified with the WITH GRANT option when creating an operation user that the user cannot use the grant command to create a user or authorize another user.

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.

1. Create a Super User

Create a super User Feihong that only allows log on locally, and allow permissions to be assigned to other users with the password [email protected]

GRANT all privileges on * * to [e-mail protected] ' localhost ' identified by ' [e-mail protected] ' with GRANT OPTION;

1. Create a Web site user (program user)

Create a general program user, this user may need only select, INSERT, UPDATE, DELETE, create temporary tables and other permissions if there is a stored procedure also need to add execute permission, In general, the designated intranet segment 192.168.100 network segment.

GRANT Usage,select, INSERT, UPDATE, DELETE, SHOW VIEW, CREATE temporary tables,execute on ' test '. * to [email protected] ' 1 92.168.100.% ' identified by ' [email protected] ';

1. Create a normal user (query permission only)

GRANT usage,select on ' test '. * to [e-mail protected] ' 192.168.100.% ' identified by ' [email protected] ';

1. Refresh Permissions

Use this command to make permissions effective, especially if you have updated or delete updates to the Permissions table user, DB, host, and so on. Previously encountered the use of grant after the permission is not updated, you can form a habit, as long as the permissions have been changed to use the flush privileges command to refresh permissions.

FLUSH privileges;

1. View Permissions

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

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

1. Reclaim Permissions

Recycle the Delete permission of the previously created WebUser user, using the following command

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

1. Delete 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.% ';

You can use the Pt-show-grants tool in the Percona-toolkit tool to help manage MySQL permissions. For specific use See blog http://blog.chinaunix.net/uid-20639775-id-3207926.html

Rights Management of MySQL database management