How to Configure DB Audit Trailing for MS Azure MySQL

Along with other functionalities, DataSunrise features a dedicated auditing tool, the DB Audit Trailing, based on usage of database native auditing tools and mechanisms. In this article we explain how to configure DataSunrise and your MySQL database hosted on MS Azure to do DB Audit Trailing.

Note that this guide describes all the actions required for DB Audit Trailing to work from the very beginning. Let’s assume that you don’t have neither a database nor the corresponding Azure resources. To follow the steps of this guide you need only DataSunrise installed on your machine and access to Microsoft Azure portal.

Log into Microsoft Azure Portal

First, log into Microsoft Azure portal and follow the steps below. If you’re not acquainted with Azure’s interface, you might find it useful to use the search field located at the top of the screen (“Search resources, services, and docs”).

Registration of an Azure Application

DataSunrise will use this application to download your MySQL’s log files out of the Blob container associated with your Storage account. Follow the step below:

1. Navigate to App registrations -> New registration and register your application. Note that Redirect URI (optional) is not required.

2. Grant your App the permissions to access Blob containers.

At the API permissions page, click Add a permission

In the Microsoft APIs tab, select Azure Storage then select Delegated permissions and user_impersonation:

Click Add permissions to apply the changes

3. Create a Secret for your App so Azure could identify your App.

• Navigate to Certificates and secrets
• At Client secrets, click New client secret to create a new secret. SAVE THE SECRET VALUE SOMEWHERE: you will need it later.

Creation of a Resource Group

A Resource group is needed to contain entities (resources) associated with your database environment.

Navigate to Resource groups and create a Resource group.

Creation of a Storage Account

Storage account will contain your MySQL’s log files. DataSunrise will get these logs from your Storage account.

1. Navigate to Storage accounts and create an Account

2. Select your Resource group in the Storage account’s settings and name the Storage account. Leave all other settings by default

3. Navigate to Access Control (IAM) and click Add -> Add role assignment

Select Reader: Next. Click +Select members and select your registered App. Review and assign. This is needed for your App to be able to access log files contained in the Blob containers of your Storage account:

4. Click Add role assignment again and select Storage Blob Data Reader.

5. Click +Select Members and select your registered App. Review and assign

Creation of a MySQL Database Server

Create a MySQL database server if you don’t have one. Otherwise, edit your database’s settings according to the steps below:

1. Navigate to Azure Database for MySQL servers and create a new server: navigate to Create -> Flexible server -> Create

2. Provide all the required server details

3. At the Networking tab, check Public access… and add required firewall rules. Complete the deployment.

4. Navigate to your MySQL database -> Settings -> Server Parameters and enable audit_log_enabled to enable native auditing for your database. Select event types to be logged by configuring audit_log_events. Add MySQL users to be included in or excluded from logging by configuring audit_log_exclude_users and audit_log_include_users respectively.

Save the settings.

5. Configure your database to store logs in an associated Storage account.

Navigate to Monitoring -> Diagnostic settings -> Add diagnostic setting

Check MySqlAuditLogs and Archive to a storage account and select your Storage Account:

Creation of a MySQL Instance in DataSunrise

Open DataSunrise’s Web Console and navigate to Configuration -> Databases to create a MySQL database Instance (refer to subs. “Creating a Target Database Profile” of the DataSunrise’s User Guide for details).

You will need to provide the following information:

1. Select Trailing the DB Audit Logs in the Instance’s settings. Fill out all the required fields.

2. Copy ClientID and TenantID from your Azure App to make DataSunrise able to use the App for downloading your MySQL’s log files:

3. Input Client Secret saved before (refer to step 3 of Registration of an Azure Application).

4. Input Blob Container Name. You can find your Blob container name in Azure’s Storage Accounts -> your account -> Containers of your Azure settings:

Audit Results

Create some Audit Rules for your MySQL database (refer to subs “Creating a Data Audit Rule” of the User Guide) and execute some queries on your MySQL database. Navigate to Audit -> Transactional trails for auditing results: