0

I'm attempting to grant a user privileges within a database, and I'm receiving the error

#1410 - You are not allowed to create a user with GRANT

I'm executing the following:

GRANT ALL PRIVILEGES ON  `database`.* TO 'user'@'localhost';

The user is already created, but I cannot grant privileges to it.

Below is a screenshot of a specific user Im attempting to edit.

Screenshot

Firav
  • 53
  • 5
  • According to mysql, the user does not exists, hence the error message. However, this question is not about programming, but about database administration, therefore it is off topic here on SO. The DBA sister site of SO provides help on database admin type of questions like this one. – Shadow Jun 18 '21 at 15:12
  • @Shadow Just making sure I understand... SQL is not code? Do you have a list of programming languages with annotation of whether they are code or not? – Bill Karwin Jun 18 '21 at 16:08
  • @BillKarwin this question is not about sql, this question is about user administration in a database instance, which is not about programming, but about database administration. Such questions have their own home. – Shadow Jun 18 '21 at 16:13
  • @Shadow The `GRANT` statement is part of the ANSI/ISO SQL language. https://crate.io/docs/sql-99/en/latest/chapters/15.html#grant-statement – Bill Karwin Jun 18 '21 at 16:55
  • @BillKarwin that's irrelevant. The question is not about programming, but about user administration. – Shadow Jun 18 '21 at 16:59
  • 1
    @Firav Can you try `SHOW GRANTS FOR 'user'@'localhost'`? If it says, "Error 1411: There is no such grant defined for user 'user' on host 'localhost'" then that user does not exist on this MySQL instance. Keep in mind that 'user'@'localhost' is different from 'user'@'%' or any other host/address. – Bill Karwin Jun 18 '21 at 17:03
  • Possible duplicate: https://stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0 – Bill Karwin Jun 18 '21 at 17:04
  • @BillKarwin It is indeed replying with Error 1411. Not sure why the user doesn't exist in that instance, even though the user is visible in the mysql.user table and I've used the ```CREATE USER``` command for it. I've also looked at the question you've mentioned, and I cant seem to ammend my issue with that solution. – Firav Jun 18 '21 at 17:10
  • I am not seeing what you see, so I can only guess. Double-check that you spelled the user and hostname correctly, double-check that you are viewing the same MySQL Server instance, etc. – Bill Karwin Jun 18 '21 at 17:20
  • @BillKarwin In this instance, Im attempting to modify the ```banmanager``` user. Heres a picture of the outputs of the user table and the attempted command. [Image](http://img.firav.us/images/2021/06/18/cmd_2021-06-18_13-21-57.png) – Firav Jun 18 '21 at 17:24
  • What does `select user, host from mysql.user where user='banmanager'` return? – Bill Karwin Jun 18 '21 at 17:26
  • @BillKarwin [Result](http://img.firav.us/images/2021/06/18/cmd_2021-06-18_13-28-08.png) – Firav Jun 18 '21 at 17:28
  • Okay I have no other idea, unless 'banmanager' has an extra space in it or something. – Bill Karwin Jun 18 '21 at 17:29
  • @BillKarwin Just checked to be sure. It is just 'banmanager'. Thanks for the time you've put forward! – Firav Jun 18 '21 at 17:31
  • @BillKarwin I've cleared out all of the users and created them all again. It must have been something to do with authentication settings/plugins. The accounts are working fine now. – Firav Jun 18 '21 at 19:24

1 Answers1

1

Conflicting authentication settings on users were preventing the user from registering properly. Deleted all affected users and created them again.

Firav
  • 53
  • 5