Sql admin activity log enabled remediation

Using Console

Using CLI

To remediate the misconfiguration “Cloud SQL Admin Activity Audit Logging Should Be Enabled” in GCP using GCP CLI, follow the below steps:

Open the Cloud Shell in the GCP console.
Run the following command to enable Cloud SQL Admin Activity Audit Logging for all Cloud SQL instances in the default project:

gcloud sql instances patch [INSTANCE_NAME] --database-flags cloudsql.enable_iam_authorization=true,log_admin_operations=true

Replace [INSTANCE_NAME] with the name of your Cloud SQL instance.

Verify that the audit logging is enabled by running the following command:

gcloud sql instances describe [INSTANCE_NAME] --format="get(settings.databaseFlags)"

This command will return a list of all the database flags that are set for the Cloud SQL instance. Verify that log_admin_operations is set to on.

Repeat the above steps for all the Cloud SQL instances in your project.

By following the above steps, you can remediate the misconfiguration “Cloud SQL Admin Activity Audit Logging Should Be Enabled” for GCP using GCP CLI.

Using Python

To remediate the Cloud SQL Admin Activity Audit Logging misconfiguration in GCP using Python, you can follow the below steps:

First, you need to import the required libraries and authenticate to your GCP account using service account credentials.

from google.oauth2 import service_account
from googleapiclient.discovery import build

credentials = service_account.Credentials.from_service_account_file(
    'path/to/service/account/key.json')
service = build('logging', 'v2', credentials=credentials)

Next, you need to create a filter that will identify the Cloud SQL Admin Activity Audit logs.

filter_str = 'resource.type="cloudsql_database" AND logName:"cloudsql.googleapis.com/activity" AND protoPayload.methodName:"cloudsql.*"'

Then, you need to check if there are any sinks already created for the logs.

sinks = service.projects().sinks().list(
    project='your-project-id').execute()

If there are no sinks created, you can create a new sink for the logs.

sink_name = 'your-sink-name'
sink_filter = 'your-filter'
destination = 'bigquery.googleapis.com/projects/your-project-id/datasets/your-dataset-id'
sink = {
    'name': sink_name,
    'filter': sink_filter,
    'destination': destination
}
response = service.projects().sinks().create(
    project='your-project-id', body=sink).execute()

Finally, you need to verify that the sink was created successfully.

if 'error' in response:
    print('Error creating sink: {}'.format(response['error']))
else:
    print('Sink created successfully: {}'.format(response['name']))

With these steps, you can remediate the Cloud SQL Admin Activity Audit Logging misconfiguration in GCP using Python.

GCP Introduction

GCP Pricing

GCP Threats

GCP Misconfigurations

Resources

GCP Threats

Sql admin activity log enabled remediation

Triage and Remediation

Remediation

GCP Introduction

GCP Pricing

GCP Threats

GCP Misconfigurations

Resources

GCP Threats

​Triage and Remediation

​Remediation

Triage and Remediation

Remediation