您的MySQL安全吗？数据是最有价值的资产，数据安全已经成为重中之重。本篇将介绍如何使用SQL语句确保MySQL的安全性。

为什么是SQL而不是其他？使用SQL管理数据库相比较其他方法而言具有如下优势：

DBA不需要使用SSH等方法登录到MySQL所在的操作系统执行操作，可以省略操作系统的权限问题。
DBA的操作可以通过捕获SQL进行审计。
操作系统管理员无需触碰MySQL。
DevOps友好——面向服务
适合自动化修复
MySQL从8.0开始，将许多配置管理任务应用到SQL语句，接下来我们将逐一梳理。

首先，用户第一件事情是增强root账户的密码安全性。当用户安装MySQL时，根据所选的安装包进行初始密码的输入，Window的安装向导和DEB的安装过程中会提示输入root账户的初始密码，rpm的安装方式则会生成一个随机初始密码。使用随机密码连接MySQL后，首先要执行更改密码的语句，否则将报错。更改密码使用如下SQL：

ALTER USER root@localhost IDENTIFIED BY ”;
注意，MySQL中可能会存在多个root账户，修改的密码仅仅是“root@localhost”，考虑安全性问题，可以删除全局账户，后缀为“%”，如果需要远程连接，需要对主机名进行限制。

设置密码策略。相关组件是否已安装？可以通过以下SQL语句进行查看：

SELECT component_urn, ‘PASSWORD Policy Component Installed?’ as Note, if(count(component_urn) > 0, ‘YES’, ‘NO’) as Answer FROM mysql.component where component_urn=’file://component_validate_password’ group by component_urn;
查看密码策略的SQL语句：

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
where VARIABLE_NAME like ‘valid%password%’
OR VARIABLE_NAME=’default_password_lifetime’;
改变密码策略的SQL语句：

安装组件

INSTALL COMPONENT ‘file://component_validate_password’;

设置策略

set persist validate_password.check_user_name=’ON’;
set persist validate_password.dictionary_file='<FILENAME OF DICTIONARY FILE’;
set persist validate_password.length=15;
set persist validate_password.mixed_case_count=1;
set persist validate_password.special_char_count=2;
set persist validate_password.number_count=2;
set persist validate_password.policy=’STRONG’;
set persist password_history = 5;
set persist password_reuse_interval = 365;
Set global default_password_lifetime = 180;

确认MySQL的连接控制。连接控制可以处理与暴力攻击相关的失败登录尝试。

连接控制插件是否安装？

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE ‘connection%’;

安装插件

INSTALL PLUGIN CONNECTION_CONTROL SONAME ‘connection_control.so’;
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME ‘connection_control.so‘;

检查设置

select @@connection_control_failed_connections_threshold, @@connection_control_min_connection_delay,
@@connection_control_max_connection_delay,
@@connection_control_failed_connections_threshold;

设置变量

SET PERSIST connection_control_failed_connections_threshold = 4;
SET PERSIST connection_control_min_connection_delay = 1500;

使用CA，MySQL安装程序创建自签名密钥，建议用户从证书颁发机构CA下载密钥进行替换。

查看密钥

select ‘ALL SSL VARIABLES Listing’ as NOTE, @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher,
@@ssl_crl, @@ssl_crlpath, @@ssl_fips_mode,@@ssl_key;

SSL——访问MySQL时建议强制使用SSL连接。

查看是否开启SSL

SELECT VARIABLE_NAME, VARIABLE_VALUE, ‘ONLY ALLOW SSL ‘ as Note,
IF(VARIABLE_VALUE = ‘ON’, ‘PASS’, ‘FAIL’) AS CHECK_VAL
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (‘require_secure_transport’);

强制开启全局加密连接

set persist require_secure_transport=ON;

使用“SET PERSIST”。在MySQL 8.0中，DBA可以通过SQL设置系统变量。“SET PERSIST”的值写入 “mysqld-auto.cnf”并可以设置运行时的值，“SET PERSIST ONLY”仅写入至 “mysqld-auto.cnf” ，用于配置只读系统变量，这些变量只能在服务器启动时设置。

