CODEWITHSUNDEEP
mysqlgoogle-cloud-platform
Dhirendra Saw
Dhirendra Saw

Reputation: 153

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

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:

enter image description here

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

Upvotes: 3

Views: 1293

Answers (2)

ziyun
ziyun

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:

  1. Grant permissions to specific databases using the full database names
GRANT ALL ON db1.* TO 'testuser'@'%';
GRANT ALL ON db2.* TO 'testuser'@'%';
GRANT ALL ON db3.* TO 'testuser'@'%';
  1. Use the grant and revoke command to grant user privileges on all database schemas while restricting access to a few database schemas.
GRANT SELECT ON *.* TO 'testuser'@'%';
REVOKE SELECT ON test3_foobar.* FROM 'testuser'@'%';

Upvotes: 0

Rafael Lemos
Rafael Lemos

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 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.

Upvotes: 1

Related Questions