Knowledge Point 15: MySQL Account Rights Management (33)

Knowledge Point 16: MySQL account permissions are given (33)

I. Introduction to MySQL privileges

The simple understanding of MySQL permissions is that MySQL allows you to do what you do best, and you can't 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, which is described in detail below:

First stage: The server will first check if you allow the connection. Because the user is created with a host limit, 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.

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 will 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:

　　　　　　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:

　second, the role and significance :
The MySQL privilege system is used primarily to authenticate users who are connected to the database to determine whether the user is a legitimate user . If it is a legitimate user, the appropriate database permissions are given.

1.MySQL Authority Experience Principle:

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

1, only to meet the needs of the minimum permissions to prevent users from doing bad things. 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 the user is created to restrict the user's login host, is generally limited to the designated IP or intranet IP segment.

3. When initializing the database, delete the user without password. 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, regularly clean up the unwanted users. Reclaim permissions or delete users.

Iii. View the current database user
user management
Span style= "font-family:"microsoft yahei"" > use MySQL;
view
select Host,user from user;
iv. mysql Permissions application:
1. grant command instruction:
First, take a look at an example, create a super user test 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 [Email protected] ' localhost '  by ' [email 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: Some versions of this version do not include local,

Previously encountered to a user set the% allow any place to log in, but not on the local login, this and version has a relationship, encounter 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, for example, you first add a SELECT permission to the user, and then add an INSERT permission to the user, then the user

It also has Selecinsert permissions.

1 --Create a test1 user with the password: [email protected]2 --can operate on all databases and assign permissions to other users3 GRANT  All Privileges  on *.* 4      to[Email protected]'localhost' 5Identified by '[email protected]'6  with GRANT OPTION;7 8 --Create a test2 user with the password: [email protected]9 --all databases can be updated and deleted, and permissions may be assigned to other usersTen GRANT UPDATE,DELETE  on *.*  One      to[Email protected]'localhost'  AIdentified by '[email protected]' -  with GRANT OPTION; -  the  - --Create a test user with the password: [email protected] - --You can have all permissions on all tables in a database and assign permissions to other users - GRANT  All Privileges  onNzjj.*  +      to[Email protected]'localhost'  -Identified by '[email protected]' +  with GRANT OPTION;

To add a user rights test2.1: View User Rights

1 --Create a Super User2     --Create a Super User Feihong that only allows log on locally, and allow permissions to be assigned to other users.3         --password is [email protected]4 GRANT  All Privileges  on *.* 5      to[Email protected]'localhost' 6Identified by '[email protected]'7  with GRANT OPTION;8 9 --Create a Web site user (program user)Ten     --To create a generic program user, this user may only need select, INSERT, UPDATE, DELETE, One         --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.  A GRANTUSAGE,SELECT,INSERT,UPDATE,DELETE, SHOWVIEW,CREATE TemporaryTABLES,EXECUTE  -  on' Test '.*  to[Email protected]'192.168.100.%'  -Identified by '[email protected]'; the  - --Create a normal user (query permission only) - GRANTUSAGE,SELECT  on' Test '.*  to  Public@'192.168.100.%'  -Identified by  '[email protected]';

Default Data

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

 for ' WebUser ' @'192.168.100.%';

2.2: Delete User

Deleting a user not only removes the user's name, but also removes the user's permissions.

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,

For example, to remove the ' webuser ' @ ' 192.168.100.% ' user with the following command:

• DROPUSER'webuser' @'192.168.100.%';

2.3: Change your account password:

Execute the SET Password statement:

Set  for ' Account name ' @ '%'=password (' new password ');

2.4: Resource settings for account permissions:

For example: Create a ZL account, have SELECT permission in the MZJJ database, the number of queries per hour less than 5 times, up to 6 users concurrently connect:

1 GRANT SELECT  onMzjj.*  to[Email protected]'localhost' 2Identified by '123456'3  withMax_queries_per_hour5Max_user_connections6;4 --View user name, number of queries per hour, maximum number of connections5 SELECT User, max_questions,max_connections from User WHERE User='ZL';

test the Account permissions resource

Knowledge Point 15: MySQL Account Rights Management (33)