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

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

DBA不需要使用SSH等方法登录到MySQL所在的操作系统执行操作，可以省略操作系统的权限问题DBA的操作可以通过捕获SQL进行审计操作系统管理员无需触碰MySQLDevOps友好——面向服务适合自动化修复

MySQL从8.0开始，将许多配置管理任务应用到SQL语句，接下来我们将逐一梳理。

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

ALTER USER root@localhost IDENTIFIED BY '<auth_string>';

注意，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='; 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 ';;#设置策略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连接。

#查看是否开启SSLSELECT 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 SecFileCheckFROM performance_schema.global_variables where variable_name = 'secure_file_priv';

如果变量值为路径名称，则允许在该路径下执行导入/导出操作。

+------------------+------------------------------------------------+-------------------+--------------+| VARIABLE_NAME    | VARIABLE_VALUE                                 | Note              | SecFileCheck |+------------------+------------------------------------------------+-------------------+--------------+| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.4\Uploads\ | Secure File Check | FAIL         |+------------------+------------------------------------------------+-------------------+--------------+

检查 “local_infile”。

select if(@@local_infile, 'ON', 'OFF') as LOCAL_LOAD_DATA_ALLOWED;

MySQL 8.0 默认设置为OFF。也可以通过下方语句设置：

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;

用户权限报告：

WITHtableprivs 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 UNIONSELECT user,host, PRIV_SOURCE, _db, _obj, _col FROM tableprivs) as tt group by user, host, PRIV_SOURCE, _db, _obj, _col;

哪些用户可以访问指定的表“actor”

use mysql;WITHglobalprivs 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 UNIONSELECT user,host FROM dbprivs UNIONSELECT 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 UNIONSELECT user,host FROM dbprivs UNIONSELECT 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 UNIONSELECT user,host FROM dbprivs UNIONSELECT user,host FROM colprivs UNION SELECT user,host FROM tableprivs) as ttRIGHT JOINmysql.role_edges as trON 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.userWHERE ( '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 NoteFROM performance_schema.global_variablesWHERE VARIABLE_NAME in ('port', 'mysqlx_port', 'admin_port');

检查文件的存储位置：

SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variablesWHERE (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_VALFROM 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_VALFROM 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_VALFROM 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解决方案首席工程师。为中国金融、政府、航空运输等行业的MySQL用户提供相关产品的售前咨询，企业级产品介绍服务以及推广和普及MySQL数据库在社区的使用。公众号“MySQL解决方案工程师”运营者和内容作者。

来源丨公众号：MySQL解决方案工程师（ID：mysqlse）

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn