Summary of basic user and permission management methods in MySQL _ MySQL

This article mainly introduces the basic user and permission management methods in MySQL. it is the basic knowledge in MySQL getting started. if you need it, you can refer to MySQL which has a root user by default, however, this user has too many permissions and is generally used only for database management. If you want to connect to the MySQL database in the project, we recommend that you create a user with lower permissions to connect.

In MySQL command line mode, enter the following command to create a new user for MySQL:

CREATE USER username IDENTIFIED BY 'password';

The new user is created, but if you log on to the user at the moment, an error is reported, because we have not assigned the corresponding permissions to the user. the command to assign permissions is as follows:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Grant the username user all permissions on all databases.

If you find that the permission you just granted is too large, if you just want to grant it permissions on a database, you need to switch to the root user to revoke the permission you just granted and re-authorize:

EVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';GRANT ALL PRIVILEGES ON wordpress.* TO 'username'@'localhost' IDENTIFIED BY 'password';

You can even specify that the user can only execute the select and update commands:

GRANT SELECT, UPDATE ON wordpress.* TO 'username'@'localhost' IDENTIFIED BY 'password';

In this way, login to MySQL with username again, only the wordpress database is visible to it, and if you only authorize it to select, then it cannot execute the delete statement.

In addition, after adjusting permissions, you usually need to execute the following statement to refresh permissions:

FLUSH PRIVILEGES;

Delete the user you just created:

DROP USER username@localhost;

By using the preceding commands, you can find that the host (the content after the @ symbol) of the response must be specified for both authorization and authorization revocation ), because the above pass commands are actually operating the user table in the mysql database, you can use the following command to view the corresponding user and the corresponding host:

SELECT User, Host FROM user;

Of course, this table also contains many other contents, such as user passwords and permission settings. be careful when performing operations.