Mysql--权限与安全管理

前言：本博客仅作记录学习使用，部分图片出自网络，如有侵犯您的权益，请联系删除

一、权限表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL数据库中，由MySQL_install_db脚本初始化。存储账户权限信息的表主要有user、db、host、tables_priv、columns_priv和procs_priv

1、user表

user表示MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息，里面的权限是全局性的。MySQL 8.0 中user表有42个字段（自行查找），可分为四类：用户列、权限列、安全列和资源控制列

1.1、用户列

user表的用户列包括Host、User、authentication_sting，分别表示主机名、用户名和密码，其中 User 和 Host 为 User 表的联合主键。当用户与服务器之间建立连接时，输入的账户信息中的用户名称、主机名和密码必须匹配 User 表中对应的字段，只有3个值都匹配的时候，才允许连接的建立。这3个字段的值就是创建账户时保存的账户信息。修改用户密码时，实际就是修改user 表的 authentication string字段的值。

1.2、权限列

权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作；普通权限用于操作数据库；高级权限用于数据库管理。如果要修改权限，可以使用GRANT语句或UPDATE语句更改user 表的这些字段来修改用户对应的权限。

1.3、安全列

安全列只有6个字段，其中两个是ssl相关的，两个是x509相关的，另外两个是授权插件相关的。ssl用于加密；x509标准可用于标识用户；Plugin字段标识可以用于验证用户身份的插件，如果该字段为空，服务器使用内建授权验证机制验证用户身份。可以通过SHOW VARIABLES LIKE 'have_openssl'语句来查询服务器是否支持ssl功能。

1.4、资源控制列

资源控制列的字段用来限制用户使用的资源，包含4个字段，分别为:

• max_questions--用户每小时允许执行的查询操作次数。
• max_updates--用户每小时允许执行的更新操作次数
• max_connections--用户每小时允许执行的连接操作次数
• max_user connections--用户允许同时建立的连接次数

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

2、db表

db表是 MySQL数据中非常重要的权限表。db 表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库；

2.1、用户列

db表用户列有3个字段，分别是Host、User、Db，标识从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。host表不存储用户名称，用户列只有2个字段，分别是Host和 Db，表示从某个主机连接的用户对某个数据库的操作权限，其主键包括 Host和 Db两个字段。host 很少用到，一般情况下 db 表就可以满足权限控制需求了

2.1、权限列

在MySQL数据库管理中，CREATE ROUTINE 和 ALTER ROUTINE 权限分别允许用户创建和修改存储过程。如果希望限制用户仅对特定数据库具有这些操作权限，可以在用户表（user表）中将这些权限设置为"N"，然后在数据库表（db表）中指定相应的数据库权限。例如，用户Zhangting需要从两个不同的主机（large.domain.com和small.domain.com）连接并操作books数据库，可以将其添加到db表，设置host字段为空，然后在host表中为这两个主机分别添加记录，并将数据库字段设置为books。这样，当用户尝试连接时，如果db表中没有匹配的主机，MySQL将查询host表来确定权限。**

3、tables_priv表和columns_priv表

tables_priv 表用来对表设置操作权限，columns_priv表用来对表的某一列设置权限

4、procs_priv表

procs_priv表可以对存储过程和存储函数设置操作权限

二、账户管理

MySOL提供了许多语句来管理用户账号，包括登录和退出MySOL服务器、创建用户、删除用户、密码管理和权限管理等内容。MySOL数据库的安全性需要通过账户管理来保证

1、登录和退出MySQL服务器

MySQL命令的常用参数如下

# 使用root用户登录到本地MySQL服务器的mysql库中
mysql -h localhost -u root -p mysql
 ​
# 使用root用户登录到本地MySQL服务器的test_db数据库中，同时执行一条查询语句
mysql -h localhost -u root -p test_db -e "DESC person";

2、新建普通用户

创建新用户，必须有相应的权限来执行创建操作。在MySOL数据库中，有两种方式创建新用户：一种是使用CREAT EUSER语句；另一种是直接操作MySQL授权表

2.1、使用create user语句创建新用户

执行create user或grant语句时，服务器会修改相应的用户授权表，添加或修改用户及其权限；其语法：

 create user user_specification
     [,user_specification] ...
 ​
 user_specification:
     user@host
     [
         IDENTIFIED BY [PASSWORD] 'password'
         | IDENTIFIED WITH auth_plugin [AS 'auth_string']
     ]

