MySQL权限管理

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

MySQL权限表

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

1.用户列

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

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

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

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	是否可以创建表空间

如果要修改权限，可以使用 GRANT 语句为用户赋予一些权限，也可以通过 UPDATE 语句更新 user 表的方式来设置权限。

3.安全列

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

字段名	字段类型	是否为空	默认值	说明
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 未锁定）

注意：即使 password_expired 为“Y”，用户也可以使用密码登录 MySQL，但是不允许做任何操作。

通常标准的发行版不支持 ssl，读者可以使用 SHOW VARIABLES LIKE "have_openssl" 语句来查看是否具有 ssl 功能。如果 have_openssl 的值为 DISABLED，那么则不支持 ssl 加密功能。

4.资源列

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

字段名	字段类型	是否为空	默认值	说明
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	规定允许同时建立的连接次数

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

MySQL创建用户

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

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

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

复制
1. 使用 CREATE USER 语句创建用户
2. 在 mysql.user 表中添加用户
3. 使用 GRANT 语句创建用户
 

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

复制
-- 语法格式：
	CREATE USER <用户> IDENTIFIED BY [ PASSWORD ] 'password'  [,<用户> IDENTIFIED BY [ PASSWORD ] 'password']
 
-- 用户
指定创建用户账号，格式为 user_name'@'host_name。这里的user_name是用户名，host_name为主机名，即用户连接 MySQL 时所用主机的名字。如果在创建的过程中，只给出了用户名，而没指定主机名，那么主机名默认为“%”，表示一组主机，即对所有主机开放权限。
 
-- IDENTIFIED BY子句
用于指定用户密码。新用户可以没有初始密码，若该用户不设密码，可省略此子句。
 
-- PASSWORD 'password'
PASSWORD 表示使用哈希值设置密码，该参数可选。如果密码是一个普通的字符串，则不需要使用 PASSWORD 关键字。
 
-- 案例：
mysql> create user zzc@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select password('123');
+-------------------------------------------+
| password('123')                           |
+-------------------------------------------+
| *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
 
-- "*23AE809DDACAF96AF0FD78ED04B6A265E05AA257"就是 zzc 的哈希值。下面创建用户 test，SQL 语句和执行过程如下：
mysql> CREATE USER 'test'@'localhost'IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257';
Query OK, 0 rows affected (0.04 sec)
 
mysql> -- 删除用户
mysql> drop user zzc@'%';
Query OK, 0 rows affected (0.00 sec)
 
#创建多个用户，用逗号隔开
mysql> create user zzc@'%' identified by '123',test@'172.16.1.52' identified by '123'; 
Query OK, 0 rows affected (0.00 sec)
 
mysql> select user,host from mysql.user;
+-----------+-------------+
| user      | host        |
+-----------+-------------+
| root      | %           |
| zzc       | %           |
| test      | 172.16.1.52 |
| mysql.sys | localhost   |
| root      | localhost   |
+-----------+-------------+
5 rows in set (0.00 sec)
 

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 ('172.16.1.52','zzc',password('123'),'','','');
Query OK, 1 row affected, 1 warning (0.00 sec)
 
#使用zzc登录MySQL报错
[root@db02 ~]# mysql -uzzc -p123 -h172.16.1.51
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'zzc'@'172.16.1.52' (using password: YES)
 
-- 无法登录，使用INSERT创建的用户需要使用FLUSH PRIVILEGES;刷新权限。
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
[root@db02 ~]# mysql -uzzc -p123 -h172.16.1.51
Welcome to the MySQL monitor.  Commands end with ; or \g.
---------- ------------ ------------- -------------------
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
 

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 参数表示新用户的密码	
 
-- 案例：
mysql> grant select on *.* to test1@'172.16.1.52' identified by 'test1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> grant all privileges on *.* to test2@'172.16.1.52' identified by 'test2';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
-- ALL PRIVILEGES : 所有的权限
 

MySQL修改用户

复制
 
-- 在 MySQL 中，我们可以使用 RENAME USER 语句修改一个或多个已经存在的用户账号。
 
-- 语法格式如下：
	RENAME USER <旧用户> TO <新用户>
	
-- 其中：
● <旧用户>：系统中已经存在的 MySQL 用户账号。
● <新用户>：新的 MySQL 用户账号。	
 
mysql> select user,host from mysql.user;
+-----------+-------------+
| user      | host        |
+-----------+-------------+
| root      | %           |
| test      | 172.16.1.52 |
| mysql.sys | localhost   |
| root      | localhost   |
+-----------+-------------+
4 rows in set (0.00 sec)
 
mysql> rename user test@'172.16.1.52' to zzc@'172.16.1.52';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select user,host from mysql.user;
+-----------+-------------+
| user      | host        |
+-----------+-------------+
| root      | %           |
| zzc       | 172.16.1.52 |
| mysql.sys | localhost   |
| root      | localhost   |
+-----------+-------------+
4 rows in set (0.00 sec)
 
