[SOLVED] Cannot grant privileges to a user using root on mysql

Cannot grant privileges to a user using root on mysql

I am trying to give explicit permissions to an user on mysql and im doing this (to an already created user)

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, SHOW DATABASES, 
 CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, 
 CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER 
ON mydatabase.* 
TO 'myuser'@'localhost' ;

But im getting this weird error:

Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

I tried on other schemas with other users making a GRANT ALL PRIVILEGES and seems is working. Any idea?

Solution

Some privileges only make sense when the grant references ON *.* as the schema.table.

The manual page https://dev.mysql.com/doc/refman/5.7/en/grant.html lists all the available privileges, and notes each for global, database, table, based on whether you can grant them at different levels of scope.

The SHOW DATABASES privilege can only be granted at the global level.

So you'll have to do it this way:

GRANT SHOW DATABASES 
ON *.* 
TO 'myuser'@'localhost' ;

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, 
 CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, 
 CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER 
ON mydatabase.* 
TO 'myuser'@'localhost' ;