CREATE USER语句会添加一个新的MySOL账户

 # 使用create user创建一个用户，用户名是jeffrey，密码是mypass，主机名是localhost
 create user 'jeffrey'@'localhost' identified by 'mypass';   # 当然不指定则表示无需密码登录

2.2、直接操作MySQL用户表

使用INSERT语句创建新的用户，必须拥有对MySQL.user表的insert权限；其基本语法：

 insert into MySQL.user(Host,User,authentication_string)
 values('host','username',MD5('password'));

Host、User、authentication string分别为user 表中的主机、用户名称和密码字段:MD5()函数为密码加密函数。

 # 使用insert创建一个用户，其名称为customer1，主机名为localhost，密码为aa123456
 mysql> insert into user (Host,User,authentication_string)
     -> values('localhost','customer1',md5('aa123456'));
 ERROR 1364 (HY000): Field 'ss1_cipher' doesn't have a default value
 ​
 # 失败，因为ssl_cipher、x509_issuer和x509_subject3个字段在user表中定义没有设置默认值

3、删除普通用户

3.1、使用drop user语句删除用户

 drop user user [,user]

DROP USER语句用于删除一个或多个MySOL账户。要使用DROPUSER，必须拥有MySQL数据库的全局CREATE USER权限或DELETE权限，

 # 删除user在本地登录权限
 drop user 'user'@'localhost';   
 drop user;      # 删除来自所有授权表的账户权限记录

3.2、使用delete语句删除用户

 delete from Mysql.user where host='hostname' and user='username'
 # 示例：使用delete删除用户'customer1'@'localhost'
 delete from MySQL.user where host='localhost' and user='customer1';

4、root用户修改自己的密码

使用update语句修改root用户密码的语句如下：

 update mysql.user set authentication_string=MD5("123456") where User="root" and Host="localhost";
 flush privileges;
 
 
 # 使用update语句将root用户的密码修改为"123456"
 mysql> update mysql.user set authentication_string= MD5("123456") where User="root" and Host="localhost";
 mysql> flush privileges;

5、root用户修改普通用户的密码

root可通过set语句修改MySQL.user表、通过UPDATE语句修改用户的密码；

创建用户user，命令如下：

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

5.1、使用set语句修改普通用户的密码

 set password for 'user'@'localhost' = 'aa123';

5.2、使用update语句修改普通用户的密码

 # 语法格式如下：
 update mysql.user set authentication_string=MD5("123456") where user="username" and Host="hostname";
 flush privileges;
 
 # 使用update语句将user用户的密码修改为"sns123"
 mysql> update mysql.user set authentication_string=MD5("sns123") where user="user" and host="localhost";

三、权限管理

1、MySQL的各种权限

账户权限信息被存储在 MySQL数据库的 user、db、host、tables priv、columns_priv和 procs_priv表中。在MySOL启动时，服务器将这些数据库表中权限信息的内容读入内存；具体权限自查；

2、授权

授权就是为某个用户授予权限。合理的授权可以保证数据库的安全。MySQL中可以使用GRANT 语句为用户授予权限。授于的权限可分为多个层级：

2.1、全局层级

全局权限适用于一个给定服务器中的所有数据库。这些权限存储在MySQL.user表中。GRANTALL ON .和 REVOKE ALL ON *. * 只授予和撤销全局权限。

2.2、数据库层级

数据库权限适用于一个给定数据库中的所有目标。这些权限存储在MySOL.db和MySOL.host表中。GRANT ALLON db name.和 REVOKE ALLON db name.*只授予和撤销数据库权限。

2.3、表层级

表权限适用于一个给定表中的所有列。这些权限存储在MySQL.talbespriv表中。GRANTALLON db name.tbl name和 REVOKE ALL ON db name.tbl name 只授予和撤销表权限

2.4、列层级

列权限适用于一个给定表中的单一列。这些权限存储在MySQL.columns_priv表中。当使用REVOKE时，必须指定与被授权列相同的列。

2.5、子程序层级

CREATE ROUTINE、ALTER ROUTINE、EXECUTE和 GRANT 权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATEROUTINE外，这些权限可以被授予子程序层级，并存储在MySQL.procspriv表中。

拥有grant权限的用户才可执行grant语句；grant的语法如下：

 grant priv_type [(columns)] [,priv_type [(columns)]] ...
 on [object_type] table1,table2,...,tablen
 to user [with grant option]
 object_type = table | function | procedure

示例：使用GRANT语句创建一个新的用户grantUser，密码为“grantpwd”。用户grantUser 对所有的数据有査询、插入权限, 并授于GRANT权限

