Chapter IX user and authority

9.1 Identity Authentication

（1）IP+ User name as authentication

​ for example ：

​ root@localhost： Use only on this machine root The user login

​ root@%： Run on any machine root The user login

​ [email protected]： Only run on 192.168.11.56 Use on main engine root The user login

​ gjz@%： Run on any machine gjz The user login

When creating users , Used for authentication IP Address , It's connected to the client of your service IP Address

for example ：[email protected], Indicates that the client is in 192.168.11.56 You can access your mysql service

And this client , To connect to your service ,-h Back of the mainframe IP The address is , Of the machine where the server is located IP Address .

for example ：mysql The service is 192.168.11.11 On the machine , The client is in 192.168.11.56 On , So the client can use [email protected] or root@%, Fill in when connecting mysql -h192.168.11.11 -u root -p password

（2） password

9.2 Assign permissions

1、 User rights include 4 Permission levels

（1） overall situation

（2） database

（3） surface

（4） Field

Check permissions in turn , If the front passes , There will be no verification later ：

overall situation > database > surface > Field

Be careful ：root@localhost, This user always retains all global permissions .

2、 Account permissions

show grants for ' user name '@' host IP Address ';

3、 New users and delete users

The corresponding statement ：

CREATE USER ' user name '@' host IP Address ' IDENTIFIED BY '123456';

If the host IP Address ：（1）192.168.29.53, Only when IP Sign in

​ （2）%, It means that you can start at any time IP Sign in

drop user ' user name '@' host IP Address ';

4、 Granting and withdrawing authority

Corresponding statement ：

GRANT  Permission list  ON *.* TO ' user name '@' host IP Address ';  # overall situation 
GRANT  Permission list  ON  Database name .* TO ' user name '@' host IP Address '; # A library 
GRANT  Permission list  ON  Database name . form  TO ' user name '@' host IP Address '; # A table of a library 
GRANT  Permission list  ON  Table name .* TO ' user name '@' host IP Address ';  # The fields of a table 

for example ：

GRANT SELECT ON *.* TO 'gao'@'192.168.29.53';
GRANT SELECT ON `test`.* TO 'gao'@'192.168.29.53';
GRANT UPDATE ON `test`.`t_department` TO 'gao'@'192.168.29.53';
GRANT UPDATE ON `t_department`.* TO 'gao'@'192.168.29.53';
GRANT SELECT (tid), INSERT (tid), UPDATE (tid), REFERENCES (tid) ON `1101db`.`course` TO 'gao'@'192.168.29.30';

Take back authority ：

revoke  Permission list  ON *.* from ' user name '@' host IP Address ';
revoke  Permission list  ON  Database name .* from ' user name '@' host IP Address ';
revoke  Permission list  ON  Database name . form  from ' user name '@' host IP Address ';
revoke  Permission list  ON  Table name .* from ' user name '@' host IP Address ';