等保测评mysql实例
一、查看(mysql性能参数)状态信息和版本
注:以下查询命令适用于mysql5.7以上版本,版本过低可能不适用
status
select @@version;
select version();
mysql> status
--------------
mysql Ver 8.0.26 for Linux on x86_64 (Source distribution)
Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.26 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 35 min 37 sec
Threads: 2 Questions: 5 Slow queries: 0 Opens: 117 Flush tables: 3 Open tables: 36 Queries per second avg: 0.002
--------------
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.26 |
+-----------+
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26 |
+-----------+
1 row in set (0.00 sec)
mysql>
二、查看插件
show plugins;
可以查看到mysql已安装的插件,以及鉴别信息采用的加密算法(如:SHA256、SHA1、SHA2
)等,比如登录失败所需插件CONNECTION_CONTROL(注:插件中如果有CONNECTION_CONTROL则已安装,没有的话,CONNECTION_CONTROL需要安装插件,一般只有安装该插件方可启用登录失败处理功能
)
三、身份鉴别及访问控制
1.检查是否存在空口令 ,是否存在多余过期的账户
select user,host,password from mysql.user; (注:针对数据库版本,“password”有些数据库版本为“authentication_string”,当前我所使用的数据库版本为8.0,对应“authentication_string”
)
select user,host from mysql.user;
select user,host,authentication_string from mysql.user;
select user,authentication_string from mysql.user;
2.检查是否定期更换口令,多久更换一次,口令是否过期
show global variables like ‘default_password_lifetime’; 查看定期多久更换一次,如90天
select host,user,password_expired,password_last_changed from mysql.user;(注:"password_expired"代表口令是否过期,为"Y"代表过期,"N"代表没有过期
)
mysql> show global variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 90 |
+---------------------------+-------+
1 row in set (0.00 sec)
mysql> select host,user,password_expired,password_last_changed from mysql.user;
+-----------+------------------+------------------+-----------------------+
| host | user | password_expired | password_last_changed |
+-----------+------------------+------------------+-----------------------+
| % | root | N | 2023-07-26 21:56:06 |
| localhost | mysql.infoschema | N | 2023-07-26 21:54:26 |
| localhost | mysql.session | N | 2023-07-26 21:54:26 |
| localhost | mysql.sys | N | 2023-07-26 21:54:26 |
+-----------+------------------+------------------+-----------------------+
4 rows in set (0.00 sec)
mysql>
3.检查口令复杂度策略
show variables like ‘validate_password%’;
注:
1)“validate_password.check_user_name” 值为“ON”表示已启用口令复杂度策略;
2)“validate_password_dictionary_file” 表示指定密码验证的文件路径;
3)“validate_password_length” 表示固定密码的总长度;
4)“validate_password_mixed_case_count ” 表示整个密码中至少要包含大/小写字母的总个数;
5)“validate_password_number_count ” 表示整个密码中至少要包含阿拉伯数字的个数;
6)“validate_password_policy” 表示指定密码的强度验证等级,默认为 MEDIUM;
7)“validate_password_special_char_count ” 表示整个密码中至少要包含特殊字符的个数;
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.01 sec)
其中:
0/LOW:表示只验证长度;
1/MEDIUM:表示验证长度、数字、大小写、特殊字符;
2/STRONG:表示验证长度、数字、大小写、特殊字符、字典文件;
4.检查登录失败处理功能
show variables like ‘%control%’;
show global variables like “%connection_control%”; ( 注:“connection_control” 一般需要安装插件才能启用登录失败,
在上面“2、查看插件”
处已有说明”)
show variables like ‘%MAX_connect_error%’; (表示最大错误连接数,ip连接mysql连接错误一次,就记录一次,如下:当达到配置文件中允许出错的最大次数( 100
)时,这时这个ip就连接不上了)
mysql> show variables like "%connection_control%";
+-------------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 5 |
| connection_control_max_connection_delay | 2147483647 |
| connection_control_min_connection_delay | 1000 |
+-------------------------------------------------+------------+
3 rows in set (0.00 sec)
mysql> show variables like '%control%';
+-------------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 5 |
| connection_control_max_connection_delay | 2147483647 |
| connection_control_min_connection_delay | 1000 |
+-------------------------------------------------+------------+
3 rows in set (0.00 sec)
mysql> show variables like '%MAX_connect_error%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
+--------------------+-------+
1 row in set (0.00 sec)
mysql>
其中:
connection_control_failed_connections_threshold 表示连续失败最大次数3次,0表示不开启
connection_control_max_connection_delay 表示超过最大失败次数之后阻塞登录最大时间(毫秒)
connection_control_min_connection_delay 表示超过最大失败次数之后阻塞登录最小时间(毫秒)
5.检查登录连接超时
show global variables like ‘%timeout%’;
show global variables like’connect_timeout’;(针对mysql客户端)
show variables like ‘wait_timeout’; 针对mysql服务器上(set wait_timeout = 900;一般不建议修改该系统变量,可能对mysql服务器的性能和安全性产生影响)
如下示例:
针对mysql客户端设置连接超时 300(单位/秒)
针对mysql服务器上连接超时 900(单位/秒)
mysql> show global variables like '%timeout%';
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| connect_timeout | 300 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| mysqlx_connect_timeout | 30 |
| mysqlx_idle_worker_thread_timeout | 60 |
| mysqlx_interactive_timeout | 28800 |
| mysqlx_port_open_timeout | 0 |
| mysqlx_read_timeout | 30 |
| mysqlx_wait_timeout | 28800 |
| mysqlx_write_timeout | 60 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| replica_net_timeout | 60 |
| rpl_stop_replica_timeout | 31536000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------------+----------+
22 rows in set (0.00 sec)
mysql> show global variables like'connect_timeout';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| connect_timeout | 300 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 900 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
6.检查用户权限控制
show grants for ‘root’@‘%’; (如查看root用户的权限是all或者select、drop等,此处对应用户进行查询,当前是root)
select * from mysql.user\G; 核查所有用户及其有哪些权限( 注:可查看到鉴别信息所采用的加密算法
)
示例:
mysql> show grants for 'root'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> select * from mysql.user\G;
*************************** 1. row ***************************
Host: %
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher: NULL
x509_issuer: NULL
x509_subject: NULL
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *1CC1E497CF5A96526175D9EC66AFBBCAECE32800
password_expired: N
password_last_changed: 2023-07-26 21:56:06
password_lifetime: NULL
account_locked: N
Create_role_priv: Y
Drop_role_priv: Y
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
*************************** 2. row ***************************
Host: localhost
User: mysql.infoschema
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher: NULL
x509_issuer: NULL
x509_subject: NULL
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: caching_sha2_password
authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
password_expired: N
password_last_changed: 2023-07-26 21:54:26
password_lifetime: NULL
account_locked: Y
Create_role_priv: N
Drop_role_priv: N
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
*************************** 3. row ***************************
Host: localhost
User: mysql.session
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: Y
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: Y
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher: NULL
x509_issuer: NULL
x509_subject: NULL
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: caching_sha2_password
authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
password_expired: N
password_last_changed: 2023-07-26 21:54:26
password_lifetime: NULL
account_locked: Y
Create_role_priv: N
Drop_role_priv: N
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
*************************** 4. row ***************************
Host: localhost
User: mysql.sys
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher: NULL
x509_issuer: NULL
x509_subject: NULL
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: caching_sha2_password
authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
password_expired: N
password_last_changed: 2023-07-26 21:54:26
password_lifetime: NULL
account_locked: Y
Create_role_priv: N
Drop_role_priv: N
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
4 rows in set (0.00 sec)
四、安全审计
show global variables like ‘%general%’; 查看general_log是否开启,如果开启则查看对应日志文件中是否有日志
show master status; 检查二进制日志状态
show global variables like ‘%log%’; 检查开启了哪些日志功能
show global variables like ‘server_audit%’; 检查是否启用审计服务
show global variables like ‘%audit%’; 检查是否启用审计服务
show global variables like ‘log_timestamps’; 表示记录审计日志的时间是从哪里获取
sudo head /var/lib/mysql/localhost.log (如果开启了审计功能,找到日志文件目录,如/var/lib/mysql/localhost.log,查看最早日志)
示例:
mysql> show global variables like '%general%';
+------------------+------------------------------+
| Variable_name | Value |
+------------------+------------------------------+
| general_log | ON |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000016 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%log%';
+------------------------------------------------+------------------------------------------+
| Variable_name | Value |
+------------------------------------------------+------------------------------------------+
| activate_all_roles_on_login | OFF |
| back_log | 151 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_encryption | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_expire_logs_seconds | 2592000 |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_rotate_encryption_master_key_at_startup | OFF |
| binlog_row_event_max_size | 8192 |
| binlog_row_image | FULL |
| binlog_row_metadata | MINIMAL |
| binlog_row_value_options | |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_compression | OFF |
| binlog_transaction_compression_level_zstd | 3 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| expire_logs_days | 0 |
| general_log | ON |
| general_log_file | /var/lib/mysql/localhost.log |
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 8192 |
| innodb_log_writer_threads | ON |
| innodb_max_undo_log_size | 1073741824 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_print_ddl_logs | OFF |
| innodb_redo_log_archive_dirs | |
| innodb_redo_log_encrypt | OFF |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /var/log/mysql/mysqld.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_raw | OFF |
| log_replica_updates | ON |
| log_slave_updates | ON |
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_replica_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | SYSTEM |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | localhost-relay-bin |
| relay_log_basename | /var/lib/mysql/localhost-relay-bin |
| relay_log_index | /var/lib/mysql/localhost-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | TABLE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
| sql_log_off | OFF |
| sync_binlog | 1 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
| terminology_use_previous | NONE |
+------------------------------------------------+------------------------------------------+
89 rows in set (0.00 sec)
mysql> show global variables like 'server_audit%';
Empty set (0.00 sec)
mysql> show global variables like '%audit%';
-> \c
mysql> show global variables like '%audit%';
Empty set (0.00 sec)
mysql> show global variables like 'log_timestamps';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| log_timestamps | SYSTEM |
+----------------+--------+
1 row in set (0.00 sec)
mysql>
[root@localhost sysadmin]# sudo head /var/lib/mysql/localhost.log
/usr/libexec/mysqld, Version: 8.0.26 (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2023-08-16T10:48:16.494191Z 8 Query SET global log_timestamps = SYSTEM
2023-08-16T18:51:45.462800+08:00 8 Query show variables like '%log%'
2023-08-16T18:52:02.662268+08:00 8 Query show global variables like '%log%'
2023-08-16T18:54:02.518355+08:00 8 Query show variables like '%timeout%'
2023-08-16T18:54:45.636991+08:00 8 Query show global variables like 'wait_timeout'
2023-08-16T18:55:12.199756+08:00 8 Query select version()
2023-08-16T18:55:52.470335+08:00 8 Query select * from mysql.user
五、关于终端接入管理限制
如下图示例图所示,限制仅允许192.*.**.4进行登录访问数据库进行管理,而localhost则为未限制管理地址,任意地址可进行访问管理。
–
示例:
六、关于加密算法(完整性与保密性)
show variables like ‘%ssl%’; 是否开启远程加密 ,传输过程中
show variables like ‘have_openssl’; 是否开启远程加密 ,传输过程中
select user,authentication_string,plugin from mysql.user; (注:存储加密如果plugin是mysql_native_password则是SHA1加密,如果是caching_sha2_password则是SHA256加密
)
示例:
七、需要导出的配置文件
需要导出的文件:
Linux中一般为:cat /etc/my.cnf(或/etc/mysql/my.cnf)
windows 中为 my.ini
总结
mysql一般现场情况都是默认配置,都不能达到测评符合要求,视情况而定,数据库有必要进行安全配置,如果数据都出现了问题,导致整个业务崩溃,后果可想而知!!!