preface

Today, Xiao Wang, a programmer, was scolded by his boss and his salary was deducted by 1k. The reason is that a project has been launched. The customer asked to add a new user to the database and give the user some permissions. However, Xiao Wang is a little rusty in this regard, which is the reality of Baidu, resulting in low work efficiency and the dissatisfaction of the boss.
Xiao Wang learned from the painful experience and made up his mind to understand the creation, user and authorization of mysql. After consulting various materials and learning, Xiao Wang knew about this almost ten times, so he became hard in front of the boss

1, Create a new user

Boss: create a new user joytom for me. The password is set to 123321 and can be accessed by any remote host. It can be completed in five minutes. If it can't be realized, give me the bucket and leave!

With a knowing smile, Xiao Wang was already familiar with the command to create users, so he skillfully operated:

1. Create user command:

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

2. Create user

mysql> CREATE USER 'joytom'@'%' IDENTIFIED BY '123321';
Query OK, 0 rows affected (0.00 sec)

Check whether the creation is successful:

mysql> select user,host from user;
+----------+---------------+
| user     | host          |
+----------+---------------+
| copytest | %             |
| joytom   | %             |
| test     | %             |
| root     | 127.0.0.1     |
| root     | ::1           |
|          | localhost     |
| root     | localhost     |
|          | vm-8-5-centos |
| root     | vm-8-5-centos |
+----------+---------------+
10 rows in set (0.00 sec)

3. Log in remotely from another server:

[root@instance-lzmtqrkn ~]# mysql -h creates the public IP address of the user's server - P 3306 - u joytom - p123321

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 784
Server version: 5.6.49-log Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.03 sec)

Check the database and find that you don't have permission. You can only see information_schema database.

2, Authorize users

After creating the user

Boss: OK, it's better than before. Then you can set a permission for the user joytom to only query and modify the student table in the copytest database.

Xiao Wang handled it skillfully:

1. Authorize commands to users

grant privileges on database.tablename to "username"@'host';

privileges: the user's operation permissions, such as SELECT, INSERT, UPDATE, etc. if you want to grant the required permissions, use ALL.

2. Grant joytom users permission to check and modify.

mysql> grant select,update on copytest.student to "joytom"@'%';
Query OK, 0 rows affected (0.00 sec)

3. Another server to test:

It is found that the copytest database can be seen:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| copytest           |
+--------------------+
2 rows in set (0.04 sec)

Check the student table in the copytest database:

mysql> use copytest;
Database changed
mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | king   |
|  2 | Lee   |
|  3 | Zhang   |
+----+------+
3 rows in set (0.04 sec)

Modify the student table:

mysql> update student set name = 'Xiao Wang' where id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | Xiao Wang   |
|  2 | Lee     |
|  3 | Zhang     |
+----+--------+
3 rows in set (0.04 sec)

What about deleting the data in the student table

mysql> delete from student where id = 1;
ERROR 1142 (42000): DELETE command denied to user 'joytom'@'xxxxxx' for table 'student'

It is found that there is no permission to delete, so you can only query and modify.

4. Adding a view permission to the joytom user

mysql> grant SHOW VIEW on copytest.student to "joytom"@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Previously, you only had the permission to query and modify. Now you can check it:

mysql> show grants for 'joytom'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for joytom@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'joytom'@'%' IDENTIFIED BY PASSWORD '*437F1809645E0A92DAB553503D2FE21DB91270FD' |
| GRANT SELECT, UPDATE, SHOW VIEW ON `copytest`.`student` TO 'joytom'@'%'                               |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

It is found that you have permission to view the view.

3, Revoke user rights

Boss: cough, good. Now joytom has three permissions (query, modify and query view). You can remove the permission of query view and leave only query and modification.

Xiao Wang was secretly pleased that I had learned all this. It's very basic

1. Revoke user permission command

revoke privileges ON database.tablename FROM 'username'@'host';

Revokes are basically the same as grants, except revoke s (corresponding to grant) and from (corresponding to to to)

2. Revoke the permission of the joytom user to view the view

mysql> revoke SHOW VIEW on copytest.student from "joytom"@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

View again:

mysql> show grants for 'joytom'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for joytom@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'joytom'@'%' IDENTIFIED BY PASSWORD '*437F1809645E0A92DAB553503D2FE21DB91270FD' |
| GRANT SELECT, UPDATE ON `copytest`.`student` TO 'joytom'@'%'                                          |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

It is found that you no longer have permission to view the view.

grant, revoke user permission, the user must reconnect to the MySQL database before the permission can take effect.

4, Delete a user

Boss: delete joytom and let me have a look.

Xiao Wang: OK, it's done in 40 seconds.

1. Delete user command

drop user username@host

2. Delete user

Check all current users first:

mysql> select user,host from user;
+----------+---------------+
| user     | host          |
+----------+---------------+
| copytest | %             |
| joytom   | %             |
| test     | %             |
| root     | 127.0.0.1     |
| root     | ::1           |
|          | localhost     |
| root     | localhost     |
|          | vm-8-5-centos |
| root     | vm-8-5-centos |
+----------+---------------+
10 rows in set (0.00 sec)

Delete joytom:

mysql> drop user joytom@'%';
Query OK, 0 rows affected (0.00 sec)

Check again and find that there is no joytom user:

mysql> select user,host from user;
+----------+---------------+
| user     | host          |
+----------+---------------+
| copytest | %             |
| test     | %             |
| root     | 127.0.0.1     |
| root     | ::1           |
|          | localhost     |
| root     | localhost     |
|          | vm-8-5-centos |
| root     | vm-8-5-centos |
+----------+---------------+
10 rows in set (0.00 sec)

5, Change the user's password

Boss: change the password of joytom.

Xiao Wang: OK, boss.

1. Modify user password command

set PASSWORD FOR 'username' @ '%' = PASSWORD('password to modify ')

2. Modify user

SET PASSWORD FOR 'joytom'@'%' = PASSWORD('123123');

6, Password expiration and user lockout

The boss is very happy: very good, very good. Pay 2k, keep working hard, and tell other colleagues about password expiration and user locking.

Thank you, boss. I will continue to work hard. I'll tidy up the courseware now.

1. About password expiration

On mysql5 Since version 6.6, password has been added_ Expired function, which allows you to set the password expiration time of MySQL database users. This feature has been added to MySQL User data table, whose default value is "N", indicating that the password expiration function has been disabled

Force password Expiration:

mysql> ALTER USER 'joytom'@'%' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.00 sec)

After the mandatory password expires, although you can log in, all permissions are empty.

2. About mysql5 7 lock user

Lock user when creating:

CREATE USER 'username'@'host' account unlock;

Lock users when they already exist:

ALTER USER 'joytom'@'%' ACCOUNT LOCK;

Unlock account:

ALTER USER 'joytom'@'%' ACCOUNT UNLOCK

7, Common keywords of permission

Boss: now you have a fairly good command of mysql permission management. Cough, give me a common keyword of permission before work today. If you can't sort it out well, you can add classes.

Xiao Wang thought, fortunately, I've sorted this out when I was studying. It looks like I don't have to work overtime today!