Permission to kill connections of other users

[julio-costa]

Hello.

We recently migrated our MySQL database from version 5.7 to 8.

After migrating, I started having a problem. When I try to end a connection from another user, I can't. Even though I'm using the root user, I get the following error: 'You are not owner of thread'.

Is there any way to solve this?

[ms4446]

In MySQL 8.0 changes were made to the privilege system, particularly affecting administrative tasks like terminating user connections. The error message "You are not owner of thread" occurs because the SUPER privilege, which previously allowed users to kill any connection, has been deprecated and its capabilities have been divided into more specific privileges such as CONNECTION_ADMIN. To terminate connections initiated by other users, you now need the CONNECTION_ADMIN privilege.

In Google Cloud SQL for MySQL, even the root user has limited privileges due to security and maintenance policies. Specifically, Cloud SQL restricts the CONNECTION_ADMIN privilege, preventing the root user from directly killing other users' connections. Since you cannot modify the root user's privileges in Cloud SQL, you need to use Google Cloud's tools to manage connections.

To resolve this issue, you can:

1. Use the Google Cloud Console:

   • Navigate to SQL > Instances in the Google Cloud Console.
   • Select your instance and go to the Connections tab.
   • Identify and terminate the desired connection.

2. Use the gcloud Command-Line Tool:

   gcloud sql connections list --instance=[INSTANCE_NAME]
   gcloud sql connections delete [CONNECTION_NAME] --instance=[INSTANCE_NAME]
   

   Replace [INSTANCE_NAME] with your instance name and [CONNECTION_NAME] with the connection you wish to terminate.

3. Use the Cloud SQL Admin API:

   • Utilize the instances.connections.delete method to programmatically terminate connections.

[julio-costa · Accepted solution]

Hello ms4446.

I found a slightly simpler solution in the GCP documentation itself: https://cloud.google.com/sql/docs/mysql/users?hl=en. It explains that if you create a user via the console, they are granted the CONNECTION_ADMIN permission, which is required to kill connections from other users.

Thank you for your response.