MySQL — About Grant privileges and Flush Privileges

First, the summary diagram:



There are two operations involved in granting or revoking permissions or creating users:

Alter table mysql.user; alter table mysql.user; alter table mysql.user

2, memory, acl_users find the object corresponding to the user, modify the access value

Grant authorization

Create user ‘ua’ @ ‘%’ with password pa

create user 'ua'@The '%' identified by 'pa';
Copy the code

Global permission

grant all privileges on *.* to 'ua'@The '%' with grant option;
Copy the code

The grant command updates both disk and memory for global permissions. The command takes effect immediately, and the new permissions are also applied to newly created connections

The grant command does not affect the global permissions of an existing connection. (That is, if global permission information is already in a thread object, revoke affects that thread object.)

Reclaim permission can look like this:

revoke all privileges on *.* from 'ua'@The '%';
Copy the code

Library level permissions

grant all privileges on db1.* to 'ua'@The '%' with grant option;
Copy the code

As long as you have access to the library, the thread object has access to the library until it switches out of the library. When a thread object has access to a library, the thread object can no longer access the library. For an existing connection, its library permissions are affected by the grant command

Table and column permissions

The weight statement is as follows:

create table db1.t1(id int, a int);
grant all privileges on db1.t1 to 'ua'@The '%' with grant option;
GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@The '%' with grant option;
Copy the code

Similar to DB permissions, operations on these two permissions will immediately affect existing connections.

Flush privileges command