User and Rights Management for MySQL

In MySQL 5.7.24, information about users and user permissions is stored in the user table of mysql library, which can be roughly divided into user column, permission column, security column and resource control column.

1. User Columns
The user column of the user table includes Host, User, and password, representing the host name, user name, and password, respectively.User and Host are the joint masters of the user table.When a connection is established between a user and a server, the user name, host name and password in the account information entered must match the corresponding field in the user table. Connection establishment is only allowed if all three values match.The values of these three fields are the account information that was saved when the account was created.When you modify a user's password, you are actually modifying the password field value of the user table.

2. Permission Columns
The fields in the permission column determine the user's permissions and describe the operations that are allowed on the data and database globally.This includes common privileges such as query privileges, modify privileges, and advanced privileges such as shutting down the server, super privileges, and loading users.General privileges are used to operate databases; advanced privileges are used for database management.The corresponding permissions in the user table are for all user databases.These field values are of type ENUM and can only be taken as Y and N, Y means that the user has the corresponding permissions, N means that the user does not have the corresponding permissions.Looking at the structure of the user table, you can see that the values of these fields are all N by default.If you want to modify permissions, you can use GRANT or UPDATE statements to change these fields of the user table to modify the user's corresponding permissions.

3. Security Columns
The security column has only six fields, two of which are SSI related, two of which are x509 related, and two of which are authorization plug-in related.SSI is used for encryption; X509 standard can be used to identify users: Plugin field identifies a plug-in that can be used to authenticate users, and if the field is empty, the server uses the built-in authorization authentication mechanism to authenticate users.The SHOW VARIABLES LIKE'have_openssl'statement can be used to query whether the server supports SSI functionality.
4. Resource Control Columns

The fields of the resource control column are used to limit the resources used by users and contain four fields:
1) Max_questions - Number of query operations the user is allowed to perform per hour.
2) Number of update operations that a Max_updates user is allowed to perform per hour.
3) Max_connections - Number of connection operations allowed per hour by the user.
4) Max_user_connections - Number of simultaneous connections allowed by the user.

If the number of user queries or connections exceeds the resource control limit within one hour, the user will be locked until the next hour before the corresponding operation can be performed here.You can use the GRANT statement to update the values of these fields.
Note: If the newly created user cannot log on to the database, he or she may try to refresh the permissions, excluding the permissions error. The command is as follows:

mysql> flush privileges;

When using grant to authorize a user, you can use the following instructions to see which permissions can be authorized to the user:

mysql> SHOW PRIVILEGES;        

1. Create Users

1. Create a new user using the create user statement
1) Method 1:

mysql> create user 'tom'@'localhost' identified by '';

The above statement creates a tom user, allows only local login, and has a password of

However, in the above way, the password is written in plain text (although stored in cipher), but there are some insecurities. The above instructions will generate tom information in the mysq.user table, as follows:

mysql> select * from user where User='tom'\G          # Query columns whose User field is tom
*************************** 1. row ***************************
                  Host: localhost                   # Which host is allowed to log in from
                  User: tom                     # User name
                                                                # Below are permissions,'N'means no corresponding permissions,'Y' means having corresponding permissions
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *AC241830FFDDC8943AB31CBD47D758E79F7953EA                # This line is a hashed password for
      password_expired: N
 password_last_changed: 2020-05-16 22:05:58
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

2) Method 2:

mysql> select password('');         # Encrypt
| password('')                       |
| *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
1 row in set, 1 warning (0.00 sec)

mysql> create user 'zhangyi'@'localhost'                 # Use the encrypted password to create the user
    -> identified by password '*AC241830FFDDC8943AB31CBD47D758E79F7953EA';

The above command encrypts the string first, then when creating the user, write the encrypted password directly, but add the password keyword
Once created, Zhangngyi users can log in to the database using the password locally.
2. Create a new user using the grant statement

mysql> grant select on test1.* to 'zhanger'@'localhost' identified by '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

The above directive, while creating user zhanger, specifies that it only allows local login with a password of and that the user has select privileges on all tables in the test01 library.

[root@mysql~]# mysql -u zhanger
mysql> show databases; 
| Database           |
| information_schema |
| test1              |
2 rows in set (0.00 sec)
mysql> use test1;                         # Cut in to a privileged Library
Database changed
mysql> show tables;                    # View all tables
| Tables_in_test1 |
| tb1             |
1 row in set (0.00 sec)
mysql> select * from tb1;           # View a table's contents
| id   |
|    1 |
1 row in set (0.00 sec)

mysql> update tb1 set id=2  ;                # An attempt was made to update the table contents, but they were rejected because they had only select privileges
ERROR 1142 (42000): UPDATE command denied to user 'zhanger'@'localhost' for table 'tb1'

2. Delete Users

1. Delete using DROP USER statement
mysql> drop user zhangyi@localhost;
2. Use delete statement to delete user

mysql> delete from mysql.user where Host='localhost' and user='tom';

3. Modify User Password

1. Use the mysqladmin command at the command line to change the user password

[root@mysql ~]# mysqladmin -uroot -p password ''           # Change root user's password to
Enter password:                    # Note, fill in the old password here
[root@mysql ~]# mysql -u root -p123                # The old password can no longer be logged in at this time
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@mysql ~]# mysql -u root              # Sign in with a new password

2. Modify the user table of MySQL database

# Modify root password to
mysql> update mysql.user set authentication_string=password('') where User='root' and Host='localhost';
mysql> flush privileges;

3. Use the set statement to modify the current user's password

mysql> set password=password('');

4. Root User Modifies Ordinary User Password
1) Use set statements to modify normal users

# Modify zhanger user password to 123 456
mysql> set password for 'zhanger'@'localhost'=password('123456');

2) Use update statements to modify common users

#Modify the zhanger user password to
mysql> update mysql.user set authentication_string=password('')
    -> where User='zhanger' and Host='localhost';
mysql> flush privileges;          # A refresh is required to take effect

3) Use grant statements to modify ordinary user passwords

#Modify the password of the zhanger user to 123 123 and grant select privileges
mysql> grant select on *.* to 'zhanger'@'localhost' identified by '123123';

5. Ordinary users change their passwords

[root@mysql ~]# mysql -uzhanger -p123123           # Sign in
mysql> set password=password('');             # Modify password to

6. Revoke the rights of ordinary users
Now that you have written how to authorize the user, here you will write how to revoke the user's existing privileges.The following:

mysql> show grants for zhanger@localhost\G           # View user-owned permissions
mysql> revoke select on *.* from 'zhanger'@'localhost';            #Revoke the user's select privilege
 mysql> flush privileges;                  # Refresh Permissions

Tags: MySQL Database mysqladmin

Posted on Sat, 16 May 2020 15:40:18 -0400 by sspoke