Mysql permission management

Some netizens often asked questions about mysql permissions in the QQ group or forum. Today, I will take the time to summarize my experience on permission management for MYSQL Databases during the past few years, I also hope it will be helpful for mysql users! I. MYSQL permission Introduction A simple understanding of mysql permissions is that mysql allows you to do things within your rights, no

Some netizens often asked questions about mysql permissions in the QQ group or forum. Today, I will take the time to summarize my experience on permission management for MYSQL Databases during the past few years, I also hope it will be helpful for mysql users! I. MYSQL permission Introduction A simple understanding of mysql permissions is that mysql allows you to do things within your rights, no

Some netizens often asked questions about mysql permissions in the QQ group or forum. Today, I will take the time to summarize my experience on permission management for MYSQL Databases during the past few years, I also hope it will be helpful for mysql users!

1. MYSQL Permissions

A simple understanding of mysql permissions is that mysql allows you to do things within your rights and cannot cross-border. For example, if you are only allowed to perform the select operation, you cannot perform the update operation. You can only connect to mysql from a machine, so you cannot connect to mysql from other machines.

How is MYSQL's permissions implemented? This requires two-phase mysql verification. The following describes in detail: Stage 1: The server first checks whether you allow connection. Because host restrictions are added when you create a user, you can restrict the cost, an IP address, an IP segment, and any location, and only allow you to log on from the specified location of the configuration. The host restrictions will be detailed later in practice. Phase 2: If you can connect, MYSQL will check every request you send 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 which table or column you have permissions on. For example, if you want to run a stored procedure, MYSQL will check whether you have the execution permission on the stored procedure.

What permissions does MYSQL have? Copy a table from the official website:

Permission	Permission level	Permission description
CREATE	Databases, tables, or Indexes	Create Database, table, or index Permissions
DROP	Database or table	Delete database or table Permissions
GRANT OPTION	Database, table, or stored program	Grant permission options
REFERENCES	Database or table
ALTER	Table	Change tables, such as adding fields and indexes.
DELETE	Table	Delete data permission
INDEX	Table	Index permission
INSERT	Table	Insert permission
SELECT	Table	Query permission
UPDATE	Table	Update Permissions
CREATE VIEW	View	Create view Permissions
SHOW VIEW	View	View view Permissions
ALTER ROUTINE	Stored Procedure	Change Stored Procedure Permissions
CREATE ROUTINE	Stored Procedure	Create Stored Procedure permission
EXECUTE	Stored Procedure	Stored Procedure execution permission
FILE	File access on the server host	File Access permission
CREATE TEMPORARY TABLES	Server Management	Create temporary table Permissions
LOCK TABLES	Server Management	Lock table Permissions
CREATE USER	Server Management	Create User Permissions
PROCESS	Server Management	View process Permissions
RELOAD	Server Management	Execute commands such as flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, and reload.
REPLICATION CLIENT	Server Management	Copy permission
REPLICATION SLAVE	Server Management	Copy permission
SHOW DATABASES	Server Management	View database Permissions
SHUTDOWN	Server Management	Disable database Permissions
SUPER	Server Management	Run the kill thread permission

The distribution of MYSQL permissions refers to the permissions that can be set for tables and columns. This can be explained from a table in the official document:

Permission Distribution	Possible Permissions
Table Permissions	'Select', 'insert', 'update', 'delete', 'create', 'drop', 'Grant ', 'references', 'index', 'alter'
Column permission	'Select', 'insert', 'update', 'references'
Process permission	'Execute ', 'alter Routine', 'Grant'

For permission, the most important thing is to know how MYSQL is verified (two-phase verification), and what mysql permissions are used, and where the permissions are used (table or column ?). If you grasp this, the MYSQL permission is just a piece of cake for you. You just need to check the permission management at the end of the page.

Ii. MYSQL permission principles

Permission control is mainly for security reasons. Therefore, we need to follow the following principles:

1. Grant only the minimum permissions that can meet your needs to prevent users from doing bad things. Haha. For example, if you only need to query, you only need to grant the select permission. do not grant update, insert, or delete permissions to the user.

2. When creating a user, restrict the user's login host to a specified IP address or Intranet IP address segment.

3. delete users without a password when initializing the database. Some users are automatically created after the database is installed. By default, these users do not have a password.

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

5. Clear unnecessary users on a regular basis. Revoke permissions or delete users.

Iii. MYSQL Permissions

1. GRANT command instructions

First let's look at an example, create a Super User feihong that only allows local login, and allow permission 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;

GRANT command description:

All privileges indicates ALL permissions. You can also use the permissions mentioned by select, update, and other permissions.

ON is used to specify which databases and tables the Permission applies.

*. * Indicates the database name, and * indicates the table name.

TO: grant permissions TO a user.

Feihong @ 'localhost' indicates the feihong user. @ is followed by a restricted host, which can be an IP address, IP segment, domain name, and %. % indicates any location. Note: Here % Some versions do not include the local version. I have previously set % to allow a user to log on anywhere, but cannot log on locally. This is related to the version, if this problem occurs, add a localhost user.

Identified by specifies the user's logon password.

With grant option indicates that the user can GRANT permissions to others. Note: Some users often do not specify the with grant option when creating operation users. As a result, this user cannot use the GRANT command to create users or GRANT permissions to other users.

Note: You can use GRANT to repeatedly add permissions to users. For example, you add a select permission to the user first and then add an insert permission to the user, then the user has both select and insert permissions.

2. Create a Super User

Create a Super User feihong that only allows local login, and allow permission to be granted to another user with a password of test@feihong.111

Grant all privileges on *. * TO feihong @ 'localhost' identified by 'test @ feihong.111 'with grant option;

3. Create a Website user (Program user)

CREATE a general program user. This user may only need the SELECT, INSERT, UPDATE, DELETE, create temporary tables and other permissions. If the stored procedure has the EXECUTE permission, generally, the Intranet CIDR Block 192.168.100 is specified.

Grant usage, SELECT, INSERT, UPDATE, DELETE, show view, create temporary tables, execute on 'test '. * TO webuser @ '2017. 168.100.% 'identified BY 'test @ feihong.111 ';

4. Create a common user (only with query permission)

Grant usage, select on 'test'. * TO public @ '192. 192.% 'identified by 'public @ feihong.111 ';

5. Refresh Permissions

Use this command to make the permission take effect, especially when you update or delete the permission table user, db, host, and so on. In the past, when the permissions are not updated after grant is used, you can get into the habit of using the flush privileges command to refresh permissions as long as the permissions are changed.

Flush privileges;

6. View Permissions

You can use the following command to conveniently view the permissions of a user:

Show grants for 'webuser' @ '192. 192.% ';

7. revoke permissions

Revoke the DELETE permission of the previously created webuser by using the following command:

Revoke delete on test. * FROM 'webuser' @ '2017. 192.% ';

8. delete a user

Note: Do not use DELETE to DELETE a user directly, because the user's permissions are not deleted after the DELETE operation. After a user with the same name is created, the user will inherit the previous permissions. The correct method is to use the drop user command to delete a USER. For example, to delete the 'webuser' @ '192. 192.% 'USER, use the following command:

Drop user 'webuser' @ '192. 192.% ';

You can use the pt-show-grants tool in percona-toolkit to manage mysql permissions. For more information, see blog http://blog.chinaunix.net/uid-20639775-id-3207926.html.

Below is the http://zhidao.baidu.com/link? Url = jivv0yTsgYfrAbvXs6EiLhg5pGgttEgY28eJn-PRfXzmhcjP-SkHUmNM-OTcAujSWHkof9HkszMVmCrbU0f4I _ description of usage

Official explanation of usage: USAGE is a synonym for "no permission". You can specify USAGE when you want to create a user without permission. Depending ON what permissions you have, execute SQL: SHOW GRANTS in my database with several rows: GRANT ALL PRIVILEGES ON 'everalan '. * TO 'everalan '@' % 'with grant option, we can see that you do not know the USAGE permission. Therefore, you can perform operations such as query, deletion, and usage normally, in fact, this is to allow you to log on to the database like a user, but you do not have any permissions except to see the database.