0

I am logged into MySQL with a user with full admin privileges, and wish to update the root user to only allow login via localhost.

Currently, the root user has full privileges via 'root'@'%', and I'd like to change that to 'root'@'localhost'. I've tested this out on a local mysql install and it worked fine. However, in GCP Cloud SQL I'm unable to do so.

Running:

RENAME USER 'root'@'%' TO 'root'@'localhost';

Yields the error:

ERROR 1221 (HY000): Incorrect usage of RENAME and SYSTEM USER

Is there any way to achieve what I want to do in locking down where the root user can login from? I would like to avoid any MySQL downtime if possible. I am using MySQL 5.7.

hightech
  • 33
  • 8
  • You need to create new user root@localhost with root privileges, log in and remove root@%. If you have any routines owned by root@% they need be updated too – Slava Rozhnev Nov 03 '20 at 21:57
  • Trying to create a new root user w/ the localhost host gives an error as well: ```ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'``` – hightech Nov 03 '20 at 22:33
  • Look this article: https://stackoverflow.com/questions/5555328/error-1396-hy000-operation-create-user-failed-for-jacklocalhost – Slava Rozhnev Nov 03 '20 at 23:23
  • Thanks, I had already found that article and nothing in there was enough to help me. I've reached out to GCP support, so hopefully will have some answers soon to post back here. – hightech Nov 04 '20 at 00:03

1 Answers1

1

As per the document, because Cloud SQL is a managed service, it restricts access to certain system procedures and tables that require advanced privileges and that includes restricting the hostname for the default root user account.

What I can suggest is that you create another MySQL user on cloud console. That user have the same privileges as the root user plus you'll be able to restrict the hostname or limit the privileges for this user. In a way you can say that Cloud SQL encourages you to create separate user accounts for different purposes because the root user is a very common target for unauthorized access.

Donnald Cucharo
  • 3,866
  • 1
  • 10
  • 17