MySQL Advanced Seventeen - MySQL Account Permission Grant

1. Function and significance

The mysql permission system is mainly used to verify the permissions of users connected to the database to determine whether the user is a legal user. If it is a legal user, if If you are a legal user, you will be granted corresponding database permissions.

2. View the users of the current database

mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| 127.0.0.1 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-----------+------+-------------------------------------------+
2 rows in set (0.04 sec)

3. MySQL permission application

1. GRANT command usage instructions

Let’s look at an example first, create A super user test that only allows local login and allows permissions to be granted to other users. The password is 123456

GRANT ALL PRIVILEGES ON *.* TO test@'localhost' IDENTIFIED BY 'test@feihong.111' WITH GRANT OPTION;

GRANT command description:

ALL PRIVILEGES means all permissions, you can also Use the permissions mentioned in select, update and other permissions.

ON is used to specify which libraries and tables the permissions target. The * in front of

*.* is used to specify the database name, and the * in the back is used to specify the table name.

TO means granting permissions to a user.

test@'localhost' means feihong user, @ is followed by the restricted host, which can be IP, IP segment, domain name and %, % means anywhere. Note: Some versions here do not include local. In the past, I have set a user to allow login from anywhere, but cannot log in locally. This is related to the version. If you encounter this problem, add a localhost user. That's it.

IDENTIFIED BY specifies the user's login password.

WITH GRANT OPTION This option indicates that the user can authorize his or her permissions to others. Note: Often people do not specify WITH GRANT OPTION when creating an operating user, resulting in the user not being able to use the GRANT command to create users or authorize other users.

Note: You can use GRANT to repeatedly add permissions to the user, and the permissions are superimposed. For example, if you first add a select permission to the user, and then add an insert permission to the user, then the user will have both select and insert permissions. insert permission.

2. Check the user's permissions

show grants for 'root'@'localhost'

3. Delete the user

Deleting a user should not only delete the user's name, but also delete the permissions owned by the user.

Be careful not to use DELETE to delete a user directly, because the user's permissions are not deleted after deletion using DELETE, and the previous permissions will be inherited when a new user with the same name is created. The correct way is to use the DROP USER command to delete the user. For example, to delete the 'webuser'@'192.168.100.%' user, use the following command:

drop user 'user'@'192.168.100.%';  
drop user 'user'@'%'

4. Change the account password

set password for 'account name'@'%'=password('new password');

The above is the content of MySQL Advanced Seventeen - MySQL account permissions. For more related content, please pay attention to the PHP Chinese website (www .php.cn)!