MySQL HeatWave Database Audit for Data Governance, Compliance, and Security

We are excited to introduce MySQL HeatWave auditing. It provides a robust and powerful auditing mechanism that meets the most demanding data governance, compliance, and security requirements for: 

• FedRAMP
• DISA STIG
• PCI-DSS
• HIPAA
• SOX
• GDPR
• FERPA
• Center for Internet Security Benchmarks
• and more

MySQL HeatWave Audit leverages the robust technology found in MySQL Enterprise Audit. It enables Database Administrators to define filters that specify which events and activities are collected. These database events provide details like who, what, when, where, how, and more. Additionally, optional query execution metrics can be included, which are useful for pinpointing issues, such as slow queries. Furthermore, if you have established rules within MySQL Enterprise Auditing, be it on-premise or on another system, you can effortlessly migrate these rules to MySQL HeatWave instances in the cloud, ensuring consistency and seamless integration across diverse database environments.

Why use MySQL HeatWave Database Audit?

1. Rigorous Compliance & Forensics: Allows you to comply with industry regulations and create a thorough record for investigations following an incident.
2. Security Operations (SecOps): Keep an eye on user behaviors and spot potential threats in real-time.
3. Holistic Server ActivityTracking: Covers all auditing needs, from basic client connects and disconnects, to more specific activities and events within the database, such as interactions with specific schemas and tables, security changes, and errors.
4. Insights into Query and Statement Performance: Track query execution statistics and pinpoint slow queries for database performance optimization and detection of outliers.
5. Utilization: Spot bottlenecks and streamline your database operations based on data-driven insights. 

Business Benefits

• “Trust but verify” security principle:
  • Monitor users with high-level privileges to prevent misuse.
• Business audit:
  • Proves data validity with detailed records for data accuracy and database integrity checks.
  • Prove no tampering to data has occurred.
• Security analysis:
  • Essential component for any defense-in-depth strategy.
  • Proactive – during an attack, audit data can be utilized by security products that include machine learning to automatically detect and flag suspicious anomalies.
  • Reactive – assessment of attacks post-mortem. Identify how it happened and by whom as well as what was altered, stolen, or encrypted.

Feature Highlights

• Ready to Use: No installation steps are required to use the Database Audit feature. Focus on easily securing and monitoring your database activities.
• Customizable Auditing: Choose to monitor specific operations, particular users, or even broad activity categories.
• Real-time & Minimal Overhead: Ensuring instantaneous access to database activity with minimal performance overhead, elevating the possibilities of using audit for a variety of use cases.
• Automatic Log Rotation & Management: Built-in log rotation and automatic purging, ensuring your DB systems stays optimized without any extra effort on your part.
• Multi-Instance Support: Works with standalone, multi-instance, or high-availability (HA). Your Audit filters and configurations are replicated to other instances, ensuring no database activity event is lost.
• Access via SQL interface: Use any MySQL client to connect to your DB systems and query the logs directly from the SQL interface. You can channel your logs to OCI Logging Analytics, third-party monitoring tools, or your SIEM systems if needed.

Audit Log Details 

The audit events generated and maintained by MySQL HeatWave Database Audit have the following key characteristics:

• JSON format: entries are structured in the easily interpretable and widely used JSON format, ensuring compatibility and seamless integration.
• Encrypted: each log is encrypted, ensuring that the contained data is shielded from unauthorized eyes.
• Compressed: logged data is compressed to optimize storage and backups.
• Read Only SQL statement access: while you can access these logs using SQL statements, they remain in a read-only mode, ensuring their integrity remains intact.
• Size and age-based deletion: automatic deletion based on logs size or age for storage efficiency.
• Option to remove sensitive data from statements: omit sensitive data before logging.
• Option to collect performance metrics: gain a deeper insight into database query execution statistics, such as slow queries.

Filters: Defining What to Audit

Using the powerful filter capabilities, Database Administrators can:

• Remove noise and eliminate distractions, ensuring targeted and focused audit data.
• Minimize the size of audit log files.
• Optimize File System I/O and storage, enhancing performance with fewer logged items.
• Increase audit log post processing efficiency with less entries to process for quicker answers.

