Home > Article > Database > What are the user management techniques for MySQL?

What are the user management techniques for MySQL?

PHPzOriginal: 2023-07-31 21:21:21792browse

MySQL is a popular relational database management system that is widely used in various web applications and software development. In MySQL, user management is very important, which involves user permission assignment, password management, and security considerations. This article will introduce some MySQL user management skills to help you better manage your MySQL database.

Create User

In MySQL, you can use the CREATE USER statement to create a new user. The basic syntax of the CREATE USER statement is as follows:

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

Where, 'username' is the username to be created, 'hostname' refers to the hostname or IP address that grants access to this username, and 'password' is the user's password.

For example, to create a user named 'john' with the password 'password' and allow the user to log in from any host, you can execute the following command:

CREATE USER 'john' @'%' IDENTIFIED BY 'password';

Authorized user permissions

In MySQL, users are granted different permissions through the GRANT statement. The basic syntax of the GRANT statement is as follows:

GRANT ON .

TO 'username'@'hostname';

Where, is The permissions to be authorized can be specific permissions, such as SELECT, INSERT, UPDATE, etc. You can also use ALL to indicate all permissions. and

are the database and table names to be authorized.

For example, to authorize user 'john' to have SELECT and INSERT permissions on table 'table1' in database 'mydb', you can execute the following command:

GRANT SELECT, INSERT ON mydb.table1 TO 'john'@'%';

Revoke user permissions

If you need to revoke a user's permissions, you can use the REVOKE statement. The basic syntax of the REVOKE statement is as follows:

REVOKE ON .

FROM 'username'@'hostname';

For example, to revoke user 'john 'With INSERT permission on table 'table1' in database 'mydb', you can execute the following command:

REVOKE INSERT ON mydb.table1 FROM 'john'@'%';

Change user password

In order to protect the security of user data, it is necessary to change user password regularly. In MySQL, you can use the SET PASSWORD statement to modify the user password. The basic syntax of the SET PASSWORD statement is as follows:

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

For example, to change the password of user 'john' to 'newpassword', you can execute the following command:

SET PASSWORD FOR 'john'@'%' = PASSWORD('newpassword');

Delete user

If a user no longer needs to access the MySQL database, you can use the DROP USER statement to delete the user. The basic syntax of the DROP USER statement is as follows:

DROP USER 'username'@'hostname';

For example, to delete user 'john', you can execute the following command:

DROP USER 'john'@'%';

To sum up, MySQL user management is an important part of database management. When creating a user, a strong password should be set; when granting user permissions, the minimum permissions should be given as needed; when revoking user permissions, clear out permissions that are no longer needed; and regularly modify user passwords to protect data security. ; When the user no longer needs to access the database, delete the user in time. These tips will help you better manage your MySQL database and protect your data security.

Code example:

--Create user 'john' and set password
CREATE USER 'john'@'%' IDENTIFIED BY 'password';

- - Authorize user 'john' to have SELECT and INSERT permissions on table 'table1' in database 'mydb'
GRANT SELECT, INSERT ON mydb.table1 TO 'john'@'%';

-- Revoke User 'john''s INSERT permission on table 'table1' in database 'mydb'
REVOKE INSERT ON mydb.table1 FROM 'john'@'%';

-- Modify the password of user 'john' For 'newpassword'
SET PASSWORD FOR 'john'@'%' = PASSWORD('newpassword');

-- Delete user 'john'
DROP USER 'john'@'%' ;

The above is the detailed content of What are the user management techniques for MySQL?. For more information, please follow other related articles on the PHP Chinese website!

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：How to implement polymorphic storage and multidimensional query of data in MySQL?Next article：How to implement polymorphic storage and multidimensional query of data in MySQL?

See more

What are the user management techniques for MySQL?

Related articles