安全审计

本文档对 MatrixOne 安全审计基本功能进行阐述，并指引如何开启和使用。

概述

审计是用来记录数据库用户行为以及数据库内部重要事件的功能，它记录了所有用户在登录数据库后做出的所有数据库操作以及数据内部的重大事件。也是很多企业级数据库必备的功能之一。

在日常的数据库运维中，为了确保数据库用户的所有行为合规合法，审计是非常有效的手段。在数据库发生重要事件时，例如启停、节点宕机等，审计内容可以非常方便地追踪到前后时段的数据库行为。

对于重要的业务信息表或系统配置表需要进行有效完整的行为监控时，数据库审计的开启十分有必要。例如监控对用户 A 在数据库中所有行为，以便于及时发现违规的数据修改或删除来源。对于数据库内部重大事件的监控，可以第一时间排查故障，并且追溯事故产生的根本原因。

MatrixOne 支持对用户行为，操作日志，SQL 语句的审计。MatrixOne 的审计数据保存在数据库表中，直接通过 SQL 交互可以查询审计数据。

开启审计

执行如下内容脚本，开启审计功能：

  1. drop database if exists mo_audits;
  2. create database mo_audits;
  3. use mo_audits;
  4. create view mo_user_action as select request_at,user,host,statement,status from system.statement_info where user in (select distinct user_name from mo_catalog.mo_user) and statement not like '______internal_%' order by request_at desc;
  5. create view mo_events as select timestamp,level,message from system.log_info where level in ('error','panic','fatal') order by timestamp desc;

审计查询

对用户行为进行审计时，执行下面的 SQL 语句进行查看：

  1. mysql> select * from mo_audits.mo_user_action;