Workflow

Below is a workflow diagram illustrating a typical process and key stages involved in implementing and utilizing MySQL HeatWave Database Audit.

How to Use MySQL HeatWave Database Audit

Pre-requisites

1. Connect to your DB system and check MySQL version and if Audit components are present.

   1. SELECT version();

      Must be 8.0.34-u2 or higher to contain Audit.

   2. SELECT * FROM mysql.plugin;

      Results should contain audit_log.

   3. SELECT * FROM mysql.component;

      Results should contain component_audit_api_message_emit.
2. Check grants.
   1. By default the MySQL HeatWave administrator user you defined when creating the DB system has the AUDIT_ADMIN privilege.

      SHOW GRANTS;

      Results should contain AUDIT_ADMIN.
   2. To add more users with audit administration privilege use:

      GRANT AUDIT_ADMIN ON *.* TO <user>;

Filters

1. Define filters. 
   1. Start by creating audit filters. For example
      To audit all events:

      SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');

      To audit connection events alone:

      SELECT audit_log_filter_set_filter('log_conn_events','{ "filter": { "class": { "name": "connection" } } }');

   2. To view audit filters, run:

      SELECT * FROM mysql_audit.audit_log_filter;

   3. More examples of common filters are available in the following resources:
      1. MySQL reference manual.
      2. My Oracle Support Knowledge Base.
         
2. Assign filter to users.
   1. Audit rules can be added by default to all users or to specific users. 
      For example, to define the default audit rule to log all connect events, use the wildcard character ‘%’:

      SELECT audit_log_filter_set_user('%', 'log_conn_events');

      Or for a specific user  – here we filter all events for Joe the DBA:

      SELECT audit_log_filter_set_user('joe_dba@%', 'log_all');

   2. To view the assigned rules, use:

      SELECT * FROM mysql_audit.audit_log_user;

   3. To unassign the rules from a user:

      SELECT audit_log_filter_remove_user('joe_dba@%');

3. You can optionally insert your own event data with the component audit_api_message_emit.
   For example, suppose you want to add additional context in the audit log, you can provide context data with:

   SELECT audit_api_message_emit_udf('component_text', 'producer_text', 'message_text', 'key1', 'value1', 'key2', 123, 'key3', NULL) AS 'Message';

Accessing and Analyzing Audit Data

1. To view the audit data, users can interact with it via SQL commands. The primary function utilized for this purpose is the audit_log_read() function, which, when invoked, fetches the audit data and presents it in JSON format.
   
   For a straightforward extraction of audit data, use the following command: 

   SELECT audit_log_read(audit_log_read_bookmark());

   The audit_log_read_bookmark() function provides a digital bookmark indicating the end of the audit log. It’s particularly useful for periodic log checks, where you want to ensure no events are reviewed multiple times or missed. In essence, every time you run the command above, you’re asking the system to show you any new logs since you last checked. This ensures you’re always updated with the latest audit logs without revisiting older logs or missing out on any new ones.
    
2. You can also extract more specific sets of audit data by providing additional parameters within the audit_log_read() function.
   For instance, to extract audit logs starting from a particular timestamp, you can use:

   SELECT audit_log_read('{ "start": { "timestamp": "2023-08-24 12:30:00" }, "max_array_length": 500 }');

