Auditing is one of the key concerns with Security & Compliance for many organizations.
This article is written to share a tutorial how to do audit log archiving by reading the audit log. The audit archiving process is to read the audit log using audit_log_read function provided to access to the audit. The data once is archived to Table "audit_archive.audit_data" and the table is renamed to "audit_data_<timestamp>" accordingly. The table is dumped to OCI Object Storage bucket using mysql shell utility. The tutorial is for demo purpose only.
MySQL Enterprise Audit is the feature allowing audit to be implemented. For managed service of "MySQL", namely HeatWave Service on Oracle Cloud Infrastructure, Enterprise Audit is installed. If running MySQL Enterprise Edition on your compute VM, please refer to the documentation on how to install MySQL Enterprise Audit
Ref : https://dev.mysql.com/doc/refman/8.4/en/audit-log-installation.html
The current practice with default AUDIT feature in HeatWave is to generate the AUDIT record in JSON format. Here below is system variables "audit%" example when HeatWave MySQL is provisioned.
mysql> show variables like 'audit%';
+--------------------------------------+---------------------+
| Variable_name | Value |
+--------------------------------------+---------------------+
| audit_log_buffer_size | 10485760 |
| audit_log_compression | GZIP |
| audit_log_connection_policy | ALL |
| audit_log_current_session | OFF |
| audit_log_database | mysql_audit |
| audit_log_disable | OFF |
| audit_log_encryption | NONE |
| audit_log_exclude_accounts | |
| audit_log_file | /db/audit/audit.log |
| audit_log_filter_id | 1 |
| audit_log_flush | OFF |
| audit_log_flush_interval_seconds | 60 |
| audit_log_format | JSON |
| audit_log_format_unix_timestamp | ON |
| audit_log_include_accounts | |
| audit_log_max_size | 5368709120 |
| audit_log_password_history_keep_days | 0 |
| audit_log_policy | ALL |
| audit_log_prune_seconds | 604800 |
| audit_log_read_buffer_size | 32768 |
| audit_log_rotate_on_size | 52428800 |
| audit_log_statement_policy | ALL |
| audit_log_strategy | ASYNCHRONOUS |
+--------------------------------------+---------------------+
23 rows in set (0.09 sec)
Audit Log Pruning
Audit Log Pruning is enabled for JSON format on OCI HeatWave Service.
For details about Audit Log Pruning, please refer to documentation : https://dev.mysql.com/doc/refman/8.4/en/audit-log-logging-configuration.html#audit-log-pruning
The audit_log_max_size is configured as 5368709120 (~5GB). The audit_log_prune_seconds is configured as 604800 (7 days).
Based on the documentation above,
"Nonzero values of audit_log_max_size take precedence over nonzero values of audit_log_prune_seconds. "
The audit_log_max_size as 5GB takes precedence for pruning action. The "audit_log_rotate_on_size" is about 50MB.
Demo Environment
- Oracle Cloud Infrastructure (OCI) HeatWave Service
- Version : 9.0.1
- Compute VM : Oracle Linux 8
- MySQL Shell version : 9.0.1
- oci cli installed and configured
https://docs.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm#InstallingCLI__oraclelinux8
Creating Table Structure for AUDIT ARCHVE
The archived audit records are stored in DB table(s).
| Table | Description |
| audit_config | To keep track the last record of the timestamp and id for audit log identifier for each server_uuid |
| audit_data_template | The Table Structure template for audit_data |
| audit_data | The Table to store the running archived records. |
| audit_data_<timestamp> | Each time archiving is executed, it retrieves audit records from timestamp registerd in audit_config table until all records are retrieved. Thereafter it renames the table to the audit_data_<timestamp>. |
