徐轶韬 2024-08-20 00:00 广东
您的MySQL真的安全吗？
您的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 '<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='file://component_validate_password' group by component_urn;查看密码策略的SQL语句：
SELECT VARIABLE_NAME, VARIABLE_VALUEFROM performance_schema.global_variableswhere 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连接。
#查看是否开启SSLSELECT VARIABLE_NAME, VARIABLE_VALUE, 'ONLY ALLOW SSL ' as Note,IF(VARIABLE_VALUE = 'ON', 'PASS', 'FAIL') AS CHECK_VALFROM performance_schema.global_variablesWHERE 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_variableswhere 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 HASHTYPEFROM 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 SubjectFROM `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 _colFROM 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 _colFROM 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 UNIONSELECT user,host FROM tableprivs) as tt group by user, host;
用户对表具有Select权限：
WITHglobalprivs AS (SELECT user,host FROM mysql.user WHERESelect_priv = 'Y'),dbprivs AS (SELECT user,host FROM mysql.db WHERESelect_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 UNIONSELECT user,host FROM tableprivs) as tt group by user, host;
哪些角色可以修改指定的表“actor”：
WITHglobalprivs 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 UNIONSELECT 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_VALUEFROM 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_VALFROM `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_VALFROM 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
