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?
SHOW GRANTS FOR {user}
to examine current grants. The structure of MySQL internal tables is changes so get used to using the SQL administration commands form.WITH GRANT OPTION
?GRANT ALL PRIVILEGES ON thedatabase name.* TO [email protected]
. (where the numbers are my IP address). At least that's whet executingSHOW GRANTS
shows meSHOW GRANTS FOR [email protected]
displays exactly the same data as justSHOW GRANTS
, since I'm the only user. My comment back to Rick James shows the result