How to set MySQL permissions in Linux

Setting MySQL permissions under Linux can be said to be a relatively common operation. This article will introduce some common methods to help you set MySQL permissions correctly. For convenience, this article assumes that you have MySQL installed and that you are operating as the root user.

1. Grant MySQL user permissions

In MySQL, we can grant users permission to access certain databases through the GRANT command. The basic format of the GRANT command is as follows:

GRANT priv_type ON database_name.table_name TO user_name@host_name IDENTIFIED BY 'password';

Among them, priv_type indicates the type of permission granted, such as SELECT, INSERT, UPDATE, DELETE, CREATE, etc.; database_name.table_name indicates the database and table name to be authorized, which can be authorized For multiple tables or all tables, use * instead; user_name@host_name represents the user and host name to be authorized. If the host name is localhost, it means that it can only be accessed locally; IDENTIFIED BY 'password' represents the password, if no password is required can be omitted.

For example, to grant user test1 SELECT, INSERT, and UPDATE permissions on all tables in database db1, and set the password to 123456, you can execute the following command:

GRANT SELECT, INSERT, UPDATE ON db1.* TO 'test1'@'%' IDENTIFIED BY '123456';

In the above command, % means that any host can access it.

2. Revoke MySQL user permissions

If a user no longer needs to access certain databases or tables, we can revoke the authorized permissions through the REVOKE command. The format of the REVOKE command is as follows:

REVOKE priv_type ON database_name.table_name FROM user_name@host_name;

Among them, the meanings of priv_type, database_name.table_name, user_name@host_name are the same as those in the GRANT command. For example, to revoke user test1's SELECT permissions on all tables in database db1, you can execute the following command:

REVOKE SELECT ON db1.* FROM 'test1'@'%';

3. View MySQL user permissions

Use the SHOW command to view MySQL user permissions . The format of the SHOW command is as follows:

SHOW GRANTS FOR user_name@host_name;

For example, to view the permissions of user test1 on all hosts, you can execute the following command:

SHOW GRANTS FOR 'test1'@'%';

After executing this command, MySQL will return the permissions of user test1 on all hosts. Permission information on the host.

4. Set the MySQL ROOT password

In MySQL, the ROOT user is the highest authority user, so it is very important to set the ROOT user's password. We can use the SET PASSWORD command to set the password of the ROOT user. The format of the SET PASSWORD command is as follows:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

Among them, 'root'@'localhost' represents the user whose password is to be changed, and new_password represents the new password.

When setting the ROOT password, you can also use the UPDATE command. The sample code of the UPDATE command is as follows:

USE mysql;
UPDATE user SET password=PASSWORD("new_password") WHERE user='root';
FLUSH PRIVILEGES;

For the above command, first switch to the mysql database, then use the UPDATE command to change the password of the ROOT user in the user table to new_password, and finally use FLUSH PRIVILEGES to refresh the permissions.

Summary

The above are several methods for setting MySQL permissions. In practical applications, we can use these methods flexibly according to actual conditions. If you want to learn more about MySQL permission management, you can learn from the MySQL official documentation.

The above is the detailed content of How to set MySQL permissions in Linux. For more information, please follow other related articles on the PHP Chinese website!