[FIXED] Access denied to database that I have access to

Issue

I am trying to grant privileges to another user using phpmyadmin, I have access to the root user (cl43-flexfit) and have tried querying the following

GRANT ALL PRIVILEGES ON `cl43-flexfit`.* TO 'supuser'@'localhost';

But receive a response of:

Access denied for user 'cl43-flexfit'@'%' to database 'cl43-flexfit'

Although I use that database with the cl43-flexfit user frequently.

I have also looked at what the root users privileges are using SHOW GRANT

and was shown these:

GRANT USAGE ON *.* TO 'cl43-flexfit'@'%' IDENTIFIED BY PASSWORD 'password'

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `cl43-flexfit`.* TO 'cl43-flexfit'@'%' WITH GRANT OPTION

and even when I try to add permissions to the user for every database (replacing cl43-flexfit.* with * .*) I get an error saying I do not have permission

Access denied for user 'cl43-flexfit'@'%' (using password: YES)

I have been in contact with my hosting service and they have said that everything is correct on their end.

I also do not have access to the privileges tab in PHPMyAdmin and therefore can not use the GUI, it must be done through written commands.

Thanks in advance and apologise if I have a lack of understanding

Solution

You cannot GRANT ALL unless you also hold all privileges, along with GRANT OPTION, which you do not.

You have to grant explictly, and list only the permissions that you have (and want to grant).

You can't grant anything ON *.* unless you globally hold the privilege you are trying to grant, on all objects, plus GRANT OPTION. Again, you don't have this.

USAGE means only that you are allowed to log in to the server, nothing more. This is a special case of ON *.* carrying no significant meaning, because merely logging into the server is associated with no particular object.

The hosting service is correct.

If you have other users, you can make only explicit grants of listed permissions, using the format shown in your own SHOW GRANTS output.

GRANT SELECT, INSERT, [more...], TRIGGER ON `cl43-flexfit`.* TO 'my-other-existing-user'@'%';

Answered By - Michael - sqlbot