今日内容概述

1.MySQL权限表
2.MySQL创建用户
3.MySQL修改用户
4.MySQL删除用户
5.MySQL授权
6.MySQL查看，删除用户权限
7.修改用户密码

今日内容详细

MySQL 是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定不同权限。我们之前使用的是 root 用户，该用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户密码等管理权限。为了实际项目的需要，可以创建拥有不同权限的普通用户。

1.MySQL权限表

我们在安装完MySQL时，会自动创建一个名为mysql的数据库，mysql 数据库中存储的都是用户权限表。用户登录以后，MySQL会根据这些权限表的内容为每个用户赋予相应的权限。
user表是 MySQL 中最重要的一个权限表，用来记录允许连接到服务器的账号信息。需要注意的是，在 user 表里启用的所有权限都是全局级的，适用于所有数据库。user 表中的字段大致可以分为 4 类，分别是用户列、权限列、安全列和资源控制列。

1.1用户列

用户列存储了用户连接 MySQL 数据库时需要输入的信息。需要注意的是 MySQL 5.7 版本不再使用 Password 来作为密码的字段，而改成了 authentication_string。

用户登录时，如果这 3 个字段同时匹配，MySQL 数据库系统才会允许其登录。创建新用户时，也是设置这 3 个字段的值。修改用户密码时，实际就是修改 user 表的 authentication_string 字段的值。因此，这 3 个字段决定了用户能否登录。

1.2权限列

权限列的字段决定了用户的权限，用来描述在全局范围内允许对数据和数据库进行的操作。
权限大致分为两大类，分别是高级管理权限和普通权限：

• 高级管理权限主要对数据库进行管理，例如关闭服务的权限、超级权限和加载用户等；
• 普通权限主要操作数据库，例如查询权限、修改权限等。

user 表的权限列包括 Select_priv、Insert_ priv 等以 priv 结尾的字段，这些字段值的数据类型为 ENUM，可取的值只有 Y 和 N：Y 表示该用户有对应的权限，N 表示该用户没有对应的权限。从安全角度考虑，这些字段的默认值都为 N。

1.3安全列

安全列主要用来判断用户能否登录成功

需要注意的是：即使 password_expired 为“Y”，即密码以及过期，用户也可以使用密码登录MySQL，但是不允许做任何操作。系统会要求你修改密码。

1.4资源控制列

资源控制列的字段用来限制用户使用的资源

当这些字段的默认值为0时，表示没有限制，一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定，直到下一个小时才可以在此执行对应的操作。可以使用 GRANT 语句更新这些字段的值。

2.MySQL创建用户

MySQL 在安装时，会默认创建一个名为 root 的用户，该用户拥有超级权限，可以控制整个 MySQL 服务器。

在对 MySQL 的日常管理和操作中，为了避免有人恶意使用 root 用户控制数据库，我们通常创建一些具有适当权限的用户，尽可能地不用或少用 root 用户登录系统，以此来确保数据的安全访问。

MySQL 提供了以下 3 种方法创建用户。

1. 使用 CREATE USER 语句创建用户

2. 在 mysql.user 表中添加用户

3. 使用 GRANT 语句创建用户

2.1 使用 CREATE USER 语句创建用户

可以使用 CREATE USER 语句来创建 MySQL 用户，并设置相应的密码。其基本语法格式如下：
CREATE USER <用户> [ IDENTIFIED BY [ PASSWORD ] 'password' ] [ ,用户 [ IDENTIFIED BY [ PASSWORD ] 'password' ]]

# 用户：格式为 user_name'@'host_name
# IDENTIFIED BY子句 用于指定用户的密码，不设密码可以不写
# PASSWORD 'password'：PASSWORD 表示使用哈希值设置密码，该参数可选。

案例：
mysql> create user 'jerry'@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

2.2 使用 INSERT 语句新建用户

可以使用 INSERT 语句将用户的信息添加到 mysql.user 表中，但必须拥有对 mysql.user 表的 INSERT 权限。通常 INSERT 语句只添加 Host、User 和 authentication_string 这 3 个字段的值

案例：
mysql> insert into mysql.user(host, user, authentication_string, ssl_cipher, x509_issuer, x509_subject) values ('localhost', 'jerry01', PASSWORD('1'), '', '', '');
Query OK, 1 row affected, 1 warning (0.01 sec)

# 使用insert创建的用户需要刷新权限后才能登录
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

2.3使用GRANT语句新建用户

虽然 CREATE USER 和 INSERT INTO 语句都可以创建普通用户，但是这两种方式不便授予用户权限。于是 MySQL 提供了 GRANT 语句。
语法：
GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']
其中：
● priv_type 参数表示新用户的权限
● database.table 参数表示新用户的权限范围，即只能在指定的数据库和表上使用自己的权限
● user 参数指定新用户的账号，由用户名和主机名构成
● IDENTIFIED BY 关键字用来设置密码
● password 参数表示新用户的密码

