Use MySQL database auditing

MySQL  |  PostgreSQL  |  SQL Server

This topic describes how to enable and use the Cloud SQL for MySQL Audit Plugin.For an overview, seeMySQL database auditing. Formore information about MySQL plugins, seeMySQL plugin loading.

Note: In this topic, Administrators and Auditors are referred to as audit users.

Before you begin

Data Access audit logs

Enable and configure Data Access audit logs. SeeConfigure Data Access audit logs.

Privileges required for audit users

You must haveEXECUTE privileges on the audit stored procedures torun them. If an administrator needs to change an auditor's access privileges tothe audit stored procedures, they should use theGRANT orREVOKE command inthemysql client. For details about the user privileges, refer toMySQL user privileges. For example, if an administrator wanted to grant access to an auditor nameduser, to manage theaudit rules, they could grant the privileges using the following statement:

CREATEUSER'user'@'%'IDENTIFIEDBY'password';GRANTEXECUTEONPROCEDUREmysql.cloudsql_list_audit_ruleTO'user'@'%';

If that administrator later wanted to revoke access to the auditor, they couldrevoke the privileges using the following statement:

REVOKEALLONPROCEDUREmysql.cloudql_list_audit_ruleFROM'user'@'%';

By default,users created using Cloud SQL(except IAM users) are administrators who have all privileges exceptFILE andSUPER, including all privileges to the audit stored procedures. IAM databaseusers, by default, have no privileges.

Enable the Cloud SQL for MySQL Audit Plugin

The Cloud SQL for MySQL Audit Plugin (cloudsql_mysql_audit) controls theauditing behavior for a given database instance. To use the plugin, you mustfirst enable it on the Cloud SQL instance.

To enable thecloudsql_mysql_audit plugin, use one of the following options:

  • --cloudsql_mysql_audit=ON

    Tells the server to enable the plugin. If the plugin fails to initialize, the server runs with the plugin disabled.

  • --cloudsql_mysql_audit=FORCE

    Tells the server to enable the plugin, but if plugin initialization fails, the server does not start. In other words, this option forces the server to run with the plugin enabled or not at all.

  • --cloudsql_mysql_audit=FORCE_PLUS_PERMANENT

    Like FORCE, but also prevents the plugin from being unloaded at runtime. If a user tries to unload the plugin withUNINSTALL PLUGIN, an error occurs.

Plugin activation states are visible in thePLUGIN_STATUS columnof theINFORMATION_SCHEMA.PLUGINStable.

Note: Changing the value of this audit flag requires a MySQL serverRESTART each time.Note: Enabling logs might result in Cloud Logging charges for the additional logs usage.

Console

  1. In the Google Cloud console, go to theCloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Open the instance and clickEdit.
  3. Scroll down to theFlags section.
  4. To set a flag that has not been set on the instance before, clickAdd flag, choosecloudsql_mysql_audit from the drop-down menu, and set its value toON.
  5. ClickSave to save your changes.
  6. Confirm your changes underFlags on theOverview page.

gcloud

Replace the following:

  • INSTANCE_NAME: The name of the instance you are setting the flag on.
gcloudsqlinstancespatchINSTANCE_NAME\--database-flagscloudsql_mysql_audit=ON

This command overwrites all database flags previously set. To keep those and add new ones, include the values for all flags you want set on the instance; any flag not specifically included is set to its default value. For flags that do not take a value, specify the flag name followed by an equals sign (=).

REST v1beta4

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body:

{  "settings":  {    "databaseFlags":    [      {        "name": "cloudsql_mysql_audit",        "value": "ON"      }    ]  }}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id"

PowerShell (Windows)

Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2020-01-21T22:43:37.981Z",  "operationType": "UPDATE",  "name": "operation-id",  "targetId": "instance-id",  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",  "targetProject": "project-id"}

REST v1

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/sql/v1/projects/project-id/instances/instance-id

Request JSON body:

{  "settings":  {    "databaseFlags":    [      {        "name": "cloudsql_mysql_audit",        "value": "ON"      }    ]  }}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1/projects/project-id/instances/instance-id"

PowerShell (Windows)

Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2020-01-21T22:43:37.981Z",  "operationType": "UPDATE",  "name": "operation-id",  "targetId": "instance-id",  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",  "targetProject": "project-id"}

Cloud SQL for MySQL Audit Plugin settings

