6.4.5.6 读取审计日志文件_MySQL 5.7 参考手册

MySQL 8.0 参考手册 / 第 6 章安全 / 6.4 安全组件和插件 / 6.4.5 MySQL企业审计 / 6.4.5.6 读取审计日志文件

6.4.5.6 读取审计日志文件

审计日志插件支持为读取 JSON 格式的审计日志文件提供 SQL 接口的函数。（此功能不适用于以其他格式编写的日志文件。）

当审计日志插件初始化并配置为 JSON 日志记录时，它使用包含当前审计日志文件的目录作为搜索可读审计日志文件的位置。该插件根据系统变量的值确定文件位置、基本名称和后缀audit_log_file ，然后查找名称与以下模式匹配的文件，其中[...]表示可选的文件名部分：

basename[.timestamp].suffix[.gz][.enc]

如果文件名以结尾.enc，则文件已加密，读取未加密的内容需要从密钥环中获取解密密码。有关加密审计日志文件的更多信息，请参阅加密审计日志文件。

该插件会忽略手动重命名且与模式不匹配的文件，以及密钥环中不再可用的密码加密文件。该插件打开每个剩余的候选文件，验证该文件是否实际包含JSON审计事件，并使用每个文件的第一个事件的时间戳对文件进行排序。结果是一系列文件，这些文件可以使用日志读取功能进行访问：

audit_log_read()从审计日志中读取事件或关闭读取进程。
audit_log_read_bookmark() 返回最近写入的审核日志事件的书签。此书签适合传递给以 audit_log_read()指示从哪里开始阅读。

audit_log_read()接受一个可选的JSON字符串参数，成功调用任一函数返回的结果都是一个JSON字符串。

要使用函数读取审计日志，请遵循以下原则：

调用audit_log_read()以读取从给定位置或当前位置开始的事件，或关闭读取：
- 要初始化审核日志读取序列，请传递一个参数以指示开始位置。一种方法是传递由返回的书签 audit_log_read_bookmark()：
```
SELECT audit_log_read(audit_log_read_bookmark());
```
- 要从序列中的当前位置继续读取，请 audit_log_read()在不指定位置的情况下调用：
```
SELECT audit_log_read();
```
- 要显式关闭读取序列，请传递一个参数： JSON null
```
SELECT audit_log_read('null');
```
  没有必要明确关闭阅读。audit_log_read()当会话结束或通过使用指示开始位置的参数调用来初始化新读取序列时，读取将隐式关闭。
成功调用 audit_log_read()读取事件会返回JSON包含审计事件数组的字符串：
- 如果返回的数组的最终值不是一个值，则在刚刚读取的事件之后还有更多事件，可以再次调用以读取更多事件。 JSON nullaudit_log_read()
- 如果返回数组的最终值是一个值，则当前读取序列中没有更多的事件需要读取。 JSON null
每个非null数组元素都是一个表示为JSON散列的事件。例如：
```
[
  {
    "timestamp": "2020-05-18 13:39:33", "id": 0,
    "class": "connection", "event": "connect",
    ...
  },
  {
    "timestamp": "2020-05-18 13:39:33", "id": 1,
    "class": "general", "event": "status",
    ...
  },
  {
    "timestamp": "2020-05-18 13:39:33", "id": 2,
    "class": "connection", "event": "disconnect",
    ...
  },
  null
]
```
有关 JSON 格式审计事件内容的更多信息，请参阅JSON 审计日志文件格式。
在audit_log_read()以下任何情况下，调用未指定位置的读取事件都会产生错误：
- 尚未通过将位置传递给来初始化读取序列 audit_log_read()。
- 当前读取序列中没有更多事件需要读取；也就是说， audit_log_read() 先前返回了一个以值结尾的数组。 JSON null
- 最近的读取序列已通过将值传递给关闭。 JSON nullaudit_log_read()
要在这些条件下读取事件，必须首先通过 audit_log_read()使用指定位置的参数调用来初始化读取序列。

要将位置指定给 audit_log_read()，请传递一个书签，它是一个包含唯一标识特定事件的元素的 JSON散列。这是一个示例书签，通过调用函数获得： timestampidaudit_log_read_bookmark()

mysql> SELECT audit_log_read_bookmark();
+-------------------------------------------------+
| audit_log_read_bookmark()                       |
+-------------------------------------------------+
| { "timestamp": "2020-05-18 21:03:44", "id": 0 } |
+-------------------------------------------------+

传递当前书签以 audit_log_read()初始化从书签位置开始的事件阅读：

mysql> SELECT audit_log_read(audit_log_read_bookmark());
+-----------------------------------------------------------------------+
| audit_log_read(audit_log_read_bookmark())                             |
+-----------------------------------------------------------------------+
| [ {"timestamp":"2020-05-18 22:41:24","id":0,"class":"connection", ... |
+-----------------------------------------------------------------------+

的参数audit_log_read() 是可选的。如果存在，它可以是关闭读取序列的值或散列。 JSON nullJSON

Within a hash argument to audit_log_read(), items are optional and control aspects of the read operation such as the position at which to begin reading or how many events to read. The following items are significant (other items are ignored):

timestamp, id: The position within the audit log of the first event to read. If the position is omitted from the argument, reading continues from the current position. The timestamp and id items together comprise a bookmark that uniquely identify a particular event. If an audit_log_read() argument includes either item, it must include both to completely specify a position or an error occurs.
max_array_length: The maximum number of events to read from the log. If this item is omitted, the default is to read to the end of the log or until the read buffer is full, whichever comes first.

Example arguments accepted by audit_log_read():

Read events starting with the event that has the exact timestamp and event ID:
```
audit_log_read('{ "timestamp": "2020-05-24 12:30:00", "id": 0 }')
```

Like the previous example, but read at most 3 events:

audit_log_read('{ "timestamp": "2020-05-24 12:30:00", "id": 0, "max_array_length": 3 }')

Read events from the current position in the read sequence:
```
audit_log_read()
```
Read at most 5 events beginning at the current position in the read sequence:
```
audit_log_read('{ "max_array_length": 5 }')
```
Close the current read sequence:
```
audit_log_read('null')
```

To use the binary JSON string with functions that require a nonbinary string (such as functions that manipulate JSON values), perform a conversion to utf8mb4. Suppose that a call to obtain a bookmark produces this value:

mysql> SET @mark := audit_log_read_bookmark();
mysql> SELECT @mark;
+-------------------------------------------------+
| @mark                                           |
+-------------------------------------------------+
| { "timestamp": "2020-05-18 16:10:28", "id": 2 } |
+-------------------------------------------------+

Calling audit_log_read() with that argument can return multiple events. To limit audit_log_read() to reading at most N events, convert the string to utf8mb4, then add to it a max_array_length item with that value. For example, to read a single event, modify the string as follows:

mysql> SET @mark = CONVERT(@mark USING utf8mb4);
mysql> SET @mark := JSON_SET(@mark, '$.max_array_length', 1);
mysql> SELECT @mark;
+----------------------------------------------------------------------+
| @mark                                                                |
+----------------------------------------------------------------------+
| {"id": 2, "timestamp": "2020-05-18 16:10:28", "max_array_length": 1} |
+----------------------------------------------------------------------+

The modified string, when passed to audit_log_read(), produces a result containing at most one event, no matter how many are available.

To read a specific number of events beginning at the current position, pass a JSON hash that includes a max_array_length value but no position. This statement invoked repeatedly returns five events each time until no more events are available:

SELECT audit_log_read('{"max_array_length": 5}');

To set a limit on the number of bytes that audit_log_read() reads, set the audit_log_read_buffer_size system variable. As of MySQL 5.7.23, this variable has a default of 32KB and can be set at runtime. Each client should set its session value of audit_log_read_buffer_size appropriately for its use of audit_log_read(). Prior to MySQL 5.7.23, audit_log_read_buffer_size has a default of 1MB, affects all clients, and can be changed only at server startup.

有关审核日志读取功能的其他信息，请参阅审核日志功能。