MySQL Privilege mechanism 2

Privilege control mechanism
Four tables : User db Tables_priv Columns_priv privilege privileges
Mysql<selectFrom User\g
Mysql<select From db\g
Mysql<selectFrom Tables_priv\g
Mysql<select * from Columns_priv\g
1. User authentication
View Mysql.user Table
2. Authority authentication
Take SELECT permission as an example:
First look at the Select_priv permissions in the user table
Y will not proceed to view other tables have permission to view all tables in all libraries
N then look at the DB table
Db
Y will not proceed to view other tables have permission to view all tables in all libraries
N then look at the Tables_priv table
Tables_priv
Table_priv: If the value of this field includes select owns view of this table all
field, and will not look down again.
Table_priv: If the value of this field does not include SELECT, then view the next table
You also need to have column_priv field permissions

                                                columns_priv                                                                 column_priv:  有select，则只对某一列有select权限                                                                         没有则对所有库所有表没有任何权限

Db:
Mysql> INSERT into db (Host,db,user,select_priv) VALUES ("10.18.44.%", ' Data ', ' Ying ', ' Y ');

Tables_priv:
mysql> INSERT INTO Tables_priv (HOST,DB,USER,TABLE_NAME,TABLE_PRIV) VALUES (' 10.18.44.% ', ' data ', ' Ying ', ' t1 ', ' Select,insert ');

Columns_priv:
mysql> INSERT INTO Columns_priv (HOST,DB,USER,TABLE_NAME,COLUMN_NAME,COLUMN_PRIV) VALUES (' 10.18.44.% ', ' data ', ' Ying ', ' t1 ', ' id ', ' select ');

Host,db,user,select_priv
Host,db,user,table_name,table_priv
Host,db,user,table_name,column_name,column_priv

Refresh permissions: Two ways
Need to refresh permissions after modifying tables
Mode 1:
MySQL > Flush privileges;
Mode 2:

Mysqladmin flush-privileges-u ROOT-P1

Five-table linkage (5.7 cancelled the host table)
User table
Login Authentication: Can users enter MySQL to view user
Permission authentication: If you can enter, see the User table permissions there is no, not to see the next DB table, the DB table did not see Tables_priv,tables_priv did not go to see Columns_priv

当db表的host字段为空的时候才会用到host表db或者host 任何一个select是N,都是没权限

Using Command authorization: Grant
You can also create a new account (although later versions will remove this feature, it is recommended to use create user)
Syntax format:
Grant permission list on Library name. Table name to ' user name ' @ ' client host ' [identified by ' Password ' with option parameter];
= = Permission list all permissions (not including authorization rights)
Select,update
Select (col1), insert (col1,col2) Column level

==数据库.表名    *.*         所有库下的所有表                  Global level                        web.*       web库下的所有表                 Database level                        web.stu_info    web库下的stu_info表   Table level==客户端主机              %                          所有主机                                    192.168.2.%         192.168.2.0网段的所有主机                                    192.168.2.168           指定主机                                    localhost                指定主机

With_option parameters
GRANT OPTION: Authorization options
Max_queries_per_hour: Defines the number of queries that are allowed to execute per hour
Max_updates_per_hour: Defines the number of updates per hour that are allowed to execute
Max_connections_per_hour: Defines the number of connections that can be established per hour
Max_user_connections: Defines the number of connections that a single user can make at the same time

Mysql> grant Select (ID), insert (ID) on wing.t1 to ' Xiaowu ' @ ' 172.16.70.% ' identified by ' 123 ';
Mysql> Grant Select,insert on wing.t1 to ' Xiaowu ' @ ' 172.16.70.% ' identified by ' 123 ';
Mysql> Grant all on wing.t1 to ' Xiaowu ' @ ' 172.16.70.% ' identified by ' 123 ';
Mysql> Grant all in . to ' Xiaowu ' @ ' 172.16.70.% ' identified by ' 123 ';
Mysql> Grant all on . to ' Xiaowu ' @ ' 172.16.70.% ';
Mysql> Grant all in . to ' Xiaowu ' @ '% ';

View Permissions
To see your own permissions:
SHOW Grants\g

看别人的权限：SHOW GRANTS FOR [email protected]‘%‘\G  

REVOKE permission: Revoke
Grammar:
REVOKE permissions list on database name from user name @ ' client Host '

mysql> revoke all on *.* from ‘xiaowu‘@‘%‘;mysql> revoke delete on *.*  from [email protected]’%’;                                   //回收部分权限REVOKE ALL PRIVILEGES  ON *.*  FROM [email protected]’%’;                             //回收所有权限REVOKE ALL PRIVILEGES,GRANT OPTION  ON *.* FROM ‘admin2‘@‘%‘;

MySQL Privilege mechanism 2