Skip to main content
Lantern Home

 

Splunk Lantern

Tracking GCP CloudSQL permission changes

  1. Last updated
  2. Save as PDF

Logging and identifying GRANT / REVOKE commands issued is key to tracking permission changes over time and preventing malicious actors from gaining access to your data. We can modify the search used to identify DDL operations, to instead search for the GRANT and REVOKE keywords.

If you haven't already configured your Splunk platform deployment to ingest GCP Cloud SQL logs from Postgres, MySQL, and SQL Server databases, review Monitoring Google Cloud SQL first.  

Data required

How Splunk software can help with this use case

Use the following general base search to get the right source type, source, and specific database (if desired).

index="gcp-auditlogs" 
    sourcetype="google:gcp:pubsub:platform"
    source="projects/project_id/subscriptions/cloudsql_auditlogs
_sub_id" 
    "data.resource.labels.database_id"="gcp-appdcloudplatfo-nprd
-id:db_name"

PostgreSQL

<base_search>
| spath data.textPayload
| rex field=data.textPayload "^(?P<timestamp>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{6}Z)\s+(?P<user>\w+)\[\w+\]\s+@\s+\[(?P<ip>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\]\d+\s+\d+\s+(?P<operation_type>\w+)\s+(?P<sql_command>.*)$"
| where match(sql_command, "(?i)^(GRANT|REVOKE)\s")
| table _time, user, ip, operation_type, sql_command, data.textPayload

GRANT and REVOKE operations will be logged if the value of the “log_statement” flag is DDL. 

MySQL

<base search>
| spath data.textPayload 
| rename data.textPayload as textPayload 
| rex field=textPayload "db=(?<db_name>[^,]+),user=(?<db_user>[^,]+),host=(?<db_host>\S+)" 
| rex field=textPayload "duration:\s(?<duration>\d+\.\d+)\sms" 
| rex field=textPayload "statement:\s(?<sql_statement>.*)" 
| rex field=sql_statement "^(?<sql_operation>\w+)" 
| where match(sql_operation, "(?i)^(GRANT|REVOKE)")
| table _time, db_name, db_user, db_host, duration, sql_statement, sql_operation, textPayload

SQL Server

In order to get GRANT/REVOKE operations in SQL Server, you can either create a new table or use the existing AuditDB and DDLOperations tables previously created. I am going to modify the existing trigger as well so it captures everything for DDL operations and permission management.

USE AuditDB;

DROP TRIGGER trg_audit_ddl ON DATABASE;

CREATE TRIGGER trg_audit_ddl ON DATABASE FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, GRANT_DATABASE, REVOKE_DATABASE, GRANT_SCHEMA, REVOKE_SCHEMA AS BEGIN SET NOCOUNT ON; DECLARE @x XML = EVENTDATA(); INSERT dbo.DdlEvents (EventType, ObjectType, SchemaName, ObjectName, Tsql, LoginName, HostName, DatabaseName, PostTimeUtc, EventXml) SELECT @x.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'), @x.value('(/EVENT_INSTANCE/ObjectType)[1]', 'sysname'), @x.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'), @x.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'), @x.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'), @x.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'), HOST_NAME(), DB_NAME(), SYSUTCDATETIME(), @x; END;

Now modify your SPL to include GRANT/REVOKE.

<base search>
| rename ObjectName AS object_name, LoginName AS db_user, Tsql AS sql_statement, EventType AS event_type
| rex field=event_type "(?<sql_operation>GRANT|REVOKE)_"
| search sql_operation=*
| table _time, DatabaseName, SchemaName, object_name, db_user, sql_operation, sql_statement
| sort - _time

Next steps

Now that you have learned how to identify database connections in your GCP CloudSQL database, check out the other use cases available for this service: