以下文章来源于MySQL解决方案工程师 ,作者徐轶韬
徐老师写的这篇文章《MySQL中涉及安全性的SQL语句》给我们讲解了MySQL数据库中涉及到安全性的SQL语句,其中很多都是我们日常工作中用到的,我们可以进行归类积累,应用到合适的场景中。
数据是最有价值的资产,数据安全已经成为重中之重。本篇将介绍如何使用SQL语句确保MySQL的安全性。
为什么是SQL而不是其他?使用SQL管理数据库相比较其他方法而言具有如下优势,
- DBA不需要使用SSH等方法登录到MySQL所在的操作系统执行操作,可以省略操作系统的权限问题。
- DBA的操作可以通过捕获SQL进行审计。
- 操作系统管理员无需触碰MySQL。
- DevOps友好——面向服务
- 适合自动化修复
MySQL从8.0开始,将许多配置管理任务应用到SQL语句,接下来我们将逐一梳理。
首先,用户第一件事情是增强root账户的密码安全性。当用户安装MySQL时,根据所选的安装包进行初始密码的输入,Window的安装向导和DEB的安装过程中会提示输入root账户的初始密码,rpm的安装方式则会生成一个随机初始密码。使用随机密码连接MySQL后,首先要执行更改密码的语句,否则将报错。更改密码使用如下SQL:
代码语言:javascript复制ALTER USER root@localhost IDENTIFIED BY '<auth_string>';
注意,MySQL中可能会存在多个root账户,修改的密码仅仅是“root@localhost”,考虑安全性问题,可以删除全局账户,后缀为“%”,如果需要远程连接,需要对主机名进行限制。
设置密码策略。相关组件是否已安装?可以通过以下SQL语句进行查看:
代码语言:javascript复制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语句:
代码语言:javascript复制SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables where VARIABLE_NAME like 'valid%password%' OR VARIABLE_NAME='default_password_lifetime';
改变密码策略的SQL语句:
代码语言:javascript复制#安装组件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的连接控制。连接控制可以处理与暴力攻击相关的失败登录尝试。
代码语言:javascript复制#连接控制插件是否安装?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下载密钥进行替换。
代码语言:javascript复制#查看密钥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连接。
代码语言:javascript复制#查看是否开启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语句查看是否允许执行相关操作,
代码语言:javascript复制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';
如果变量值为路径名称,则允许在该路径下执行导入/导出操作。
代码语言:javascript复制 ------------------ ------------------------------------------------ ------------------- -------------- | VARIABLE_NAME | VARIABLE_VALUE | Note | SecFileCheck | ------------------ ------------------------------------------------ ------------------- -------------- | secure_file_priv | C:ProgramDataMySQLMySQL Server 8.4Uploads | Secure File Check | FAIL | ------------------ ------------------------------------------------ ------------------- --------------
检查 “local_infile”。
代码语言:javascript复制select if(@@local_infile, 'ON', 'OFF') as LOCAL_LOAD_DATA_ALLOWED;
MySQL 8.0 默认设置为OFF。也可以通过下方语句设置,
代码语言:javascript复制set persist local_infile=OFF;
其次,用户认证安全需要确认,谁?什么样的用户?在哪里?如何认证等方面的内容。用户包括内部用户,内部使用X.509的用户,外部认证用户,及代理用户。
内部用户,通过下方SQL语句查看相关内容,
代码语言:javascript复制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证书,
代码语言:javascript复制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确认,
代码语言:javascript复制SELECT `user`.`Host`, `user`.`User`,`user`.`plugin`, `user`.`authentication_string` from mysql.user where plugin like 'auth%';
多因素认证。MySQL至多支持三因素认证,下方是一个二因素认证的示例,
代码语言:javascript复制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语句添加第二个或第三个因素,
代码语言:javascript复制ALTER USER 'alice'@'localhost' ADD 3 FACTOR IDENTIFIED WITH authentication_fido;
角色和代理用户,
代码语言:javascript复制SELECT * FROM mysql.proxies_priv where grantor<>'boot@';#检查指定用户、角色或使用角色的用户SHOW GRANTS FOR 'app_developer'@'%';#用户使用角色SHOW GRANTS FOR 'u1'@'localhost' USING 'r1';
用户权限——最大连接数。假设最大连接策略为210,则,
代码语言:javascript复制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;
用户权限报告,
代码语言:javascript复制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”,
代码语言:javascript复制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权限,
代码语言:javascript复制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”,
代码语言:javascript复制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;
用户具有的全局/管理权限,
代码语言:javascript复制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的插件,
代码语言:javascript复制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使用的端口,
代码语言:javascript复制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');
检查文件的存储位置,
代码语言:javascript复制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;
检查密钥是否安全,
代码语言:javascript复制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';
检查表空间文件是否静态加密,
代码语言:javascript复制SELECT `INNODB_TABLESPACES`.`NAME`,`INNODB_TABLESPACES`.`ENCRYPTION`, IF(ENCRYPTION = 'Y', 'PASS', 'FAIL') as CHECK_VAL FROM `information_schema`.`INNODB_TABLESPACES` where ENCRYPTION='N';
要求使用TDE,
代码语言:javascript复制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,及审计日志是否加密,
代码语言:javascript复制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';
如果您认为这篇文章有些帮助,还请不吝点下文章末尾的"点赞"和"在看",或者直接转发朋友圈,