3. To analyze the audit data, a more human-readable format can be useful. You can use JSON_PRETTY() and CONVERT() functions to beautify the JSON format and make it easier to read and understand. For example:

   SELECT JSON_PRETTY(CONVERT(audit_log_read( ... ) USING UTF8MB4));

   Output example:

   	{ "timestamp": "2022-01-28 13:09:30", 
     "id": 0, 
     "class": "general", 
     "event": "status", 
     "connection_id": 46, 
     "account": { "user": "user", "host": "localhost" }, 
     "login": { "user": "user", “os": "", “ip": "127.0.0.1", “proxy": "" }, 
     "general_data": { "command": "Query", 
                       "sql_command": "insert",
              "query": "INSERT INTO audit_table VALUES(4)",
              "status": 1146 }
     "query_statistics": { "query_time": 0.116250,
                           "bytes_sent": 18384,
                           "bytes_received": 78858,
                           "rows_sent": 3,
                           "rows_examined": 20878 }
   

4. You can also use MySQL JSON functions to transform data to tabular format using MySQL JSON functions. In the following example, we use transform a subset of the JSON name-value pairs into a structured table format, making it easier to interact with and analyze. 

   SELECT @@server_uuid as server_uuid, ts, class, event, login_ip,login_user,connection_id,
    status,connection_type,_client_name,_client_version,
    command,sql_command,command_status
   FROM
   JSON_TABLE
   (
     AUDIT_LOG_READ( '{ "start": {\"timestamp\": \"2023-08-16 15:33:37\"}, \"max_array_length\": 10 }' ), 
     '$[*]'
     COLUMNS
     ( 
       ts TIMESTAMP PATH '$.timestamp',
       class VARCHAR(20) PATH '$.class',
       event VARCHAR(80) PATH '$.event',      
       login_ip VARCHAR(200) PATH '$.login.ip',
       login_user VARCHAR(200) PATH '$.login.user',
       connection_id VARCHAR(80) PATH '$.connection_id',
       status INT PATH '$.connection_data.status',
       connection_type VARCHAR(40) PATH '$.connection_data.connection_type',
       _client_name VARCHAR(80) PATH '$.connection_data.connection_attributes._client_name',
       _client_version VARCHAR(80) PATH '$.connection_data.connection_attributes._client_version',
       command VARCHAR(40) PATH '$.general_data.command',
       sql_command VARCHAR(40) PATH '$.general_data.sql_command',
       command_status VARCHAR(40) PATH '$.general_data.status'
      )) as audit_log;

5. Tip: To further refine your data extraction, use WHERE clauses in your SQL statements. For instance: WHERE connection_type <> 'SSL'.

Extended Analysis with OCI Logging Analytics and OCI Data Integration

For a deeper dive into your audit data and longer retention you can leverage the advanced analytical capabilities of OCI Logging Analytics. Here are the key benefits:

1. Enhanced Visibility: A centralized view of all MySQL audit logs, enabling identification of suspicious activities or performance anomalies.
2. Long-Term Retention: Retain logs for extended periods, ensuring compliance with various regulatory standards and facilitating historical analysis.
3. Scalable Log Management: Handle large volumes of audit logs, ensuring efficient storage, indexing, and querying.
4. Advanced Analysis: Perform complex queries, pattern recognition, and visualizations, making it easier to derive insights from your MySQL audit data.
5. Automated Alerts: Set up custom alerts based on specific log patterns or thresholds, ensuring real-time notifications of potential issues or security breaches.
6. Integrated Security: Built-in security features, including encryption, access controls, and compliance certifications.

OCI Data Integration can be used to pull data directly from MySQL HeatWave via SQL. This can be used for many purposes, including moving audit data from MySQL HeatWave and pushing to centralize audit repositories for analysis.

For example – OCI Logging Analytics or Object Storage for long term archival. By using OCI Data Integration, users benefit from a simplified process, ensuring efficient extraction of database logs and data within OCI’s robust environment.

MySQL HeatWave Database Audit, OCI Logging Analytics, and OCI Data Integration can be combined to offer an advanced, scalable, secure, and streamlined solution for comprehensive database monitoring and management.

Conclusion

MySQL HeatWave Database Audit provides advanced auditing capabilities for the most demanding data governance, compliance, and security requirements. 

We look forward to your comments and feedback.

Thank you for using MySQL HeatWave.

To learn more about Database Audit

• MySQL HeatWave Release Notes.
• MySQL HeatWave Technical Documentation.
• MySQL Database Technical Documentation.
• More blog posts on auditing.

To learn more about MySQL HeatWave, visit oracle.com/mysql. For a free trial account, visit oracle.com/mysql/free.

Thanks for using MySQL HeatWave!