4

i have a mysql installation with lots of users and grants. But one grant is unique, since its supposed to be a simple DB user and usually does not need GRANT privileges. However someone added it when the account where added. It looks like this:

GRANT USAGE ON *.* TO 'freg2'@'%' IDENTIFIED BY PASSWORD '*XXXXX';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, \
    REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, \
    LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, \
    CREATE ROUTINE, ALTER ROUTINE \
    ON `freg2`.* TO 'freg2'@'%' WITH GRANT OPTION;

I searched the net (and this site) for a solution on how to remove that "GRANT OPTION" thingy, without recreating the whole grant/account . so far without luck.

Is there really no way just to remove "this" privilege or right from that account ?

thanks in advance.

Improve this question

1 Answer 1

Reset to default
8

Just as usual...

root@localhost:playground > grant select, grant option on playground.* to asdf@localhost identified by 'asdf';
Query OK, 0 rows affected (0.02 sec)

root@localhost:playground > show grants for asdf@localhost;
+-------------------------------------------------------------------------------------------------------------+
| Grants for asdf@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'asdf'@'localhost' IDENTIFIED BY PASSWORD '*7F0C90A004C46C64A0EB9DDDCE5DE0DC437A635C' |
| GRANT SELECT ON `playground`.* TO 'asdf'@'localhost' WITH GRANT OPTION                                      |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost:playground > revoke grant option on playground.* from asdf@localhost;
Query OK, 0 rows affected (0.00 sec)

root@localhost:playground > show grants for asdf@localhost;
+-------------------------------------------------------------------------------------------------------------+
| Grants for asdf@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'asdf'@'localhost' IDENTIFIED BY PASSWORD '*7F0C90A004C46C64A0EB9DDDCE5DE0DC437A635C' |
| GRANT SELECT ON `playground`.* TO 'asdf'@'localhost'                                                        |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The manual is also as always very helpful.

Improve this answer
2
  • 2
    Except the "usual" may not be equally obvious to everyone, as evidenced by this old bug report. Still, good job both showing how it's done and illustrating how it works.
    – Andriy M
    Mar 30, 2016 at 14:54
  • thanks! after all my failed attempts and net searching i am surprised that what i already tried multiple times and failed, now is supposed to work. i guess the problem was some nasty details with syntax and quoting that i did wrong. what worked for me at the end was: mysql -u"${targetMySqlUser}" -p"${targetMySqlPass}" -e "revoke grant option on freg2.* from freg2@%;" - btw: i read the manual for 5.5 (revoke) , but without a proper example i still had trouble putting something together that worked.
    – Axel Werner
    Mar 31, 2016 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.