MySQL权限管理

今日内容概述

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 个字段决定了用户能否登录。

字段名	字段类型	是否为空	默认值	说明
Host	char(60)	NO	无	主机名
User	char(32)	NO	无	用户名
authentication_string	text	YES	无	密码

1.2权限列

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

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

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

字段名	字段类型	是否为空	默认值	说明
Select_priv	enum('N','Y')	NO	N	是否可以通过SELECT 命令查询数据
Insert_priv	enum('N','Y')	NO	N	是否可以通过 INSERT 命令插入数据
Update_priv	enum('N','Y')	NO	N	是否可以通过UPDATE 命令修改现有数据
Delete_priv	enum('N','Y')	NO	N	是否可以通过DELETE 命令删除现有数据
Create_priv	enum('N','Y')	NO	N	是否可以创建新的数据库和表
Drop_priv	enum('N','Y')	NO	N	是否可以删除现有数据库和表
Reload_priv	enum('N','Y')	NO	N	是否可以执行刷新和重新加载MySQL所用的各种内部缓存的特定命令，包括日志、权限、主机、查询和表
Shutdown_priv	enum('N','Y')	NO	N	是否可以关闭MySQL服务器。将此权限提供给root账户之外的任何用户时，都应当非常谨慎
Process_priv	enum('N','Y')	NO	N	是否可以通过SHOW PROCESSLIST命令查看其他用户的进程
File_priv	enum('N','Y')	NO	N	是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令
Grant_priv	enum('N','Y')	NO	N	是否可以将自己的权限再授予其他用户
References_priv	enum('N','Y')	NO	N	是否可以创建外键约束
Index_priv	enum('N','Y')	NO	N	是否可以对索引进行增删查
Alter_priv	enum('N','Y')	NO	N	是否可以重命名和修改表结构
Show_db_priv	enum('N','Y')	NO	N	是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库
Super_priv	enum('N','Y')	NO	N	是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程；使用SET GLOBAL命令修改全局MySQL变量，执行关于复制和日志的各种命令。（超级权限）
Create_tmp_table_priv	enum('N','Y')	NO	N	是否可以创建临时表
Lock_tables_priv	enum('N','Y')	NO	N	是否可以使用LOCK TABLES命令阻止对表的访问/修改
Execute_priv	enum('N','Y')	NO	N	是否可以执行存储过程
Repl_slave_priv	enum('N','Y')	NO	N	是否可以读取用于维护复制数据库环境的二进制日志文件
Repl_client_priv	enum('N','Y')	NO	N	是否可以确定复制从服务器和主服务器的位置
Create_view_priv	enum('N','Y')	NO	N	是否可以创建视图
Show_view_priv	enum('N','Y')	NO	N	是否可以查看视图
Create_routine_priv	enum('N','Y')	NO	N	是否可以更改或放弃存储过程和函数
Alter_routine_priv	enum('N','Y')	NO	N	是否可以修改或删除存储函数及函数
Create_user_priv	enum('N','Y')	NO	N	是否可以执行CREATE USER命令，这个命令用于创建新的MySQL账户
Event_priv	enum('N','Y')	NO	N	是否可以创建、修改和删除事件
Trigger_priv	enum('N','Y')	NO	N	是否可以创建和删除触发器
Create_tablespace_priv	enum('N','Y')	NO	N	是否可以创建表空间

1.3安全列

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

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

字段名	字段类型	是否为空	默认值	说明
ssl_type	enum('','ANY','X509','SPECIFIED')	NO	支持ssl标准加密安全字段
ssl_cipher	blob	NO	支持ssl标准加密安全字段
x509_issuer	blob	NO	支持x509标准字段
x509_subject	blob	NO	支持x509标准字段
plugin	char(64)	NO	mysql_native_password	引入plugins以进行用户连接时的密码验证，plugin创建外部/代理用户
password_expired	enum('N','Y')	NO	N	密码是否过期 (N 未过期，y 已过期)
password_last_changed	timestamp	YES	记录密码最近修改的时间
password_lifetime	smallint(5) unsigned	YES	设置密码的有效时间，单位为天数
account_locked	enum('N','Y')	NO	N	用户是否被锁定（Y 锁定，N 未锁定）

1.4资源控制列

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

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

字段名	字段类型	是否为空	默认值	说明
max_questions	int(11) unsigned	NO	0	规定每小时允许执行查询的操作次数
max_updates	int(11) unsigned	NO	0	规定每小时允许执行更新的操作次数
max_connections	int(11) unsigned	NO	0	规定每小时允许执行的连接操作次数
max_user_connections	int(11) unsigned	NO	0	规定允许同时建立的连接次数

2.MySQL创建用户

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

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

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

2. 使用 CREATE USER 语句创建用户
3. 在 mysql.user 表中添加用户
4. 使用 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 权限类型

权限名称	对应user表中的字段	说明
SELECT	Select_priv	表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
INSERT	Insert_priv	表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
DELETE	Delete_priv	表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
UPDATE	Update_priv	表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
REFERENCES	References_priv	表示授予用户可以创建指向特定的数据库中的表外键的权限。
CREATE	Create_priv	表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
ALTER	Alter_priv	表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
SHOW VIEW	Show_view_priv	表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
CREATE ROUTINE	Create_routine_priv	表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
ALTER ROUTINE	Alter_routine_priv	表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
INDEX	Index_priv	表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
DROP	Drop_priv	表示授予用户可以删除特定数据库中所有表和视图的权限。
CREATE TEMPORARY TABLES	Create_tmp_table_priv	表示授予用户可以在特定数据库中创建临时表的权限。
CREATE VIEW	Create_view_priv	表示授予用户可以在特定数据库中创建新的视图的权限。
EXECUTE ROUTINE	Execute_priv	表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
LOCK TABLES	Lock_tables_priv	表示授予用户可以锁定特定数据库的已有数据表的权限。
ALL 或 ALL PRIVILEGES 或 SUPER	Super_priv	表示以上所有权限/超级权限

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即可。