I had no problems making a test MySQL server and applying permissions a week ago with their MySQL 5.7, but trying out the new 8.0 version, I can't seem to alter permissions as easily, if at all. I'm logged in as root remotely, but a local cloud shell instance does the same.
I'm trying to create a new user that only has access to one database, but I can't seem to get past the part where I revoke all default permissions first.
My method:
CREATE USER 'test_user'@'%' IDENTIFIED BY '{password}';
Gives no error. (I've also tried creating a user through GCP's admin panel)
SHOW GRANTS FOR 'test_user'@'%'; returns GRANT USAGE ON *.* TO `test_user`@`%` (I assume this means the new user has full permissions?)
Then, trying to remove all privileges to start fresh with the user,
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'test_user'@'%';
shows:
Error: UNKNOWN_CODE_PLEASE_REPORT: Access denied for AuthId `root`@`%` to database 'mysql'.
I'm pretty new to Mysql, so I'm unsure if this is my fault or just a bug. Thank you!
First, I would like to point out that GRANT USAGE ON *.* TO test_user@% means the inverse, it means that the user has no privileges ! ( more info: Mysql Reference )
Secondly, I think that's what causing the error is the ALL PRIVILEGES keyword, it may have been removed in v8.0, so just go straight after user creation and grant him the privileges that you want on a table/database.