[root@db02 ~]# mysql -uzzc -p123 -h172.16.1.51
Welcome to the MySQL monitor.  Commands end with ; or \g.
---------- ------------ ------------- -------------------
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
 

MySQL删除用户

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

1.使用 DROP USER 删除普通用户

复制
-- 语法格式：
	DROP USER <用户1> [ , <用户2> ]…
 
-- 示例：
mysql> drop user zzc@'172.16.1.52';
Query OK, 0 rows affected (0.00 sec)
 

2.使用 DELETE 语句删除普通用户

复制
-- 可以使用 DELETE 语句直接删除 mysql.user 表中相应的用户信息，但必须拥有 mysql.user 表的 DELETE 权限。
 
-- 格式：
	DELETE FROM mysql.user WHERE Host='hostname' AND User='username';
 
-- Host 和 User 这两个字段都是 mysql.user 表的主键。因此，需要两个字段的值才能确定一条记录。
 
-- 示例：
mysql> select user,host from mysql.user;
+-----------+-------------+
| user      | host        |
+-----------+-------------+
| root      | %           |
| test01    | %           |
| pm        | 172.16.1.52 |
| test      | 172.16.1.52 |
| zzc       | 172.16.1.52 |
| mysql.sys | localhost   |
| root      | localhost   |
+-----------+-------------+
7 rows in set (0.00 sec)
 
mysql> delete from mysql.user where user='pm' and host='172.16.1.52';
Query OK, 1 row affected (0.00 sec)
 
mysql> select user,host from mysql.user;
+-----------+-------------+
| user      | host        |
+-----------+-------------+
| root      | %           |
| test01    | %           |
| test      | 172.16.1.52 |
| zzc       | 172.16.1.52 |
| mysql.sys | localhost   |
| root      | localhost   |
+-----------+-------------+
6 rows in set (0.00 sec)
 

MySQL查看权限

复制
-- 在 MySQL 中，可以通过查看 mysql.user 表中的数据记录来查看相应的用户权限，也可以使用 SHOW GRANTS 语句查询用户的权限。
 
-- 格式：
	SHOW GRANTS FOR 'username'@'hostname';
 
-- 示例：
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)
 

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 指定表名或视图名。
 

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	表示以上所有权限/超级权限

2.用户授权

复制
-- 使用 GRANT 语句创建一个新的用户 test7，密码为 test7。用户 test7 对所有的数据有查询、插入权限，并授予 GRANT 权限。SQL 语句和执行过程如下。
mysql>  GRANT SELECT,INSERT ON *.* TO 'test7'@'localhost' IDENTIFIED BY 'test7' WITH GRANT OPTION;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
 
mysql> SHOW GRANTS FOR 'test7'@'localhost';
+----------------------------------------------------------------------+
| Grants for test7@localhost                                           |
+----------------------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'test7'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
 
-- 注：常用的权限
SELECT, SHOW VIEW :数据库可读权限。
 

3.删除用户权限

复制
-- 在 MySQL 中，可以使用 REVOKE 语句删除某个用户的某些权限（此用户不会被删除），在一定程度上可以保证系统的安全性。例如，如果数据库管理员觉得某个用户不应该拥有 DELETE 权限，那么就可以删除 DELETE 权限。
 
1.删除用户某些特定权限
 
-- 格式：
	REVOKE priv_type [(column_list)]...
	ON database.table
	FROM user [, user]...
	
-- REVOKE 语句中的参数与 GRANT 语句的参数意思相同。其中：
● priv_type 参数表示权限的类型
● column_list 参数表示权限作用于哪些列上，没有该参数时作用于整个表上
● user 参数由用户名和主机名构成，格式为'username'@'hostname'
 
-- 示例：
mysql> grant all privileges on *.* to zzc@'172.16.1.52' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> revoke select on *.* from zzc@'172.16.1.52';
Query OK, 0 rows affected (0.00 sec)
 
[root@db02 ~]# mysql -uzzc -p123 -h172.16.1.51
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
----- ----------- ------------  --------------- --------------
mysql> 
 
-- 可以看到select查询权限没了
mysql> select * from linux13;
ERROR 1142 (42000): SELECT command denied to user 'zzc'@'172.16.1.52' for table 'linux13'
 
 
2.删除特定用户的所有权限
-- 格式：
	REVOKE ALL PRIVILEGES, GRANT OPTION ON database.table FROM user [, user] ...
	
-- 删除用户权限需要注意以下几点：
● REVOKE 语法和 GRANT 语句的语法格式相似，但具有相反的效果。
● 要使用 REVOKE 语句，必须拥有 MySQL 数据库的全局 CREATE USER 权限或 UPDATE 权限。
 
-- 示例：
mysql> revoke all privileges on *.* from zzc@'172.16.1.52';
Query OK, 0 rows affected (0.01 sec)
 
mysql> show grants for zzc@'172.16.1.52';
+-------------------------------------------+
| Grants for zzc@172.16.1.52                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'zzc'@'172.16.1.52' |
+-------------------------------------------+
1 row in set (0.00 sec)