Trying to revoke rights from a user on an old MySQL 5.1 server:

mysql> show grants for 'jive'@'%';
| GRANT USAGE ON *.* TO 'jive'@'%' IDENTIFIED BY PASSWORD 'XXXXXXX'
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `jive`.* TO 'jive'@'%'    |

A revoke of the same rights fails (as does revoke all):

mysql> revoke SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON jive.* from 'jive'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'jive' on host '%'
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'jive'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'jive' on host '%'

From digging online I found a suggestion to try granting a right first and then revoke, which initially appears to work:

mysql> grant select on jive.* to 'jive'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> revoke SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON jive.* from 'jive'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'jive'@'%';
| GRANT USAGE ON *.* TO 'jive'@'%' IDENTIFIED BY PASSWORD 'XXXX'

But a 'flush privileges' brings them back:

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'jive'@'%';
| GRANT USAGE ON *.* TO 'jive'@'%' IDENTIFIED BY PASSWORD 'XXXX'                                                                          
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `jive`.* TO 'jive'@'%'    |

I've looked in mysql.user and they are listed once, with all 'N's. They are not present in tables_priv or procs_priv. Where are the rights coming from?

  • Hmm, your grants are to 'jive' but your revokes are from jive. Maybe the ' marks matter? idk – indiri Oct 25 '17 at 15:47
  • I think it ate the backticks in your reply, but it gives the same errors with or without them. – bitmage Oct 25 '17 at 20:16
  • You said you checked mysql.tables_piv and mysql.procs_priv. Did you check mysql.db (where Database Level grants are stored) ??? – RolandoMySQLDBA Oct 26 '17 at 15:32
  • On the linux command line, please run mysql --version and tell us what is the version of the mysql client program. – RolandoMySQLDBA Oct 26 '17 at 15:36
  • mysql Ver 14.14 Distrib 5.1.47, for pc-linux-gnu (i686) using readline 5.1 The user does exist in mysql.db: | % | jive | jive | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | N | N | N | N | Y | Y | Y | N | N | So that has the rights that I've been unable to revoke. – bitmage Oct 26 '17 at 17:34

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.