“mysqld-auto.cnf”文件位于datadir中，访问程度少于“my.cnf”，增加了安全性。文件中采用UNIX时间戳，便于追踪变化时间。

允许导入/导出操作，可以减少被攻击的范围。通过对变量“secure_file_priv”进行设置，以允许执行导入/导出操作。执行SQL语句查看是否允许执行相关操作：

SELECT VARIABLE_NAME, VARIABLE_VALUE, ‘Secure File Check’ as Note,
IF(length(VARIABLE_VALUE) > 0 and VARIABLE_VALUE!=’NULL’ , ‘FAIL’, ‘PASS’) as SecFileCheck
FROM performance_schema.global_variables
where variable_name = ‘secure_file_priv’;
如果变量值为路径名称，则允许在该路径下执行导入/导出操作。

set persist local_infile=OFF;
其次，用户认证安全需要确认，谁？什么样的用户？在哪里？如何认证等方面的内容。用户包括内部用户，内部使用X.509的用户，外部认证用户，及代理用户。

内部用户，通过下方SQL语句查看相关内容：

select host, user, plugin,
if(plugin = ‘mysql_native_password’, ‘WEAK SHA1’, ‘STRONG SHA2’) AS HASHTYPE
FROM mysql.user WHERE user not in (‘mysql.infoschema’, ‘mysql.session’)
and (plugin not like ‘auth%’ and plugin <> ‘mysql_no_login’) and length(authentication_string) > 0 order by plugin;
内部用户，要求使用X.509证书：

SELECT user.Host, user.User, user.ssl_type,
CAST(user.x509_issuer as CHAR) as Issuer,
CAST(user.x509_subject as CHAR) as Subject
FROM mysql.user where (user not like ‘mysql.%’) AND ssl_type=’X509′;
外部认证可以进行全局管理——映射到企业，使用更强大的选项LDAP、Windows AD SSPI、 Kerberos、FIDO2 等，通过下方SQL确认：

SELECT user.Host, user.User,user.plugin, user.authentication_string from mysql.user where plugin like ‘auth%’;
多因素认证。MySQL至多支持三因素认证，下方是一个二因素认证的示例：

CREATE USER ‘alice’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘sha2_password’ AND IDENTIFIED WITH authentication_ldap_sasl AS ‘uid=u1_ldap,ou=People,dc=example,dc=com’;
也可以稍后使用SQL语句添加第二个或第三个因素：

ALTER USER ‘alice’@’localhost’ ADD 3 FACTOR IDENTIFIED WITH authentication_fido;
角色和代理用户：

SELECT * FROM mysql.proxies_priv where grantor<>’boot@’;

检查指定用户、角色或使用角色的用户

SHOW GRANTS FOR ‘app_developer’@’%’;

用户使用角色

SHOW GRANTS FOR ‘u1’@’localhost’ USING ‘r1’;
用户权限——最大连接数。假设最大连接策略为210，则：

SELECT VARIABLE_NAME, VARIABLE_VALUE, ‘MUST be 210 or less’ as Note,
IF(VARIABLE_VALUE < 211, ‘PASS’, ‘FAIL’)
FROM performance_schema.global_variables WHERE VARIABLE_NAME LIKE ‘max_connections’;

如果返回错误，修改如下

SET PERSIST max_connections = 210;
用户权限报告：

WITH
tableprivs AS (SELECT user, host, ‘mysql.tables_priv’ as PRIV_SOURCE , DB as _db, Table_Name as _obj , ‘ ‘ as _col
FROM mysql.tables_priv where Table_name like ‘%’ ),
colprivs AS (SELECT User, Host, ‘mysql.columns_priv’ as PRIV_SOURCE , DB as _db, table_name as _obj , column_name as _col
FROM mysql.columns_priv WHERE Table_name like ‘%’ )
SELECT user,host, PRIV_SOURCE , _db as _db, _obj, _col FROM
( SELECT user,host, PRIV_SOURCE, _db, _obj, _col FROM colprivs UNION
SELECT user,host, PRIV_SOURCE, _db, _obj, _col FROM tableprivs) as tt group by user, host, PRIV_SOURCE, _db, _obj, _col;
哪些用户可以访问指定的表“actor”

