Skip to main content

SQL Configuration Change Log Alerts Should Be Enabled

More Info:​

Ensures that logging and log alerts exist for SQL configuration changes. Project Ownership is the highest level of privilege on a project, any changes in SQL configurations should be heavily monitored to prevent unauthorized changes.

Risk Level​

Medium

Address​

Security

Compliance Standards​

  • CIS GCP
  • CIS GCP 2.0.0
  • Cloudanix Best Practice
  • HIPAA
  • HITRUST CSF
  • ISO 27001
  • NIST CSF
  • PCI
  • SOC2
  • Securities and Exchange Board of India (SEBI) - Cloud Security Adoption Framework

Triage and Remediation​

Remediation​

Using Console

To remediate the misconfiguration "SQL Configuration Change Log Alerts Should Be Enabled" in GCP, follow these steps:

  1. Go to the GCP Console and select the project that has the SQL instance that needs to be configured.

  2. Navigate to the SQL Instances page by clicking on the “SQL” option in the left-hand menu.

  3. Select the SQL instance that needs to be configured.

  4. Click on the “Edit” button at the top of the page.

  5. Scroll down to the “Alerting and Monitoring” section.

  6. In the “Alerting and Monitoring” section, enable the “Configuration changes” option.

  7. Click on the “Save” button at the bottom of the page to save the changes.

Once you have completed these steps, the SQL Configuration Change Log Alerts will be enabled for your SQL instance in GCP.

Using CLI

To remediate the misconfiguration "SQL Configuration Change Log Alerts Should Be Enabled" in GCP using GCP CLI, you can follow the below steps:

  1. Open the Cloud Shell in the GCP console.

  2. Run the following command to enable SQL Configuration Change Log Alerts for all Cloud SQL instances in the current project:

    gcloud sql instances patch [INSTANCE_NAME] --enable-bin-log --backup-start-time [HH:MM]

    Replace <INSTANCE_NAME> with the name of the Cloud SQL instance for which you want to enable the alerts. Replace <HH:MM> with the time at which you want to start the daily backup, in 24-hour format. For example, if you want to start the backup at 2:00 AM, you can use --backup-start-time 02:00.

  3. Run the following command to verify that the SQL Configuration Change Log Alerts have been enabled for the instance:

    gcloud sql instances describe [INSTANCE_NAME] | grep 'binaryLogEnabled\|backupStartTime'

    This command will display the values of the binaryLogEnabled and backupStartTime fields for the instance. If binaryLogEnabled is set to true and backupStartTime is set to the time you specified in step 2, then the SQL Configuration Change Log Alerts have been successfully enabled for the instance.

  4. Repeat steps 2 and 3 for all Cloud SQL instances in the project to ensure that the alerts are enabled for all instances.

By following these steps, you can remediate the "SQL Configuration Change Log Alerts Should Be Enabled" misconfiguration in GCP using GCP CLI.

Using Python

To remediate the misconfiguration "SQL Configuration Change Log Alerts Should Be Enabled" for GCP using Python, you can follow the below steps:

  1. Import the required libraries:
from googleapiclient import discovery
from oauth2client.client import GoogleCredentials
  1. Set up the credentials for the GCP project:
credentials = GoogleCredentials.get_application_default()
service = discovery.build('sqladmin', 'v1beta4', credentials=credentials)
  1. Get the list of SQL instances:
instances = service.instances().list(project='<project-id>').execute()
  1. Loop through each instance and check if the "enableDatabaseFlags" property is set to True:
for instance in instances['items']:
    if instance['settings']['databaseFlags']['enableDatabaseFlags']:
        print('SQL Configuration Change Log Alerts are already enabled for instance: ' + instance['name'])
    else:
        print('Enabling SQL Configuration Change Log Alerts for instance: ' + instance['name'])
        instance['settings']['databaseFlags']['enableDatabaseFlags'] = True
        service.instances().update(project='<project-id>', instance=instance['name'], body=instance).execute()
  1. Run the Python script, and it will enable SQL Configuration Change Log Alerts for all the SQL instances in the specified GCP project.

Note: Replace <project-id> with your GCP project ID in the code. Also, make sure that the Google Cloud SQL API is enabled for the project.

Using Terraform
resource "google_logging_metric" "sql_config_change_logs" {
  # Replace with your project ID
  project = "PROJECT_ID"

  # Terraform resource name; changing this forces replacement
  name        = "sql_configuration_change_count"
  description = "Count of Cloud SQL configuration change operations for monitoring and alerting."

  # Logs-based metric filter for Cloud SQL configuration changes via Cloud Audit Logs
  #
  # Adjust methodName list if you have additional/other SQL admin methods you care about.
  filter = <<-EOT
    logName = "projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Factivity"
    AND resource.type = "cloudsql_database"
    AND protoPayload.serviceName = "sqladmin.googleapis.com"
    AND protoPayload.methodName = (
      "cloudsql.instances.update"
      OR "cloudsql.instances.patch"
      OR "cloudsql.instances.promoteReplica"
      OR "cloudsql.instances.create"
      OR "cloudsql.instances.delete"
    )
  EOT

  metric_descriptor {
    metric_kind = "DELTA"
    value_type  = "INT64"
    unit        = "1"

    display_name = "SQL Configuration Change Count"
  }
}

Substitute:

  • PROJECT_ID with your actual GCP project ID in both project and the logName filter.

This change does not force replacement of any existing Cloud SQL instances, only creates (or replaces, if you change name) the logs-based metric itself.

To verify, terraform plan should show this google_logging_metric.sql_config_change_logs resource with + create and no other changes.

Additional Reading:​