How to give all privileges to a new user that I created 'user@%'

Question

I switch to MySQL 8 recently; earlier I was using MySQL 5.7 in GCP. I know questions like this have already been asked, but I didn't have any luck. My question is, I want to create a new user just say 'user1' and grant all privileges to a user account on all databases.

The query I am using for user creation:

CREATE USER 'user-1'@'%' IDENTIFIED BY 'user_password';

For privileges:

GRANT ALL PRIVILEGES ON *.* TO 'user-1'@'%';

This privileges query used to work on MySQL 5.7, but when I try to run this query in MySQL 8, I get this error (I logged in as root user and MySQL is in GCP):

SQL Error (1045): Access denied for user 'root'@'%' (using password: YES)

I also tried to run this query one after another like this:

CREATE USER 'user-1'@'%' IDENTIFIED BY 'user_password';


GRANT ALL PRIVILEGES ON *.* TO 'user-1'@'%';

But still I get the same error. I came to know that in MySQL 8 localhost root user have all the privileges, but my server is in Google Cloud, so how can I grant all the privileges to the user I created?

When I run this query in the mysql-8 (I am using HeidiSQL to connect the DB and run query)

SELECT * FROM mysql.user;

I got this output:

In this there are two root users:

1. For one host is localhost/127.0.0.1 (With all the privilege).
2. For other host is % (Not have any privilege).

I think I logged in as a user with host-% because my server is in GCP, that's why I cannot give any privilege to the user that I have created. So is there any way to give full permission to the

root@%

so that I can give full permission to the other users, Because I don't think there is any way to log in as a root@localhost

Answer 1

The problem here is that you are trying to create a super user, which is not something supported in cloud SQL, as you can see in this documentation:

Cloud SQL does not support SUPER privileges, which means that GRANT ALL PRIVILEGES statements will not work. As an alternative, you can use GRANT ALL ON %.*.

This alternative mentioned could be enough to grant the permissions you expected.