0

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?

4
  • use 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.
    – danblack
    Commented Mar 19, 2020 at 22:56
  • Were you granted WITH GRANT OPTION?
    – Rick James
    Commented Apr 1, 2020 at 23:57
  • @Rick James I believe the admins at the ISP used the command GRANT ALL PRIVILEGES ON thedatabase name.* TO [email protected]. (where the numbers are my IP address). At least that's whet executing SHOW GRANTS shows me Commented Apr 3, 2020 at 8:41
  • @danblack SHOW GRANTS FOR [email protected] displays exactly the same data as just SHOW GRANTS , since I'm the only user. My comment back to Rick James shows the result Commented Apr 3, 2020 at 8:44

2 Answers 2

1

You need to grant CREATE USER using similar syntax:

grant create user on *.* to 'admin'@'%';
1
  • I'm not sure that's correct as that would grant admin access to every database on the server. Anyway, I get the error Access denied for user 'myusername@myip address (using password YES) even though I'm already logged in . I suspect this is an admin command only available to those who work for the ISP where the database server is Commented Mar 19, 2020 at 20:10
0

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;
2
  • Well, maybe the admins at the IPS do but I don't have any say on how they operate. Never mind, thanks for all the suggestions. (Maybe it would help SO users if questions and answers regarding database management specified whether they refer to someone hosting the DB on their own server with full admin rights or if it refers to a DB that's just part of a domain package hosted by an ISP company who inevitable give limited rights to their customers.) Commented Apr 6, 2020 at 14:38
  • If your grant does not have WITH GRANT OPTION, then you cannot GRANT anything to anyone.
    – Rick James
    Commented Apr 6, 2020 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.