mysql> grant select,insert on *.* to 'grantUser'@'localhost'
identified by 'grantpwd'
with grant option;
 
# 使用select语句查询用户的权限
mysql> select Host,User,Select_priv,Insert_priv,Grant_priv from mysql.user where user='grantUser';
+-----------+-----------+-------------+-------------+------------+
| Host      | User      | Select_priv | Insert_priv | Grant_priv |
+-----------+-----------+-------------+-------------+------------+
| localhost | grantUser | Y           | Y           | Y          |
+-----------+-----------+-------------+-------------+------------+

3、收回权限

在将用户账户从user表删除之前，应收回相应用户的所有权限。REVOKE语句有两种语法格式：

第一种语法是收回所有用户的所有权限，用于取消对于已命名的用户的所有全局层级、数据库层级、表层级和列层级的权限，具体如下：

revoke all privileges,grant,option
from 'user'@'host' [,'user'@'host' ...]

REVOKE语句必须和FROM语句一起使用。FROM语句指明需要收回权限的账户。

另一种为长格式的revoke语句，基本语法如下：

revoke priv_type [(columns)] [,priv_type[(columns)]] ...
on table1,table2,...,tablen
from 'user'@'host'[,'user'@'host'...]

要使用REVOKE语句，必须拥有MySQL数据库的全局CREATEUSER权限或UPDATE权限；

# 使用revoke语句取消用户user的更新权限
mysql> revoke update on *.* from 'user'@'localhost';
 
# 使用select语句查询用户的权限
mysql> select Host,User,Select_priv,Insert_priv,Grant_priv from mysql.user where user='grantUser';
+-----------+-----------+-------------+-------------+------------+
| Host      | User      | Select_priv | Update_priv | Grant_priv |
+-----------+-----------+-------------+-------------+------------+
| localhost | user      | Y           | N           | Y          |
+-----------+-----------+-------------+-------------+------------+

4、查看权限

show grants语句可以显示指定用户的权限信息，使用show grant查看账户信息的基本语法：

show grants for 'user'@'host';
 
 
# 使用show grants语句查询用户user的权限信息
mysql> show grants for 'user'@'localhost';
+--------------------------------------------------------------------+
| Grants for user@localhost                                          |
+--------------------------------------------------------------------+
| GRANT SELECT,INSERT ON *.* TO 'user'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------------+

在前面创建用户时,查看新建的账户时使用SELECT语句,也可以通过 SELECT语句查看 user表中的各个权限字段以确定用户的权限信息，其基本语法格式如下：

select privileges_list from user where user='username',host='hostname';

四、访问控制

正常情况下，并不希望每个用户都可以执行所有的数据库操作。当MySQL允许一个用户执行各种操作时，它将首先核实该用户向MySQL服务器发送的连接请求，然后确认用户的操作请求是否被允许。MySOL的访问控制分为两个阶段：连接核实阶段和请求核实阶段

1、连接核实阶段

当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码身份验证来接受或拒绝连接，如果连接核实没有通过，服务器完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待用户请求

2、请求核实阶段

建立了连接之后，服务器进入访问控制的阶段2。对在此连接上的每个请求，服务器检查用户要执行的操作，然后检查是否有足够的权限来执行它

五、提升安全性

1、AES 256加密

这里通过AES_ENCODE()和解密函数AES_DECODE()来提高安全强度

1.1、AES_ENCODE()

aes_encode(str,pawd_str)
# 参数pawd_str是密钥
 
# 下面通将字符串' Adversity does teach who your real friends are'加密，密钥为'key10001'，加密后的串存在@ss中。输入语句如下:
SET@ss=AES_ENCRYPT('Adversity does teach whoe your real friends are','key10001');
select @ss;
+--------------------------------------------------+
| @ss                                              |
+--------------------------------------------------+
| HGõ2oe�Ҧ�{o6n�4�>�-��yǁ�WJ��ݼ@)B�t>F�      |
+--------------------------------------------------+
select char_length(@ss);
+------------------+
| char_length(@ss) |
+------------------+
|               48 |
+------------------+

1.2、AES_DECODE()

# 将@ss中的字符串解密
select aes_decrypt(@ss,'key10001');
+-------------------------------------------------+
| aes_decrypt(@ss,'key10001')                     |
+-------------------------------------------------+
| Adversity does teach whoe your real friends are |
+-------------------------------------------------+

1.3、将加密字符串存入数据表中

创建数据表mm，包含3个字段，属性分别为varbinary、binary、blob