You can tune the behavior of the Cloud SQL for MySQL Audit Plugin using the flagsbelow. All flags can be altered without restarting the database. To understandhow to manage the flags, seeConfiguring database flags.

  • cloudsql_mysql_audit_data_masking_regex

    A regular expression used for data masking that is compliant withPerlCompatible Regular Expressions (PCREs).

    By default, in the output audit log, user passwords(<psw>) are substituted by a password mask of***.

    Regex is applied only to statements with a command type specified usingcloudsql_mysql_audit_data_masking_cmds. When usinggcloud you must use a flag file to set values with complexspecial characters.

    Default values for this flag:

    identified(?:/\*.*?\*/|\s)*?by(?:/\*.*?\*/|\s)*?(?:password)?(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?\((?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"](?:/\*.*?\*/|\s)*?\)|password(?:/\*.*?\*/|\s)*?(?:for(?:/\*.*?\*/|\s)*?\S+?)?(?:/\*.*?\*/|\s)*?=(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]

    See the following examples.

    • Without thecloudsql_mysql_audit_data_masking_regex, anaudit log entry describing a create user command would appear as follows:

      {..."cmd":"create_user","query":"create user user1@'localhost' identifiedby 'pw'"}

    • With the default value ofcloudsql_mysql_audit_data_masking_regex,the same audit log would be changed to:

      {..."cmd":"create_user","query":"create user user1@'localhost' identified by'***'"}

    • If you setcloudsql_mysql_audit_data_masking_regex to(?<psw>.*), Cloud SQL can filter all the query contents.

      {..."cmd":"create_user","query":"***"}

  • cloudsql_mysql_audit_data_masking_cmds

    Comma-separated list of commands that the data masking regex(cloudsql_mysql_audit_data_masking_regex) is applied to. Set an emptystring ("")to stop filtering. When usinggcloud,you must use a flag file to set a comma-separated list. The default valueincludes MySQL commands with the following password clause:

    create_user,alter_user,grant,update

  • cloudsql_mysql_audit_max_query_length

    Controls the maximum length of the query to record in the audit log. If youdon't need to see the details of the query, you can use0,which means the query is not recorded in the audit log. This saves storagespace for the log, resulting in lower cost.-1 means nolimitation. Default is-1.

  • cloudsql_mysql_audit_log_write_period

    The log write period, for which the writer thread writes the contents ofthe buffer to disk after reaching the number of milliseconds you set forthis flag option, or when the buffer is full. If you set this flag optionto0, the user thread is forced to wait for a notificationfrom the writer thread that the write has been completed. The default is500 (milliseconds).

Console

  1. In the Google Cloud console, go to theCloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Open the instance and clickEdit.
  3. Scroll down to theFlags section.
  4. To set a flag that has not been set on the instance before, clickAdd flag, and choose a database flag from the list above from the drop-down menu, and set its value.
  5. ClickSave to save your changes.
  6. Confirm your changes underFlags on theOverview page.

gcloud

Replace the following:

  • INSTANCE_NAME: The name of the instance you are setting the flag on.
  • FLAG_NAME: The name of the configuration flag.
  • FLAG_VALUE: The value to use for the flag.

gcloudsqlinstancespatchINSTANCE_NAME/--database-flagsFLAG_NAME=FLAG_VALUE

REST v1beta4

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body:

{  "settings":  {    "databaseFlags":    [      {        "name": "flag_name",        "value": "flag_value"      }    ]  }}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id"

PowerShell (Windows)

Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2020-01-21T22:43:37.981Z",  "operationType": "UPDATE",  "name": "operation-id",  "targetId": "instance-id",  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",  "targetProject": "project-id"}

REST v1

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id

Request JSON body:

{  "settings":  {    "databaseFlags":    [      {        "name": "flag_name",        "value": "flag_value"      }    ]  }}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id"

PowerShell (Windows)

Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2020-01-21T22:43:37.981Z",  "operationType": "UPDATE",  "name": "operation-id",  "targetId": "instance-id",  "selfLink": "https://sqladmin.googleapis.com/v1/projects/project-id/operations/operation-id",  "targetProject": "project-id"}

Configure database auditing

Manage audit rules

Cloud SQL uses a set of stored procedures to manage Cloud SQL for MySQL AuditPlugin audit rules. There are four stored procedures that you can use to create,list, update, and delete audit rules.

Each stored procedure returns two variables:@outval and@outmsg. Thesevariables indicate the status code and the error message of the storedprocedure, respectively. You can use an asterisk (*) as a wildcard to searchfor user, host, database, and table names. Use the asterisk as a suffix, aprefix, or both. In addition, you can use the wildcard character% only forhosts. You can use the backtick (`) to indicate that a stringshould be used literally. For example,`ta*ble` matchesliterally.

To make the new changes take effect, you should either run the stored procedureswith the input parameterreload_mode=1 or callmysql.cloudsql_reload_audit_rule(1) to make the new changes take effect.

Note: Audit rules are changeable, even when thecloudsql_mysql_audit plugin is disabled on the primary instance. However, database activity doesn't generate audit logs unless the Cloud SQL for MySQL Audit Plugin is enabled.

Create audit rules

You can usemysql.cloudsql_create_audit_rule to create a new audit rule.

To create the audit rule and reload it in one call, use the following statement:

CALLmysql.cloudsql_create_audit_rule('user@host','db','obj','ops','op_result',1,@outval,@outmsg);SELECT@outval,@outmsg;

To create the rule and reload it in a separate call, use the followingstatement:

CALLmysql.cloudsql_create_audit_rule('user@host','db','obj','ops','op_result',0,@outval,@outmsg);SELECT@outval,@outmsg;CALLmysql.cloudsql_reload_audit_rule(1);

The previous command lets you create multiple rules and then reload all changesat the same time.

The following tables show the input and output parameters for the previouscommand.

Input parameters
NameTypeDescriptionExample
user@host oruser@ipstring The comma-separated list of database users to audit. Use the formatuser@host oruser@ip.

Note: Currently the plugin only supports filters by the host name, not the IP address.		user1@localhost,
user1@*,
user1@%,
user@ip
dbstringThe comma-separated list of databases to audit.db1,db2,db3*
objstringThe comma-separated list of database objects to audit.table1,table2,table3*
opsstringThe comma-separated list of database actions to audit.select,delete,insert
op_resultstringAudit success (S), unsuccessful (U) or both (B) successful and unsuccessful operations.S,U, orB
reload_modeInteger0 for not reload the rule and1 for reload.0 or1
Output parameters
NameTypeDescriptionExample
@outvalintThe status code of the stored procedure.0 for success and1 for failure.
@outmsgstringThe error message of the stored procedure.

Audit rules have the following limitations:

Limitations
Length ofuser,db,obj, andops.Maximum size is 2048 characters.
Number of combinations ofuser,db,obj ,andops.Maximum of 1000 combinations. For example, an audit rule auditinguser1,user2,db1, db2,table1,table2, andselect,delete generates 2 x 2 x 2 x 2 = 16 combinations.

List audit rules

Auditors can usemysql.cloudsql_list_audit_rule to list existing auditrules.

To list audit rule 1 and 2, use the following commands:

CALLmysql.cloudsql_list_audit_rule('1,2',@outval,@outmsg);SELECT@outval,@outmsg;

To list all audit rules, do the following:

CALLmysql.cloudsql_list_audit_rule('*',@outval,@outmsg);SELECT@outval,@outmsg;

The following tables show the input and output parameters for the previouscommand.

Input parameters
NameTypeDescriptionExample
rule_idstringThe comma-separated list of rule IDs to remove.1,2,3
Output parameters
NameTypeDescriptionExample
@outvalintThe status code of the stored procedure.0 for success and1 for failure.
@outmsgstringThe error message of the stored procedure.

Update audit rules

You can usemysql.cloudsql_update_audit_rule to update an existingaudit rule.

To update the audit rule and reload it in one call, use the following statement:

CALLmysql.cloudsql_update_audit_rule(1,'user@host','db','obj','ops','op_result',1,@outval,@outmsg);SELECT@outval,@outmsg;

You might want to update multiple audit rules in one session before reloadingthe rules. You can use the following stored procedure to update rules in onestep and then reload those rules in a later step.

CALLmysql.cloudsql_update_audit_rule(1,'user@host','db','obj','ops','op_result',0,@outval,@outmsg);SELECT@outval,@outmsg;CALLmysql.cloudsql_reload_audit_rule(1);

The following tables show the input and output parameters for the previouscommand.

Input parameters
NameTypeDescriptionExample
rule_idintThe ID for the rule to update.5
user@hoststring The comma-separated list of database users to audit. Use the format user@host.

Note: Currently the plugin only supports filters by the host name, not the IP address.		user1@localhost,user1@*
user1@%
dbstringThe comma-separated list of databases to audit.db1,db2,db3*
objstringThe comma-separated list of database objects to audit.table1,table2,table3*
opsstringThe comma-separated list of database actions to audit.SELECT,DELETE,INSERT
op_resultstringAudit success (S), unsuccessful (U), or both (B) successful and unsuccessful operations.S,U, orB
reload_modeInteger0 for not reload the rule and1 for reload.0 or1
Output parameters
NameTypeDescriptionExample
@outvalintThe status code of the stored procedure.0 for success and1 for failure.
@outmsgstringThe error message of the stored procedure.

Audit rules have the following limitations:

Limitations
Length ofuser,db,obj andops.Maximum size 2048 characters.
Number of combinations ofuser,db,obj andops.Maximum of 1000 combinations. For example, an audit rule auditinguser1,user2,db1, db2,table1,table2, andselect,delete generates 2 x 2 x 2 x 2 = 16 combinations.

Delete audit rules

You can use themysql.cloudsql_delete_audit_rule to deletean existing audit rule.

To delete the audit rule and reload it in one call, use the following statement:

CALLmysql.cloudsql_delete_audit_rule('1,2',1,@outval,@outmsg);SELECT@outval,@outmsg;

To delete the rule and reload it in a separate call, use the followingstatement:

CALLmysql.cloudsql_delete_audit_rule('1,2',0,@outval,@outmsg);SELECT@outval,@outmsg;CALLcloudsql_reload_audit_rule(1);

The previous command lets you delete multiple rules and then reload all changesat the same time.

The following tables show the input and output parameters for the previouscommand.

Input parameters
NameTypeDescriptionExample
rule_idstringThe comma-separated list of rule IDs to remove.1,2,3
reload_modeInteger0 for not reload the rule and1 for reload.0 or1
Output parameters
NameTypeDescriptionExample
@outvalintThe status code of the stored procedure.0 for success and1 for failure.
@outmsgstringThe error message of the stored procedure.

Operation groups

The Cloud SQL for MySQL Audit Plugin supports using operation groups in ruledefinitions to audit collections of activities. You can use the followingoperation groups to simplify audit rule creation.

Operation groupsOperations included
dqlselect
dmldelete,delete_multi,insert,insert_select,load,replace,replace_select,truncate,update,update_multi,
ddlalter_db,alter_event,alter_function,alter_procedure,alter_table,alter_user,create_db,create_event,create_function,create_index,create_procedure,create_table,create_trigger,create_user,create_udf,create_view,drop_db,drop_event,drop_function,drop_index,drop_procedure,drop_table,drop_trigger,drop_user,drop_view,rename_table,rename_user
dclgrant,revoke,revoke_all
showshow_binlog_events,show_create_func,show_create_proc,show_procedure_code,show_create_event,show_create_trigger,show_events,show_function_code,show_grants,show_relaylog_events,show_triggers,
callcall_procedure

View Cloud SQL database audit logs

View database audit log records in Cloud Logging

To view database audit logs, first make sure you'veenabled DataAccess audit logs for your project. The generated MySQL audit logs for a giveninstance are sent to Cloud Logging asData Access audit logs.You can view the generated MySQL database audit logs through theLogs Explorer application.

In Logs Explorer, you can view MySQL audit logs by using the following query toshow all MySQL database audit logs for a given Cloud SQL project through theAdvanced Filter interface.

Replace the following:

  • PROJECT_NAME: The name of the project you want audit logs for.
resource.type="cloudsql_database"logName="projects/PROJECT_NAME/logs/cloudaudit.googleapis.com%2Fdata_access"protoPayload.request.@type="type.googleapis.com/google.cloud.sql.audit.v1.MysqlAuditEntry"

Alternatively, you can select thecloudaudit.googleapis.com/data_access logfilter.

Audit Log Format

An audit log has the following fields.

Field nameDescription
msgTypeA string to represent the audit log message type.The only value formsgType isactivity.
statusStatus of the operation, eithersuccess orunsuccessful.
dateA timestamp indicating when the audit event was generated.
threadIdThe ID of the MySQL thread.
queryIdThe ID of the MySQL query.
userA string representing the username sent by the client. This might differ from the privUser value.
privUserA string representing the user that the server authenticated the client as. This is the username that the server uses for privilege checking. It might differ from the user value.
gcpIamAccountA string representing the GCP IAM account or service account.
ipA string representing the client IP address.
hostA string representing the client host name.
errCodeThe MySQL error code for a unsuccessful operation. See the MySQL Server Error Message Reference.
cmdA string that indicates the SQL statement (operation type) to use. For example,INSERT,UPDATE, orDELETE.
objects The audit objects. Normally a table. This field includes the following information about the object:

dbA string representing the default database name.
nameA string representing the object name. Normally the table name.
objTypeA string representing the type of the object. NormallyTABLE.
queryAn SQL statement (executed directly).
chunkCountTotal number of chunks if the audit log query size is more than thecloudsql_mysql_audit_event_split_threshold (90k).
chunkIndexIndex of the chunk.chunk_index starts at 1.
@typeType is alwaystype.googleapis.com/google.cloud.sql.audit.v1.MysqlAuditEntry.

The following is an example audit log entry.

{"msgType":"activity","status":"unsuccessful","date":"2021-11-11T06:16:16.163603Z","threadId":"750","queryId":"26763","user":"root","priv_user":"root","ip":"","host":"localhost","errCode":"1146","cmd":"select","objects":[{"db":"test","name":"t4","obj_type":"TABLE"}],"query":"select * from test.t4","chunkCount":2,"chunkIndex":2,"@type":"type.googleapis.com/google.cloud.sql.audit.v1.MysqlAuditEntry"}

Disable database auditing

To disable the Cloud SQL for MySQL Audit Plugin, you can set the database flagcloudsql_mysql_audit to OFF or remove the flag.

Note: This operation requires you to restart the instance.

Console

  1. In the Google Cloud console, go to theCloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Open the instance and clickEdit.
  3. Scroll down to theFlags section.
  4. To set a flag that has not been set on the instance before, clickAdd flag, choosecloudsql_mysql_audit from the drop-down menu, and set its value toOFF.
  5. ClickSave to save your changes.
  6. Confirm your changes underFlags on theOverview page.

gcloud

Replace the following:

  • INSTANCE_NAME: The name of the instance you are setting the flag on.

gcloudsqlinstancespatchINSTANCE_NAME\--database-flagscloudsql_mysql_audit=OFF
Note: This operation requires you to restart the instance.

REST v1beta4

Before using any of the request data, make the following replacements:

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body:

{  "settings":  {    "databaseFlags":    [      {        "name": "cloudsql_mysql_audit",        "value": "OFF"      }    ]  }}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id"

PowerShell (Windows)

Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2020-01-21T22:43:37.981Z",  "operationType": "UPDATE",  "name": "operation-id",  "targetId": "instance-id",  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",  "targetProject": "project-id"}

REST v1

Before using any of the request data, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/sql/v1/projects/project-id/instances/instance-id

Request JSON body:

{  "settings":  {    "databaseFlags":    [      {        "name": "cloudsql_mysql_audit",        "value": "OFF"      }    ]  }}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.json, and execute the following command:

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1/projects/project-id/instances/instance-id"

PowerShell (Windows)

Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2020-01-21T22:43:37.981Z",  "operationType": "UPDATE",  "name": "operation-id",  "targetId": "instance-id",  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",  "targetProject": "project-id"}

Examples of Audit Rules

  1. Audit all activities for all the users:

    CALLmysql.cloudsql_create_audit_rule('*','*','*','*','B',1,@outval,@outmsg);

  2. Audit all activities for a single useruser1:

    CALLmysql.cloudsql_create_audit_rule('user1@*','*','*','*','B',1,@outval,@outmsg);

  3. Audit all activities for all users that start withuser:

    CALLmysql.cloudsql_create_audit_rule('user*@*','*','*','*','B',1,@outval,@outmsg);

  4. Audit all DML operations for all the users:

    CALLmysql.cloudsql_create_audit_rule('*','*','*','dml','B',1,@outval,@outmsg);

  5. Audit select operation for a special databasedb1:

    CALLmysql.cloudsql_create_audit_rule('*','db1','*','select','B',1,@outval,@outmsg);

  6. Audit all activities for a database with special characterdb~1:

    CALLmysql.cloudsql_create_audit_rule('*','`db~1`','*','*','B',1,@outval,@outmsg);

  7. Audit select and delete operations for tabledb1.table1:

    CALLmysql.cloudsql_create_audit_rule('*','db1','table1','select,delete','B',1,@outval,@outmsg);

  8. Audit all successful operations:

    CALLmysql.cloudsql_create_audit_rule('*','*','*','*','S',1,@outval,@outmsg);

  9. Do not audit all activities for all users that start withuser:

    CALLmysql.cloudsql_create_audit_rule('user*@*','*','*','*','E',1,@outval,@outmsg);

Troubleshoot

IssueTroubleshooting
I can't seecloudsql_mysql_audit when I invoke the command:

SHOW (global) variables		cloudsql_mysql_audit is the name of the plugin. To check if it is active, use the following command:

SHOW PLUGINS

And check thestatus entry.
Why can't I see mysql audit logs after enablingcloudsql_mysql_audit? You must enable Data Access audit logs (as described inConfigure Data Access audit logs. Also, audit rules are needed for the Cloud SQL for MySQL Audit Plugin to audit wanted audit logs. Use stored procedures with `reload_mode=1` to create audit rules, or run the following command or restart the database to make the newly added audit rules work:

CALL mysql.cloudsql_reload_audit_rule(1)
I see the following error when updating the database flag:

Error 1193: Unknown system variable 'cloudsql_mysql_audit_xxx'		cloudsql_mysql_audit_xxx flags only work when the Audit Plugin is active. Remove any existingcloudsql_mysql_audit_xxx flags from the instance, and then turn on the plugin using the following command before updatingcloudsql_mysql_audit_xxx flags:

cloudsql_mysql_audit=ON
I can see audit logs generated even though I didn't set any audit rules.Changes to the audit rule tables (mysql.audit_log_rules andmysql.audit_log_rules_expanded) and audit stored procedures (mysql.cloudsql_xxxx_audit_rule) are logged by default.
After I made changes on the primary instance, I can't see the audit logs on my replica instances.Replication threads and crash recovery threads aren't logged. Cloud SQL audits activities on the primary instance but not on the replica instance.
I tried to use the following command to set values from a comma-separated list, but it doesn't work.

gcloud instances patch --database-flags		 If specifying a comma-separated list of flag values usinggcloud commands, use the--flags-file argument. First, you need to create a file with the following information:

--database-flags:general_log: OFFcloudsql_mysql_audit_max_query_length: '20'cloudsql_mysql_audit_data_masking_cmds: select,update

Then run the following command:

gcloud sql instances patch --flags-file=flagfile
The following command returns an error:

CALL mysql.cloudsql_create_canonical_rules		This is intended.mysql.cloudsql_create_canonical_rules should only be called internally bymysql.cloudsql_create_audit_ruleandmysql.cloudsql_update_audit_rule.
Which operations can I audit? The supported operations are listed in theFull list of supported operations. Some operations, such as the following, aren't audited since they're not affecting the database:

USE db_name


or

SHOW VARIABLES


Also, in some cases, the functions can't be audited as an object (non-supported features).
I want to audit all operations on a specific table, so I created the following audit rule:

mysql.cloudsql_create_audit_rule("user@*","db", "table","*","B",1,@1,@2);
But I can see audit logs that aren't related to this table, such asdisconnect andconnect.		Some operations, such as disconnect or connect, are considered global. They ignore the inputdb andobject fields.
When I create, update, or delete an audit rule using stored procedures, I see the following error.

The MySQL server is running with theread-only option so it can't execute this statement.		 Cloud SQL can't change stored procedures on a read-only instance. If the instance is primary, remove theread_only flags. If the instance is a replica instance, make the changes on its primary instance. After the changes of the rules are replicated to the replica instance, on the replica instance, run the following command to reload the rules into the replica:
CALL mysql.cloudsql_reload_audit_rule(1)
When I create, update, or delete an audit rule, I see the following error, even though the change was successful.

0 rows affected		 The0 rows affected response is for the last statement executed in the stored procedure, not the tables. To see if the audit rule is changed, use the following command:

mysql.cloudsql_list_audit_rule
I can't setcloudsql_mysql_audit_data_masking_cmds andcloudsql_mysql_audit_data_masking_regexp withgcloud.gcloud requires using the--flags-file argument to set complex flag values (the flags that include special characters).
I created a stored procedure using theCREATE USER statement, but the password isn't masked. By default, masking only works forcmds (operations), such as the following:

CREATE_USER,ALTER_USER,GRANT,UPDATE.

To filter the password while creating the stored procedure, addcreate_procedure to thecloudsql_mysql_audit_data_masking_cmds.
I got a lock wait timeout exceeded error when trying to create, update, or delete audit rules.Normally, this error is caused when two or more sessions try to modify the audit rules at the same time. If this happens frequently, increase the value of theinnodb_lock_wait_timeout variable. It can be increased either globally (using the database flag) or just for the session, for example, using the following command:
SETinnodb_lock_wait_timeout=120
.

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-11-24 UTC.