In MySQL, user data is stored in the user table of MySQL library, which stores the user name, password and permission information of the user.

Create a user

Command: CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;

Description:

  • Username: indicates the login username
  • Password: indicates the login password
  • Host: specifies the host that can be logged in to. Localhost indicates the localhost, and % indicates all hosts

For example:

CREATE USER 'testuser'@'%' IDENTIFIED BY '123';
Copy the code

After a user is created, a new row is inserted into the User table, but the user does not have any permissions

Managing User Rights

Adding User Rights

GRANT PRIVILEGES ON databasename. Tablename TO ‘username’@’host’

  • Privileges: Privileges of the user, such as SELECT, INSERT, UPDATE. To grant all privileges, use all privileges
  • Databasename: Specifies the name of the database to which permissions are to be granted, if permissions are to be granted to all libraries*Instead of
  • Tablename: Specifies the name of the table to which permissions are to be granted, if permissions are to be granted to all tables*Instead of
  • Username: indicates the name of the user whose permission is granted
  • Host: the host to which permission is granted. Use % instead to grant permission to all hosts

For example:

GRANT SELECT ON test.* TO 'testuser'@'%'; Y GRANT INSERT ON *.* TO 'testuser'@'%'; GRANT ALL PRIVILEGES ON *.* TO 'testUser '@'%'; After executing the command granting privileges, you must execute the following command for the changes to take effectCopy the code

Revoking user rights

Command: REVOKE PRIVILEGES ON databasename. Tablename FROM ‘username’@’host’;

Example: REVOKE ALL PRIVILEGES ON *.* FROM ‘testuser’@’%’;

The method of revoking a permission is similar to that of granting a permission.

Changing a User password

SET PASSWORD FOR ‘username’@’host’ = PASSWORD(‘newpassword’);

Example: SET PASSWORD FOR ‘testUser ‘@’%’ = PASSWORD(‘abcdef’);

Check the permissions

To check the privileges of this user: SHOW GRANTS;

Select from ‘username’@’host’;

For example:

* select * from 'testuser'@'%'Copy the code

Delete user

DROP USER ‘username’@’host’;

Example: DROP USER ‘testuser’@’%’;

After a user is deleted, the user’s data does not exist in the user table. CREATE user’ testUser ‘@’10.1.1.1’ IDENTIFIED BY ‘password’; CREATE user’ testUser ‘@’10.1.1.1’ IDENTIFIED BY ‘password’; And CREATE USER ‘testuser’@’10.1.1.2’ IDENTIFIED BY ‘password’; These two statements insert two pieces of information into the user table. Therefore, users can only be deleted in two times.

conclusion

This article introduces some common MySQL user management commands, including create user, add user permissions, delete user permissions, modify user passwords, query user permissions. In addition, the user table is also a data table, so you can use the MySQL SQL statement to operate the table, for example, DELETE FORM user WHERE user = testuser. Of course, this method is not recommended, there is a greater risk of misoperation.