查询示例结果如下：

  1. +----------------------------+------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+
  2. | request_at                 | user | host    | statement                                                                                                                                                                                                                                      | status  |
  3. +----------------------------+------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+
  4. | 2023-02-10 19:54:28.831970 | root | 0.0.0.0 | create view mo_user_action as select request_at, user, host, statement, status from system.statement_info where user in (select distinct user_name from mo_catalog.mo_user) and statement not like "______internal_%" order by request_at desc | Success |
  5. | 2023-02-10 19:54:14.079939 | root | 0.0.0.0 | show tables                                                                                                                                                                                                                                    | Success |
  6. | 2023-02-10 19:54:14.076260 | root | 0.0.0.0 | show databases                                                                                                                                                                                                                                 | Success |
  7. | 2023-02-10 19:54:14.071728 | root | 0.0.0.0 | use mo_audits                                                                                                                                                                                                                                  | Success |
  8. | 2023-02-10 19:54:14.071108 | root | 0.0.0.0 | select database()                                                                                                                                                                                                                              | Success |
  9. | 2023-02-10 19:54:01.007241 | root | 0.0.0.0 | create database mo_audits                                                                                                                                                                                                                      | Success |
  10. | 2023-02-10 19:53:48.924819 | root | 0.0.0.0 | drop database if exists mo_audits                                                                                                                                                                                                              | Success |
  11. | 2023-02-10 19:30:59.668646 | root | 0.0.0.0 | show triggers                                                                                                                                                                                                                                  | Success |
  12. | 2023-02-10 19:30:53.438212 | root | 0.0.0.0 | show locks                                                                                                                                                                                                                                     | Success |
  13. | 2023-02-10 19:30:44.258894 | root | 0.0.0.0 | show index from t                                                                                                                                                                                                                              | Success |
  14. | 2023-02-10 19:30:43.662063 | root | 0.0.0.0 | create table t (a int, b int, c int, primary key (a))                                                                                                                                                                                          | Success |
  15. | 2023-02-10 19:30:23.104830 | root | 0.0.0.0 | show triggers                                                                                                                                                                                                                                  | Success |
  16. | 2023-02-10 19:30:20.062010 | root | 0.0.0.0 | show tables                                                                                                                                                                                                                                    | Success |
  17. | 2023-02-10 19:30:20.060324 | root | 0.0.0.0 | show databases                                                                                                                                                                                                                                 | Success |
  18. | 2023-02-10 19:30:20.055515 | root | 0.0.0.0 | use aab                                                                                                                                                                                                                                        | Success |
  19. | 2023-02-10 19:30:20.055186 | root | 0.0.0.0 | select database()                                                                                                                                                                                                                              | Success |
  20. | 2023-02-10 19:30:17.152087 | root | 0.0.0.0 | create database aab                                                                                                                                                                                                                            | Success |
  21. | 2023-02-10 19:30:10.621294 | root | 0.0.0.0 | create aab                                                                                                                                                                                                                                     | Failed  |
  22. | 2023-02-10 19:29:59.983433 | root | 0.0.0.0 | show databases                                                                                                                                                                                                                                 | Success |
  23. | 2023-02-10 19:29:45.370956 | root | 0.0.0.0 | show index from t                                                                                                                                                                                                                              | Failed  |
  24. | 2023-02-10 19:29:44.875580 | root | 0.0.0.0 | create table t (a int, b int, c int, primary key (a))                                                                                                                                                                                          | Failed  |
  25. | 2023-02-10 19:29:44.859588 | root | 0.0.0.0 | drop table if exists t                                                                                                                                                                                                                         | Success |
  26. | 2023-02-10 19:29:19.974775 | root | 0.0.0.0 | show index                                                                                                                                                                                                                                     | Failed  |
  27. | 2023-02-10 19:29:11.188286 | root | 0.0.0.0 | show locks                                                                                                                                                                                                                                     | Success |
  28. | 2023-02-10 19:29:06.618778 | root | 0.0.0.0 | show node list                                                                                                                                                                                                                                 | Success |
  29. | 2023-02-10 19:19:11.319058 | root | 0.0.0.0 | show triggers                                                                                                                                                                                                                                  | Failed  |
  30. | 2023-02-10 19:19:06.809302 | root | 0.0.0.0 | show databases                                                                                                                                                                                                                                 | Success |
  31. | 2023-02-10 19:18:52.840282 | root | 0.0.0.0 | show triggers                                                                                                                                                                                                                                  | Failed  |
  32. | 2023-02-10 10:54:09.892254 | root | 0.0.0.0 | show databases                                                                                                                                                                                                                                 | Success |
  33. | 2023-02-10 10:54:04.468721 | root | 0.0.0.0 | select @@version_comment limit 1                                                                                                                                                                                                               | Success |
  34. +----------------------------+------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+
  35. 30 rows in set (0.81 sec)

查询数据库内部状态变更查询，执行下面的 SQL 语句进行查看：

  1. mysql> select * from mo_events;

查询示例结果如下：

  1. |
  2. | 2022-10-18 15:26:20.293735 | error | error: timeout, converted to code 20429                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
  3. | 2022-10-18 15:26:20.293725 | error | failed to propose initial cluster info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
  4. | 2022-10-18 15:26:20.288695 | error | failed to set initial cluster info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
  5. | 2022-10-18 15:26:20.288559 | error | failed to propose initial cluster info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
  6. | 2022-10-18 15:26:20.285384 | error | failed to set initial cluster info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
  7. | 2022-10-18 15:26:20.285235 | error | failed to propose initial cluster info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
  8. | 2022-10-18 15:26:18.473472 | error | failed to join the gossip group, 1 error occurred:
  9.     * Failed to join 127.0.0.1:32022: dial tcp 127.0.0.1:32022: connect: connection refused                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
  10. | 2022-10-18 15:26:18.469029 | error | failed to join the gossip group, 1 error occurred:
  11.     * Failed to join 127.0.0.1:32012: dial tcp 127.0.0.1:32012: connect: connection refused

关闭审计

执行下面的 SQL 语句，关闭审计：

  1. > drop database if exists mo_audits;