案例：
grant select on *.* to 'jerry02'@'localhost' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 也需要刷新权限

3.MySQL修改用户

在 MySQL 中，我们可以使用 RENAME USER 语句修改一个或多个已经存在的用户账号。
语法格式如下：
RENAME USER <旧用户> TO <新用户>
其中：
● <旧用户>：系统中已经存在的 MySQL 用户账号。
● <新用户>：新的 MySQL 用户账号。

案例：
mysql> rename user 'jerry01'@'localhost' to 'jerryli'@'%';
Query OK, 0 rows affected (0.00 sec)

4.MySQL删除用户

在 MySQL 数据库中，可以使用 DROP USER 语句删除用户，也可以直接在 mysql.user 表中删除用户以及相关权限。

4.1 使用 DROP USER 语句删除普通用户

语法：
DROP USER <用户1> [ , <用户2> ]…

案例：
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| jerryli       | %         |
| lty           | %         |
| root          | %         |
| jerry         | localhost |
| jerry02       | localhost |
| lty07         | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| test01        | localhost |
| test02        | localhost |
| test03        | localhost |
| test07        | localhost |
+---------------+-----------+
13 rows in set (0.00 sec)

mysql> drop user 'test01'@'localhost','test07'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| jerryli       | %         |
| lty           | %         |
| root          | %         |
| jerry         | localhost |
| jerry02       | localhost |
| lty07         | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| test02        | localhost |
| test03        | localhost |
+---------------+-----------+
11 rows in set (0.00 sec)

4.2 使用DELETE语句删除普通用户

可以使用 DELETE 语句直接删除 mysql.user 表中相应的用户信息，但必须拥有 mysql.user 表的 DELETE 权限。

语法：
DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

Host 和 User 这两个字段都是 mysql.user 表的主键。因此，需要两个字段的值才能确定一条记录。
案例：

案例：
mysql> delete from mysql.user where host='localhost' and user='test03';
Query OK, 1 row affected (0.00 sec)

5.MySQL授权

在 MySQL 中，可以通过查看 mysql.user 表中的数据记录来查看相应的用户权限，也可以使用 SHOW GRANTS 语句查询用户的权限。

语法：
SHOW GRANTS FOR 'username'@'hostname';

案例：
mysql> show grants for 'jerry'@'localhost';
+-------------------------------------------+
| Grants for jerry@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'jerry'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

6.MySQL查看，删除用户权限

授权就是为某个用户赋予某些权限。例如，可以为新建的用户赋予查询所有数据库和表的权限。MySQL 提供了 GRANT 语句来为用户设置权限。
# 在实际工作中，我们可能需要为开发人员创建用户并授予适当的权限让他们自己玩。

在 MySQL 中，拥有 GRANT 权限的用户才可以执行 GRANT 语句，其语法格式如下：
GRANT priv_type [(column_list)] ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password'] [, user[IDENTIFIED BY [PASSWORD] 'password']] ... [WITH with_option [with_option]...]

其中：
● priv_type 参数表示权限类型；
● columns_list 参数表示权限作用于哪些列上，省略该参数时，表示作用于整个表；
● database.table 用于指定权限的级别；
● user 参数表示用户账户，由用户名和主机名构成，格式是“'username'@'hostname'”；
● IDENTIFIED BY 参数用来为用户设置密码；
● password 参数是用户的新密码。

WITH 关键字后面带有一个或多个 with_option 参数。这个参数有 5 个选项，详细介绍如下：
● GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；
● MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询；
● MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新；
● MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接;
● MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。

MySQL 中可以授予的权限有如下几组：
● 列权限，和表中的一个具体列相关。例如，可以使用 UPDATE 语句更新表 students 中 name 列的值的权限。
● 表权限，和一个具体表中的所有数据相关。例如，可以使用 SELECT 语句查询表 students 的所有数据的权限。
● 数据库权限，和一个具体的数据库中的所有表相关。例如，可以在已有的数据库 mytest 中创建新表的权限。
● 用户权限，和 MySQL 中所有的数据库相关。例如，可以删除已有的数据库或者创建一个新的数据库的权限。

对应地，在 GRANT 语句中可用于指定权限级别的值有以下几类格式：
● *：表示当前数据库中的所有表。
● *.*：表示所有数据库中的所有表。
● db_name.*：表示某个数据库中的所有表，db_name 指定数据库名。
● db_name.tbl_name：表示某个数据库中的某个表或视图，db_name 指定数据库名，tbl_name 指定表名或视图名。

6.1 权限类型

6.2 用户授权

使用 GRANT 语句创建一个新的用户 test7，密码为 test7。用户 testUser 对所有的数据有查询、插入权限，并授予 GRANT 权限。SQL 语句和执行过程如下。

案例：
mysql> grant select,insert on *.* to 'jerryli'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> show grants for 'jerryli'@'%';
+----------------------------------------------+
| Grants for jerryli@%                         |
+----------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'jerryli'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)

# grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效

# 一般给开发创建用户权限，建议不给delete权限
grant select,update,delete,insert on *.* to egon@'10.0.0.%'' identified by '123';
# 开发图方便想要root用户，这玩意，谁给谁背锅，自己斟酌，可以考虑让部门老大来给，你自己千万不要给！！！

6.3删除用户权限

# 删除某些特定用户的权限
语法：
REVOKE priv_type [(column_list)]... ON database.table FROM user [, user]...

其中：
● priv_type 参数表示权限的类型
● column_list 参数表示权限作用于哪些列上，没有该参数时作用于整个表上
● user 参数由用户名和主机名构成，格式为'username'@'hostname'

# 删除特定用户的所有权限
语法：
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

需要注意的是：
● REVOKE 语法和 GRANT 语句的语法格式相似，但具有相反的效果。
● 要使用 REVOKE 语句，必须拥有 MySQL 数据库的全局 CREATE USER 权限或 UPDATE 权限。

案例：
mysql> revoke insert on *.* from 'jerryli'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'jerryli'@'%';
+--------------------------------------+
| Grants for jerryli@%                 |
+--------------------------------------+
| GRANT SELECT ON *.* TO 'jerryli'@'%' |
+--------------------------------------+
1 row in set (0.00 sec)

7.修改用户密码

7.1MySQL登录和退出

启动 MySQL 服务后，可以使用以下命令来登录。
mysql -h hostname|hostlP -p port -u username -p DatabaseName -e "SQL语句"
-- 退出
exit
quit
\q

其中：
● -h：指定连接 MySQL 服务器的地址。可以用两种方式表示，hostname 为主机名，hostIP 为主机 IP 地址。
● -P：指定连接 MySQL 服务器的端口号，port 为连接的端口号。MySQL 的默认端口号是 3306，因此如果不指定该参数，默认使用 3306 连接 MySQL 服务器。
● -u：指定连接 MySQL 服务器的用户名，username 为用户名。
● -p：提示输入密码，即提示 Enter password。
● DatabaseName：指定连接到 MySQL 服务器后，登录到哪一个数据库中。如果没有指定，默认为 mysql 数据库。
● -e：指定需要执行的 SQL 语句，登录 MySQL 服务器后执行这个 SQL 语句，然后退出 MySQL 服务器。

7.2修改用户密码

# 使用SET语句修改普通用户的密码
在 MySQL 中，只有 root 用户可以通过更新 MySQL 数据库来更改密码。使用 root 用户登录到 MySQL 服务器后，可以使用 SET 语句来修改普通用户密码。

案例：
mysql> set password for 'jerry'@'localhost' = password ('123456'); 
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 使用UPDATE语句修改普通用户的密码
使用 root 用户登录 MySQL 服务器后，可以使用 UPDATE 语句修改 MySQL 数据库的 user 表的 authentication_string 字段，从而修改普通用户的密码。

案例：
update mysql.user set authentication_string = PASSWORD("123456") where User = "test07" and Host = "localhost";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

# 使用mysqladmin命令在命令行修改root密码
root用户可以使用 mysqladmin 命令来修改密码
mysqladmin -u username -h hostname -p password "newpwd"
语法参数说明如下：
● usermame 指需要修改密码的用户名称，在这里指定为 root 用户；
● hostname 指需要修改密码的用户主机名，该参数可以不写，默认是 localhost；
● password 为关键字，而不是指旧密码；
● newpwd 为新设置的密码，必须用双引号括起来。如果使用单引号会引发错误，可能会造成修改后的密码不是你想要的。

案例：
mysqladmin -uroot -p123456 password 555666

# 使用 GRANT 语句修改普通用户密码
除了前面介绍的方法，还可以在全局级别使用 GRANT USAGE 语句指定某个账户的密码而不影响账户当前的权限。需要注意的是，使用 GRANT 语句修改密码，必须拥有 GRANT 权限。一般情况下最好使用该方法来指定或修改密码。

# 修改MySQL数据库的user表
因为所有账户信息都保存在 user 表中，因此可以直接通过修改 user 表来改变 root 用户的密码

案例：
update mysql.user set authentication_string = PASSWORD ("123456") WHERE User = "root" and Host="localhost";

刷新权限：
FLUSH PRIVILEGES;

# root密码忘记了，如何重置？
上面的修改密码的方式都建立在知晓密码的情况下，若我们忘记了密码该怎么办？

我们可以通过跳过授权表的方式
vim /etc/my.cnf
在[mysqld]下添加一条指令：
skip_grant_tables
保存退出即可

mysql -uroot 	# 就可以不用输入密码了

进入mysql命令行后我们使用之前的几种方法修改密码即可
UPDATE mysql.user set authentication_string = PASSWORD ("123456") WHERE User = "root" and Host="localhost";

修改完密码后记得将配置文件中跳过授权表的语句删除，重启mysql即可。