How to set permissions for users in mysql

MySQL is a popular relational database management system. Many websites and applications use MySQL to store and manage data. When developing MySQL applications, in order to ensure data security and accuracy, it is usually necessary to set different levels of user permissions. This article will introduce how to set permissions for users in MySQL.

1. Log in to MySQL

Enter the following command in the terminal to log in to the MySQL server.

$ mysql -u root -p

Among them, -u represents the user name, and -p represents the need to enter a password. If no password is set, the -p parameter can be omitted.

2. Create a new user

The command to create a new user in MySQL is:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

where username is you The username you want to create, password is the password you want to set. @'localhost' indicates that the user can only connect to the MySQL server locally. If you want to allow connections to the MySQL server from other machines, you can replace localhost with %.

3. Assign permissions to users

In MySQL, you can use the GRANT command to assign different levels of permissions to users. For example, the following command can grant user username query and update permissions on database test:

GRANT SELECT, INSERT, UPDATE ON test.* TO 'username'@'localhost';

where SELECT means executing on the table Permissions for query operations, INSERT represents the authority to perform insert operations on the table, UPDATE represents the authority to perform update operations on the table. test.* means that all tables under the test database are authorized. If you want to authorize a single table, you can replace test.* with test.tablename.

If you want to grant the user all operation permissions on all databases, you can use the following command:

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

In addition to ALL PRIVILEGES, you can also use other permissions, For example, SELECT,INSERT,UPDATE,DELETE,CREATE,DROP, etc.

4. Refresh permissions

When we assign new permissions to a user, the permissions need to be refreshed to take effect. You can use the following command to refresh the permissions:

FLUSH PRIVILEGES;

If you do not refresh the permissions, the user may get an "Access denied" error when performing some operations that require permissions.

5. Delete user permissions

If you want to revoke certain permissions of the user, you can use the following command:

REVOKE SELECT, INSERT ON test.* FROM 'username'@'localhost';

Among them, SELECT and INSERT indicate that the query and insert permissions on database test are revoked from user username. Likewise, test.* can be replaced with test.tablename, thereby revoking the user's access to a single table. If you want to remove all permissions from a user, you can use the following command:

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';

6. View user permissions

You can use the following command to view the permissions of all users in MySQL:

SELECT * FROM mysql.user;

In addition, you can also use the following command to view the permissions of a user:

SHOW GRANTS FOR 'username'@'localhost';

The above command will display all the permissions of user username in MySQL.

Summary

In MySQL, the management of user permissions is very important. By granting different levels of permissions, you can ensure that only authorized users can read, write, modify or delete data in the database, thereby ensuring data security and accuracy. When developing MySQL applications, be sure to follow best practices to prevent data leakage or corruption due to improper permission settings.

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