use mysql;
WITH
globalprivs AS (SELECT user,host FROM mysql.user WHERE ‘Y’ IN
(Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv,
Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv,
Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv,
Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv,
Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv,
Create_routine_priv, Alter_routine_priv, Create_user_priv,
Event_priv, Trigger_priv, Create_tablespace_priv, Create_role_priv,
Drop_role_priv)
),
dbprivs AS (SELECT user,host FROM mysql.db WHERE ‘Y’ IN
(Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv,
Grant_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv,
Lock_tables_priv, Create_view_priv, Show_view_priv, Create_routine_priv,
Alter_routine_priv, Execute_priv, Event_priv, Trigger_priv)
),
tableprivs AS (SELECT user, host FROM tables_priv WHERE Table_name=’actor’ ),
colprivs AS (SELECT User, Host FROM mysql.columns_priv WHERE Table_name=’actor’ )
SELECT user,host FROM (SELECT user,host FROM globalprivs UNION
SELECT user,host FROM dbprivs UNION
SELECT user,host FROM colprivs UNION
SELECT user,host FROM tableprivs) as tt group by user, host;
用户对表具有Select权限：

WITH
globalprivs AS (SELECT user,host FROM mysql.user WHERE
Select_priv = ‘Y’
),
dbprivs AS (SELECT user,host FROM mysql.db WHERE
Select_priv = ‘Y’
),
colprivs AS (SELECT user, host FROM mysql.columns_priv WHERE Table_name=’actor’ AND FIND_IN_SET(‘Select’,Column_priv)),
tableprivs AS (SELECT User, Host FROM mysql.tables_priv WHERE Table_name=’actor’ AND FIND_IN_SET(‘Select’,Table_priv))
SELECT user,host FROM (SELECT user,host FROM globalprivs UNION
SELECT user,host FROM dbprivs UNION
SELECT user,host FROM colprivs UNION
SELECT user,host FROM tableprivs) as tt group by user, host;
哪些角色可以修改指定的表“actor”：

WITH
globalprivs AS (SELECT user,host FROM mysql.user WHERE ‘Y’ IN
(Insert_priv, Update_priv, Delete_priv, Drop_priv, Alter_priv)
),
dbprivs AS (SELECT user,host FROM mysql.db WHERE ‘Y’ IN
(Insert_priv, Update_priv, Delete_priv, Drop_priv, Alter_priv)
),
tableprivs AS (SELECT user, host FROM tables_priv WHERE table_name=’actor’),
colprivs AS (SELECT User, Host FROM mysql.columns_priv WHERE table_name=’actor’)
SELECT from_user,from_host FROM (SELECT user,host FROM globalprivs UNION
SELECT user,host FROM dbprivs UNION
SELECT user,host FROM colprivs UNION
SELECT user,host FROM tableprivs) as tt
RIGHT JOIN
mysql.role_edges as tr
ON tr.to_user=tt.user AND tr.to_host= tt.host GROUP BY from_user, from_host;

用户具有的全局/管理权限：

SELECT user,host, ‘Global Priv’, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv,
Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv,
Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv,
Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv,
Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv,
Create_routine_priv, Alter_routine_priv, Create_user_priv,
Event_priv, Trigger_priv, Create_tablespace_priv, Create_role_priv,
Drop_role_priv FROM mysql.user
WHERE ( ‘Y’ IN
(Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv,
Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv,
Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv,
Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv,
Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv,
Create_routine_priv, Alter_routine_priv, Create_user_priv,
Event_priv, Trigger_priv, Create_tablespace_priv, Create_role_priv,
Drop_role_priv)) and (user.user not like ‘mysql.%’);
查看MySQL的插件：

SELECT PLUGINS.PLUGIN_NAME,PLUGINS.PLUGIN_VERSION,
PLUGINS.PLUGIN_STATUS,PLUGINS.PLUGIN_TYPE,
PLUGINS.PLUGIN_TYPE_VERSION,PLUGINS.PLUGIN_LIBRARY,
PLUGINS.PLUGIN_LIBRARY_VERSION,PLUGINS.PLUGIN_DESCRIPTION,
PLUGINS.PLUGIN_LICENSE,PLUGINS.LOAD_OPTION
FROM information_schema.PLUGINS where plugin_library is Not null;
查看MySQL使用的端口：

SELECT VARIABLE_NAME, VARIABLE_VALUE, ‘If the defined port is deemed prohibited, this is a FAIL.’ as Note
FROM performance_schema.global_variables
WHERE VARIABLE_NAME in (‘port’, ‘mysqlx_port’, ‘admin_port’);
检查文件的存储位置：

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE (VARIABLE_NAME LIKE ‘%dir’ or VARIABLE_NAME LIKE ‘%file’)
and (VARIABLE_NAME NOT LIKE ‘%core%’ AND VARIABLE_NAME <> ‘local_infile’
AND VARIABLE_NAME <> ‘relay_log_info_file’) order by VARIABLE_NAME;
检查密钥是否安全：

SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION, LOAD_OPTION
FROM information_schema.PLUGINS where PLUGIN_NAME LIKE ‘keyring_file’ and plugin_status=’ACTIVE’;
检查表空间文件是否静态加密：

SELECT INNODB_TABLESPACES.NAME,INNODB_TABLESPACES.ENCRYPTION,
IF(ENCRYPTION = ‘Y’, ‘PASS’, ‘FAIL’) as CHECK_VAL
FROM information_schema.INNODB_TABLESPACES where ENCRYPTION=’N’;
要求使用TDE：

SELECT VARIABLE_NAME, VARIABLE_VALUE, ‘table_encryption_privilege_check – TABLE REQUIRE AT REST ENCRYPTION’ as Note,
IF(VARIABLE_VALUE = ‘ON’, ‘PASS’, ‘FAIL’) as CHECK_VAL
FROM performance_schema.global_variables where variable_name = ‘table_encryption_privilege_check’;
InnoDB REDO、UNDO、Binlog，及审计日志是否加密：

SELECT VARIABLE_NAME, VARIABLE_VALUE, ‘innodb_redo_log AT REST ENCRYPTION’ as Note,
IF(VARIABLE_VALUE = ‘ON’, ‘PASS’, ‘FAIL’) as CHECK_VAL
FROM performance_schema.global_variables where variable_name = ‘innodb_redo_log_encrypt’;

SELECT VARIABLE_NAME, VARIABLE_VALUE, ‘innodb_undo_log AT REST ENCRYPTION’ as Note,
IF(VARIABLE_VALUE = ‘ON’, ‘PASS’, ‘FAIL’) as CHECK_VAL
FROM performance_schema.global_variables where variable_name = ‘innodb_undo_log_encrypt’;

SELECT VARIABLE_NAME, VARIABLE_VALUE, ‘BINLOG – AT REST ENCRYPTION’ as Note,
IF(VARIABLE_VALUE = ‘ON’, ‘PASS’, ‘FAIL’) as CHECK_VAL
FROM performance_schema.global_variables where variable_name = ‘binlog_encryption’;

SELECT VARIABLE_NAME, VARIABLE_VALUE, ‘AUDIT LOG – AT REST ENCRYPTION’ as Note,
IF(VARIABLE_VALUE = ‘AES’, ‘PASS’, ‘FAIL’)
FROM performance_schema.global_variables where variable_name = ‘audit_log_encryption’;
以上内容是关于MySQL安全性方面的相关SQL语句，欢迎关注、收藏、转发！

感谢您关注“MySQL解决方案工程师”！

声明：文中观点不代表本站立场。本文传送门：https://eyangzhen.com/418967.html

MySQL中涉及安全性的SQL语