CODEWITHSUNDEEP
sqlmysqldatabaseprivilegessql-grant
jpganz18
jpganz18

Reputation: 5878

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?

Upvotes: 14

Views: 14451

Answers (3)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

I got the same error below:

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

When I gave PROCESS privilege on all the tables in only apple database (apple.*) to the user john with GRANT by login with the user root as shown below:

GRANT PROCESS ON apple.* TO 'john'@'localhost';

So, I gave PROCESS privilege on all the tables in all databases (*.*) as shown below, then I could solve the error:

GRANT PROCESS ON *.* TO 'john'@'localhost';

Actually, the doc says below:

Because any static global privilege is considered a privilege for all databases, ...

And, I gave PROCESS privilege on only person table in all databases (*.person) as shown below, but I got error:

GRANT PROCESS ON *.person TO 'john'@'localhost';

So, we need to grant static global privilege like PROCESS, FILE, REPLICATION SLAVE, etc on all the tables in all databases (*.*) as shown below, otherwise there is error:

GRANT [STATIC_GLOBAL_PRIVILEGES] ON *.* TO <USER>;

Upvotes: 0

questionto42
questionto42

Reputation: 9640

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

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562931

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

Upvotes: 28

Related Questions