Reputation: 153
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:
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
Upvotes: 3
Views: 1291
Reputation: 1
For MySQL 8 on GCP SQL you cannot use:
GRANT ALL ON `%`.*
Users will only be granted access to the database with the name %
.
This is because of the partial_revokes
system flag. You can read the full details here.
The options available are:
GRANT ALL ON db1.* TO 'testuser'@'%';
GRANT ALL ON db2.* TO 'testuser'@'%';
GRANT ALL ON db3.* TO 'testuser'@'%';
GRANT SELECT ON *.* TO 'testuser'@'%';
REVOKE SELECT ON test3_foobar.* FROM 'testuser'@'%';
Upvotes: 0
Reputation: 5829
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 thatGRANT ALL PRIVILEGES
statements will not work. As an alternative, you can useGRANT ALL ON
%.*
.
This alternative mentioned could be enough to grant the permissions you expected.
Upvotes: 1