2

Trying to grant privileges to an existing database user using

GRANT SELECT, INSERT, UPDATE ON myschema.* TO 'OptimusPrime'@'cybertron';

But I keep getting

1410, 'You are not allowed to create a user with GRANT'

I'm stumped since I'm certain that user OptimusPrime exists in the database (!). I verified it using

select * from mysql.user;

and indeed, OptimusPrime is there. I'm stumped. Anyone as a clue on why I keep getting this? I'm not trying to create a new user

EDIT: I'm using the root user

EDIT2: Tried doing that to root user. Getting same error. I'll try to reinstall the server. Something is fishy. I'll update once I've done that

Ruslan
  • 911
  • 2
  • 11
  • 28
  • check your current user, maybe your user doesn't have access to grant other users permissions, are using a privileged user? – ROOT Jan 11 '20 at 15:59
  • I'm using root user. The user has created the entire db schema just before trying this statement – Ruslan Jan 11 '20 at 16:03
  • You say OptimusPrime is there, but is that OptimusPrime@cybertron? Actually show us the results of that query so that we can help you. – Lightness Races in Orbit Jan 11 '20 at 16:10
  • check this, https://stackoverflow.com/questions/19237475/cannot-grant-privileges-to-mysql-database the accepted answer might help. – ROOT Jan 11 '20 at 16:11
  • Thanks @mamounothman, I checked and I'm using root user and root does have grant options so I'm not sure this is the case – Ruslan Jan 11 '20 at 16:18
  • @LightnessRacesBY-SA3.0 can you be more specific? The users table is big and I can't post all of it here – Ruslan Jan 11 '20 at 16:19
  • Then make a new database with just the one user in it, reproduce the issue, then show the output. Or copy this one and remove the unrelated items. Or show only the output pertaining to this user. We cannot help you if all we see is a normal `GRANT` statement and a normal `SELECT` query. And if you just "reinstall the server" then you may never know what happened (and/or you may cause other problems). – Lightness Races in Orbit Jan 11 '20 at 18:46

1 Answers1

1

To execute this command, you need to execute the sql query as admin, or with user granted to execute a GRANT query.

You can connect with root user, and try to execute 

GRANT SELECT, INSERT, UPDATE ON myschema.* TO 'OptimusPrime'@'cybertron';

and the result will be better.

MrLizzard
  • 158
  • 9
  • Actually, I'm already connecting with root. It is a part of a script that creates the schema. At the end of it (to avoid any further root-user statements) I want to grant permissions to a regular user – Ruslan Jan 11 '20 at 16:02
  • You have edited the permissions of your root user before ? Or is a fresh installation ? – MrLizzard Jan 11 '20 at 16:03
  • I'm not sure that you mean: edited the root user? no. I'm trying to edit OptimusPrime. And yeah, I think he's been edited before – Ruslan Jan 11 '20 at 16:04
  • Ok, what's your MySQL server version ? – MrLizzard Jan 11 '20 at 16:07
  • SELECT @@version; -- 8.0.18 – Ruslan Jan 11 '20 at 16:09
  • Thanks, silly question but, the user OptimusPrime already exists ? – MrLizzard Jan 11 '20 at 16:11
  • Yes, he exists. I *think* he exists for the schema, but unsure how to verify that – Ruslan Jan 11 '20 at 16:18
  • It strange, because the root user have all privileges by default... Have tried to execute this query (just adding the grant option to your root user) ? `GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*your password root*' WITH GRANT OPTION;` – MrLizzard Jan 11 '20 at 16:23
  • Tried. Strangely, I get the same error. I think I'll reinstall the server, this starts looking very strange. I'll post an update once I've done that – Ruslan Jan 11 '20 at 16:29
  • 1
    May be the force be with you. – MrLizzard Jan 11 '20 at 16:36