mysql set user

MySQL is a commonly used database, and many websites use MySQL as a data storage tool. When using MySQL, one or more users must be set up to access the database and the permissions of these users must be configured. This article explains how to set up users and their permissions in MySQL.

1. Log in to MySQL

Before setting up a MySQL user, you need to log in to MySQL with an administrator account. Open the terminal and enter the following command:

mysql -u root -p

Among them, "root" is the administrator account. After entering the command, you will be prompted to enter the password. After entering the password, you can enter the MySQL system.

2. Add new users

The command to add new users in MySQL is CREATE USER. The syntax is as follows:

CREATE USER 'user_name'@'host' IDENTIFIED BY 'password';

Among them, user_name is the user name you want to create, host is the host address that the user can access, and password is the user's password. If the user can connect to the MySQL server from any host address, host can be set to %. For example, to create a user named testuser and set its password to testpassword, you can use the following command:

CREATE USER 'testuser'@'%' IDENTIFIED BY 'testpassword';

3. Delete user

The command to delete a user in MySQL is DROP USER. The syntax is as follows:

DROP USER 'user_name'@'host';

Among them, user_name is the user name you want to delete, and host is the host address that the user can access. For example, delete the user named testuser:

DROP USER 'testuser'@'%';

4. Change the password

The command to change the user password in MySQL is ALTER USER. The syntax is as follows:

ALTER USER 'user_name'@'host' IDENTIFIED BY 'new_password';

Among them, user_name is the user name whose password you want to change, host is the host address that the user can access, and new_password is the new password of the user. For example, change the password of the user named testuser to newpassword:

ALTER USER 'testuser'@'%' IDENTIFIED BY 'newpassword';

5. Authorization

The authorized command in MySQL is GRANT. Authorization is the process of assigning access rights to users. The syntax is as follows:

GRANT privileges ON database.table TO 'user_name'@'host';

Among them, privileges are the database operations that the user can perform, including SELECT, INSERT, UPDATE, DELETE, etc. database.table is the database and table that the user can access, user_name is the user name of the user, and host is the host address that the user can access. For example, grant SELECT and INSERT permissions to a user named testuser:

GRANT SELECT, INSERT ON testdb.* TO 'testuser'@'192.168.1.%';

6. Recover permissions

The command to revoke user permissions in MySQL is REVOKE. The syntax is as follows:

REVOKE privileges ON database.table FROM 'user_name'@'host';

Among them, privileges are the permissions owned by the user, database.table is the database and table that the user can access, user_name is the user name of the user, and host is the host address that the user can access. . For example, to reclaim the INSERT permissions of the user named testuser:

REVOKE INSERT ON testdb.* FROM 'testuser'@'192.168.1.%';

7. View permissions

The command to view user permissions in MySQL is SHOW GRANTS. The syntax is as follows:

SHOW GRANTS FOR 'user_name'@'host';

Among them, user_name is the user name whose permissions are to be viewed, and host is the host address that the user can access. For example, check the permissions of the user named testuser:

SHOW GRANTS FOR 'testuser'@'%';

The above is the operation method of setting users and their permissions in MySQL. Through these commands, you can easily manage database users and their permissions to ensure the security of the database. .

The above is the detailed content of mysql set user. For more information, please follow other related articles on the PHP Chinese website!