MySQL数据库涉及安全性的SQL语句集合

bisal的个人杂货铺 2024-08-27

点击标题下「蓝色微信名」可快速关注

徐老师写的这篇文章《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：

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_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';
复制

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

+------------------+------------------------------------------------+-------------------+--------------+
| 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;
复制

用户权限报告，

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';
复制