Why can't I create a MySQL user even if i have 'all priviliges'?

Question

I am trying to resolve issues created when my ISP moved my database from a shared server to dedicated cloud storage. I am reaching it remotely via SQLyog. They say they have given all privileges to all users (I'm the only user)

Issuing the sql

SHOW GRANTS

returns

GRANT ALL PRIVILEGES ON `my_database_name`.* TO 'myusername'@'my ip address'

but if I try to create a user for my database using command such as

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

or

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

I get the error

Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

and if I try to see exactly what privileges I have using

SELECT USER FROM mysql.db WHERE db = 'my_database_name';

I get the error

SELECT command denied to user 'myusername'@'my ip address' for table 'db'

I can't select from several other system tables either, eg user

Should I be able to do these things if I really have 'ALL privileges'?

How can I list exactly which privileges I have instead of just getting 'ALL' ?

Can anyone suggest a way I can try to track down what is wrong?

BTW I don't know if they executed flush privileges after doing the grant or not. would this have made a difference given that SHOW GRANTS appears to show that I have all privileges?

Answer 1

You need to grant CREATE USER using similar syntax:

grant create user on *.* to 'admin'@'%';

Answer 2

ALL PRIVILEGES does not include the GRANT OPTION.

You needed something like

CREATE USER admin@localhost
GRANT ALL PRIVILEGES ON *.* TO admin@localhost WITH GRANT OPTION;