CODEWITHSUNDEEP
mysql
Houman
Houman

Reputation: 66390

Allowing remote access in MySql 8.0 not allowed

A lot of the existing answers are targeting the old MySQL and I haven't been able to find a solution for the new MySQL 8.0.

mysql> SELECT user FROM mysql.user;
+------------------+
| user             |
+------------------+
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| r00t             |
| root             |
+------------------+

I have already a user called r00t.

When I login as root and try to give r00t remote permission:

GRANT ALL PRIVILEGES ON *.* TO r00t@'18.132.x.xxx';

ERROR 1410 (42000): You are not allowed to create a user with GRANT

But the user already exists, so what gives?

The old way doesn't work either:

GRANT ALL PRIVILEGES ON *.* TO 'r00t'@'18.132.x.xxx' IDENTIFIED WITH caching_sha2_password BY 'myPass';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED WITH caching_sha2_password BY 'myPass'' at line 1

Upvotes: 0

Views: 280

Answers (1)

RiggsFolly
RiggsFolly

Reputation: 94682

In MYSQL the users account is identified by the username and the domain i.e.

username        domain
`r00t`    and   '18.132.x.xxx'

So if you created a 'r00t'@'localhost' it is not the same as 'r00t'@'18.132.x.xxx'

So in fact you are trying to create a new account, or rather 'r00t'@'18.132.x.xxx' does not exist yet, hence the error.

So first you should create the account 'r00t'@'18.132.x.xxx'

Upvotes: 1

Related Questions