How to use SQL statements for data permissions and user management in MySQL?

How to use SQL statements for data permissions and user management in MySQL?

Introduction:
Data permissions and user management are very important links in database management. In the MySQL database, data permission control and user management can be easily carried out through SQL statements. This article will introduce in detail how to use SQL statements for database permissions and user management in MySQL.

1. Data permission management

1. Create users and authorize
   In MySQL, you can create new users through the CREATE USER statement, and use the GRANT statement to authorize the user.

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

The above statement creates a user named username and specifies the password as password. Among them, 'localhost' means that the user can only access the database locally. If you want to allow remote access, you can use '%'.

GRANT privileges ON database.table TO 'username'@'localhost';

In the above statement, privileges represents authorized permissions, which can be SELECT, INSERT, UPDATE, etc. You can also use ALL to represent all permissions. database.table represents the database and table name to be authorized.

2. Revoke user permissions
   If you want to revoke a user's permissions, you can use the REVOKE statement.

REVOKE privilege ON database.table FROM 'username'@'localhost';

In the above statement, privilege, database and table have the same meaning as in the authorization statement.

3. Refresh permissions
   After modifying user permissions in MySQL, you need to use the FLUSH PRIVILEGES statement to refresh the permissions so that the modification takes effect immediately.

FLUSH PRIVILEGES;

2. User management

1. View user list
   Use the following statement to view the user list in the current database.

SELECT user, host FROM mysql.user;

2. Modify user password
   Use the following statement to modify the user's password.

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('newpassword');

In the above statement, 'username'@'localhost' represents the user who wants to change the password, and newpassword represents the new password.

3. Delete User
   Use the following statement to delete a user.

DROP USER 'username'@'localhost';

In the above statement, 'username'@'localhost' indicates the user to be deleted.

3. Example
The following is a complete example that demonstrates how to create users, authorize, change passwords and delete users.

-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

-- 授予SELECT权限
GRANT SELECT ON testdb.* TO 'newuser'@'localhost';

-- 修改密码
SET PASSWORD FOR 'newuser'@'localhost' = PASSWORD('newpassword');

-- 删除用户
DROP USER 'newuser'@'localhost';

Summary:
Through the above introduction, we can see that it is very simple to use SQL statements for data permissions and user management in MySQL. Through statements such as CREATE USER, GRANT, REVOKE, SET PASSWORD and DROP USER, we can easily create users, authorize, revoke permissions, change passwords and delete users. These features provide database administrators with powerful management tools to ensure the safe and reliable operation of the database.

The above is the detailed content of How to use SQL statements for data permissions and user management in MySQL?. For more information, please follow other related articles on the PHP Chinese website!