Cannot grant privileges to a user using root on mysql

user_1143482 0
Viewed 2477

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?

sql-grant mysql
2 Answers

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' ;
Bill_Karwin_208600

I had the strange thing that the "root" user had the rights to grant rights, but I still had to use the user "admin" instead.

With root, I got:

SQL Error [1045] [28000]: Access denied for user 'root'@'%' (using password: YES)

It might just be the setup, but when I look it up in the GUI of DBeaver (Connection --> Users --> Grants --> View Grants), they both have all of the rights checked, and I still cannot grant rights with the "root" user.

Perhaps it helps someone with another weird db setup.

Admin:

enter image description here

Root:

enter image description here

user_111548410
Related