Can't update mysql users authentication plugin in Google Cloud SQL because root does not have SYSTEM_USER permission
1
vote
2
answers
45
views
I'm working on upgrading my Google Cloud SQL mysql instance from 8.0 to 8.4. I just upgraded it from 5.7 to 8.0 and now I'm trying to convert my user authentication plugins for my existing users from
mysql_native_password
to caching_sha2_password
so that I can take the next step of upgrading from 8.0 to 8.4.
When I login as root
, I'm not able to alter the various user's plugins and it gives me the error:
ERROR: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
I can't see any way to grant my root user that permission. I've got this list of users and I don't know how to update them before I perform the 8.0 -> 8.4 upgrade. I'm worried that I'll break a bunch of stuff if I don't update them before the upgrade to 8.4.
mysql> SELECT user, host, plugin FROM mysql.user WHERE plugin = 'mysql_native_password' order by user, host;
+----------------------------+-----------+-----------------------+
| user | host | plugin |
+----------------------------+-----------+-----------------------+
| cloudiamgroup | % | mysql_native_password |
| cloudsqlapplier | localhost | mysql_native_password |
| cloudsqlexport | 127.0.0.1 | mysql_native_password |
| cloudsqlexport | ::1 | mysql_native_password |
| cloudsqlimport | 127.0.0.1 | mysql_native_password |
| cloudsqlimport | ::1 | mysql_native_password |
| cloudsqlimport | localhost | mysql_native_password |
| cloudsqlinactiveuser | % | mysql_native_password |
| cloudsqlobservabilityadmin | 127.0.0.1 | mysql_native_password |
| cloudsqlobservabilityadmin | ::1 | mysql_native_password |
| cloudsqlobservabilityadmin | localhost | mysql_native_password |
| cloudsqloneshot | 127.0.0.1 | mysql_native_password |
| cloudsqloneshot | ::1 | mysql_native_password |
| cloudsqlreadonly | 127.0.0.1 | mysql_native_password |
| cloudsqlreadonly | ::1 | mysql_native_password |
| cloudsqlreadonly | localhost | mysql_native_password |
| cloudsqlreplica | % | mysql_native_password |
| cloudsqlsuperuser | % | mysql_native_password |
| mysql.sys | localhost | mysql_native_password |
| root | 127.0.0.1 | mysql_native_password |
| root | ::1 | mysql_native_password |
| root | localhost | mysql_native_password |
+----------------------------+-----------+-----------------------+
22 rows in set (0.06 sec)
mysql>
How do I update these user entries so that I can safely upgrade to mysql 8.4 in my Google Cloud SQL instance?
Asked by Kenny Wyland
(129 rep)
Jun 27, 2025, 09:58 PM
Last activity: Jun 30, 2025, 07:55 PM
Last activity: Jun 30, 2025, 07:55 PM