How to assign different permissions to users in MySQL

MySQL is a common relational database management system that can be used to store and manage large amounts of data. In MySQL, administrators need to assign different permissions to different users so that they can perform different operations on different databases and tables. This article will introduce how to assign different MySQL permissions to users.

1. Log in to MySQL

Enter the following command in the command line interface to log in to MySQL:

mysql -u root -p

The "-u" option is used to specify the The user name used, "root" is the administrator user name; the "-p" option instructs MySQL to require the user to provide a password before logging in. When logging into MySQL, you need to provide the correct password.

2. Create user

Use the following command to create a new user:

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

Where "new_user" is the username of the new user, you need to Users are assigned an easy-to-remember username rather than using random names. "localhost" means restricting this user's IP address to localhost only. If you want this user to access the database from an external host, use the "%" symbol instead. Finally, "password" is the new user's password, and a secure password needs to be set for the new user.

3. Assigning permissions

Assigning permissions includes two aspects: one is to assign permissions to the database and tables that the user can operate, and the other is to assign the MySQL Server permissions to the user. Global permissions.

Grant users specific database and table permissions:

GRANT 权限 ON 数据库名.数据表名 TO 'user_name'@'访问来源';

Among them, "permissions" refer to the specific operation permissions granted, such as SELECT, INSERT, DELETE, UPDATE, ALL PRIVILEGES, etc.; " user_name" is the user name to which permissions are to be assigned; "access source" refers to the access source of the user. For example, 'localhost' means that it is on the local host, and the '%' symbol means that the user can access the resource from any address.

Examples of statements to grant database operation permissions:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER
ON test_db.*
TO 'new_user'@'localhost';

Examples of statements to grant table operation permissions:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER
ON test_db.test_table
TO 'new_user'@'localhost';

Grant global permissions to users on MySQL Server:

GRANT 权限 ON *.* TO 'user_name'@'access_mode';

Among them, "." represents global permissions. For example, use the following command to grant a new user all permissions on all databases and tables:

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

4. Revoke Permissions

Similarly, the administrator needs to revoke permissions from a user When, you can use the following statement:

REVOKE 权限 ON 数据库名.数据表名 FROM 'user_name'@'访问来源';

The syntax is similar to GRANT. For example, the following command will revoke all global permissions from user "new_user":

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

Summary

In MySQL, administrators need to assign different permissions to different users so that they can Different databases and tables perform different operations. By using the GRANT and REVOKE statements, administrators can control what actions users can perform.

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