create table mm (s1 varbinary(16),s2 binary(16),s3 blob);
 
# 将'雨里一两家'、'two things'、'闲看栀子花'加密，密钥为key，存入数据表mm中
insert into mm values(aes_encrypt('雨里一两家','key'),aes_encrypt('two things','key'),aes_encrypt('闲看栀子花','key'));
 
# 进行解密
mysql> select aes_decrypt(s1,'key'),aes_decrypt(s2,'key'),aes_decrypt(s3,'key') from mm;
+-----------------------+-----------------------+-----------------------+
| aes_decrypt(s1,'key') | aes_decrypt(s2,'key') | aes_decrypt(s3,'key') |
+-----------------------+-----------------------+-----------------------+
| 雨里一两家            | two things            | 闲看栀子花            |
+-----------------------+-----------------------+-----------------------+

2、密码到期更换策略

MVSOL 8.0允许数据库管理员手动设置账户密码过期时间。任何密码超期的账户想要连接服务端时都必须更改密码。通过设置 default password lifetime 参数可以设置账户过期时间。

首先查看系统中的账户过期时间：

mysql> select user,host,password_last_changed,password_lifetime,password_expired from mysql.user \G
*************************** 1. row ***************************
                 user: root
                 host: localhost
password_last_changed: 2024-07-22 19:13:09
    password_lifetime: NULL
     password_expired: N
*************************** 2. row ***************************
                 user: mysql.session
                 host: localhost
password_last_changed: 2024-07-22 19:13:09
    password_lifetime: NULL
     password_expired: N
*************************** 3. row ***************************
                 user: mysql.sys
                 host: localhost
password_last_changed: 2024-07-22 19:13:09
    password_lifetime: NULL
     password_expired: N
3 rows in set (0.00 sec)

可以看出，password_lifetime：NULL表示密码永不过期

# 设置root用户的密码过期时间为260天
mysql> alter user root@localhost password expire interval 260 day;
 
mysql> select user,host,password_last_changed,password_lifetime,password_expired from mysql.user \G
*************************** 1. row ***************************
                 user: root
                 host: localhost
password_last_changed: 2024-07-22 19:13:09
    password_lifetime: 260			# 可看到此处更改为260
     password_expired: N
...
 
# 将root用户的密码过期时间重新设置为永不过期
mysql> alter user root@localhost password expire default;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select user,host,password_last_changed,password_lifetime,password_expired from mysql.user \G
*************************** 1. row ***************************
                 user: root
                 host: localhost
password_last_changed: 2024-07-22 19:13:09
    password_lifetime: NULL
     password_expired: N
...

3、安全模式安装

MySQL新增了"安全模式"的安装形式，从而避免用户的数据被泄露。具体方式：

• 为root账户设置密码
• 移除能从本地主机以外的地址访问数据库的root账户
• 移除匿名账户
• 移除test数据库，该数据库默认可被任意用户甚至匿名账户访问

使用 mysqld -initialize 命令来安装MySQL实例默认是安全的，主要原因如下：

• 在安装过程只创建一个root账户'root'@"ocalhost，自动为这个账户生成一个随机密码并标记密码过期
• 数据库管理员必须用root账户及该随机密码登录并设置一个新密码后才能对数据库进行正常操作
• 安装过程不创建任何匿名账户
• 安装过程不创建 test 数据库

六、MySQL 8.0的新特性

1、管理角色

在MySOL8.0数据库中，角色可以看成是一些权限的集合，为用户赋予统一的角色，权限的修改直接通过角色来进行，无须为每个用户单独授权。

# 创建角色
create role role_tt;
 
# 给角色授于权限
grant select on db.* to 'role_tt';
 
# 创建用户myuser1
create user 'myuser1'@'%' identified by '123456';
 
# 为用户myuser1赋予角色role_it
grant 'role_tt' to 'myuser1'@'%';
 
# 给角色role_tt增加insert权限
grant insert on fdb.* to 'role_tt';
 
# 给角色role_tt删除insert权限
revoke insert ob db.* from 'role_it';
 
# 查看默认角色信息
select * from mysql.default_roles;
 
# 查看角色与用户关系
select * from mysql.role_edges;
 
# 删除角色
drop role role_tt;

致谢

在此，我要对所有为知识共享做出贡献的个人和机构表示最深切的感谢。同时也感谢每一位花时间阅读这篇文章的读者，如果文章中有任何错误，欢迎留言指正。 

学习永无止境，让我们共同进步！！