Database Audit for Google Cloud SQL

Introduction

In today’s data-driven world, having a reliable database management solution is essential for maintaining competitiveness. As businesses adopt advanced techniques for database audit and database security, hackers continually evolve their methods to breach systems. This article explores how Google Cloud SQL provides a robust platform for conducting database audit while ensuring high-level compliance and security. Additionally, we’ll cover best practices to enhance the security of your SQL database.

What is Google Cloud SQL?

Google Cloud SQL is a unified platform for managing relational databases, including MySQL, PostgreSQL, and SQL Server (MSSQL). Its user-friendly, web-based interface makes executing SQL queries and managing databases seamless. The platform offers tools for reliable database auditing, connecting storage buckets, and configuring secure environments, making it a flexible solution for secure data management.

Let’s explore how to configure a database audit using Google Cloud SQL.

Setting Up a Database Audit in Google Cloud SQL

To set up a database audit in Google Cloud SQL, start by ensuring you have a functional database instance. This guide uses an SQL Server Express 19 instance. A crucial step involves configuring a storage bucket for your audit logs:

Once storage is configured, you can proceed with the audit setup.

Types of Database Audits in SQL Server

In SQL Server, audits are categorized into three levels:

1. Server-level Audit: Tracks high-level events like authentication attempts and management changes.
2. Database-level Audit: Monitors DML (Data Manipulation Language) and DDL (Data Definition Language) operations.
3. Audit-level Actions: Logs activities related to the auditing process.

Here’s how to create a Server-level Audit in SQL Server:

CREATE SERVER AUDIT testAudit TO FILE (FILEPATH = '/var/opt/mssql/audit', MAXSIZE = 10 MB);
ALTER SERVER AUDIT testAudit WITH (STATE = ON);

Creating an Audit Specification

Specify the events to monitor using an Audit Specification. For instance, the following captures all failed login attempts:

CREATE SERVER AUDIT SPECIFICATION testAuditSpec  
FOR SERVER AUDIT testAudit  
    ADD (FAILED_LOGIN_GROUP)  
    WITH (STATE=ON); 
ALTER SERVER AUDIT SPECIFICATION testAuditSpec WITH (STATE=ON);

View the audit results using this stored procedure:

SELECT * FROM msdb.dbo.gcloudsql_fn_get_audit_file('/var/opt/mssql/audit/*', NULL, NULL);

Expanding to Database-Level Audits

For more granular monitoring, configure Database-level Audits. For example, to log all queries on the MOCK_DATA database by a specific user:

CREATE DATABASE AUDIT SPECIFICATION dbSpec 
FOR SERVER AUDIT testAudit  
    ADD (SELECT ON dbo.MOCK_DATA BY testuser) 
WITH (STATE = ON);

Logs for queries executed under the testuser account will be stored in the configured bucket. You can retrieve and analyze these logs as follows:

SELECT * FROM msdb.dbo.gcloudsql_fn_get_audit_file('/var/opt/mssql/audit/*', NULL, NULL);

Granular Rule-Based Database Audit: DataSunrise + Google Cloud SQL

While Google Cloud SQL provides foundational auditing tools, integrating third-party solutions like DataSunrise elevates your database audit and security capabilities. DataSunrise offers comprehensive tools for database security, real-time threat assistance, and data compliance.

Here’s how to use DataSunrise to set up granular audit rules:

• Add the database to the list of managed databases:

• Create an audit rule by clicking “Add New Rule.”:

• Configure general settings:

• Configure filter sessions so only sessions from testuser are audited:

• Set up a statement filter to monitor only the MOCK_DATA database:

Save your settings and view audit logs in the Transactional Trails tab:

These logs are interactive and you can create separate rules auditing a certain types of queries:

Best Practices for Database Audit in Google Cloud SQL

To maximize performance and security when auditing Google Cloud SQL, consider these best practices:

1. Place both DataSunrise and MSSQL instances within Google’s Virtual Private Cloud (VPC).
2. Allow external connections only through the DataSunrise proxy for enhanced security.

Conveniently, DataSunrise is available on the Google Cloud Marketplace for ease of configuration of such setup.

Conclusion

Conducting a database audit is a complex yet critical task to ensure compliance with regulatory standards and protect against security threats. Use a combination of built-in tools from MSSQL and advanced third-party solutions like DataSunrise to achieve comprehensive audit coverage and enhanced database security. Leveraging Google Cloud SQL as your platform simplifies setup and management while ensuring robust data protection.