This article describes the MySQL user management operation. Share with you for your reference, the details are as follows:

In this article:

• User introduction
• View Users
• Create a User Account
• Modify Account
• Deleting an Account
• About Anonymous Users

Release date: 2018-04-19

User's introduction:

• The mysql client connects to the server using the username.
• The server can change the user's permissions, so each user has different permissions for the database or data table.
• Generally speaking, you should not log in as the root user, because the root user has the highest permissions and can perform "dangerous" operations such as deleting the database. For security reasons, you should log in using another user and assign him appropriate permissions.
• And the user should have a password. Using an anonymous user (without a password) is very dangerous. If this anonymous user is open to remote login, then others can log in to your MySQL as long as they detect that your port is open.

View users:

• MySQL user accounts and information are stored in a MySQL database named mysql.
• The mysql database has a table called user which contains all user accounts. The user table has a column called user, which stores the user login name.
• • 
• When using select * from user\G to get complete table data
• • The suffix priv means permission, and N stands for none.
  • host represents the host that is allowed to log in, and % allows local and non-local hosts to log in
  • user is the user name
  • The authentication string is a hash of the password.

Create a user account:

• Method 1: create user username@login address identified by 'password';
  • No login address means all addresses can log in
  • If only local logins are allowed, this should be set to localhost
• Method 2: grant permissions on database.table to username@login address identified by 'password';
  • 
• Method 3: You can use Insert to directly insert data into the user table, but it is not recommended.

Modify account:

• Rename user: rename user original username@accessible address to new username@accessible address; [The accessible address of the original username is required, while the accessible address of the new username is optional, but if it is not filled in, it will default to % which means remote login is allowed]
• Change password: set password for 'username'@accessible address = password('password'); [To change the password of the current user without specifying a username] [Accessible address is optional and must be the original accessible address]
• • 
• Modifying account permissions will be covered in another blog post. Hyperlink: MySQL user rights management

Deleting an Account:

• drop user username;

Replenish:

• Before MySQL 5.0, the drop user command only deletes the user but not the permissions. Before 5.0, you need to use revoke to delete the permissions first.

About anonymous user:

• In the old version, there is an anonymous user by default, which allows you to log in to MySQL without using a user and password.
• How to create an anonymous user
• Generally, anonymous users should not be created. Even if they are created for learning convenience, they should be set to local.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.