From: Daevid Vincent Date: July 22 2009 7:52pm Subject: How do you remove a user from the grant table?!! List-Archive: http://lists.mysql.com/mysql/218268 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit How the F do you remove a user from the grant table?!! The mysql.com site is down too by the way... (root@localhost) [(none)]> SHOW GRANTS FOR 'madc'; ERROR 1141 (42000): There is no such grant defined for user 'madc' on host '%' (root@localhost) [(none)]> SHOW GRANTS FOR 'madc'@; ERROR 1141 (42000): There is no such grant defined for user 'madc' on host '' (root@localhost) [(none)]> SHOW GRANTS FOR 'madc'@'%'; ERROR 1141 (42000): There is no such grant defined for user 'madc' on host '%' (root@localhost) [(none)]> SHOW GRANTS FOR 'madc'@'localhost'; +------------------------------------------------------------------+ | Grants for madc@localhost | +------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'madc'@'localhost' | | GRANT ALL PRIVILEGES ON `panasonic_elog`.* TO 'madc'@'localhost' | +------------------------------------------------------------------+ 2 rows in set (0.00 sec) (root@localhost) [(none)]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'madc'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) (root@localhost) [(none)]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'madc'@'localhost'; Query OK, 0 rows affected (0.00 sec) (root@localhost) [(none)]> GRANT ALL PRIVILEGES ON `panasonic_elog`.* TO 'madc'@'127.0.0.1' IDENTIFIED BY 'madc'; Query OK, 0 rows affected (0.00 sec) (root@localhost) [(none)]> SHOW GRANTS FOR 'madc'@'127.0.0.1'; +--------------------------------------------------------------------------- ----------------------------------+ | Grants for madc@stripped | +--------------------------------------------------------------------------- ----------------------------------+ | GRANT USAGE ON *.* TO 'madc'@'127.0.0.1' IDENTIFIED BY PASSWORD '*3A4AE615A4AC13515847C40F6F34892B51A6D209' | | GRANT ALL PRIVILEGES ON `panasonic_elog`.* TO 'madc'@'127.0.0.1' | +--------------------------------------------------------------------------- ----------------------------------+ 2 rows in set (0.00 sec) (root@localhost) [(none)]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'madc'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) (root@localhost) [(none)]> SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants; +-------------------------------------------------+ | mygrants | +-------------------------------------------------+ | SHOW GRANTS FOR ''@'localhost'; | | SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; | | SHOW GRANTS FOR 'madc'@'127.0.0.1'; | | SHOW GRANTS FOR 'madc'@'localhost'; | | SHOW GRANTS FOR 'root'@'127.0.0.1'; | | SHOW GRANTS FOR 'root'@'localhost'; | | SHOW GRANTS FOR 'slave'@'10.10.10.%'; | | SHOW GRANTS FOR 'slave_user'@'%'; | +-------------------------------------------------+ (root@localhost) [(none)]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'madc'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) (root@localhost) [(none)]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'madc'@'localhost'; Query OK, 0 rows affected (0.01 sec) (root@localhost) [(none)]> SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants; +-------------------------------------------------+ | mygrants | +-------------------------------------------------+ | SHOW GRANTS FOR ''@'localhost'; | | SHOW GRANTS FOR ''@'pse05'; | | SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; | | SHOW GRANTS FOR 'madc'@'127.0.0.1'; | | SHOW GRANTS FOR 'madc'@'localhost'; | | SHOW GRANTS FOR 'root'@'127.0.0.1'; | | SHOW GRANTS FOR 'root'@'localhost'; | | SHOW GRANTS FOR 'slave'@'10.10.10.%'; | | SHOW GRANTS FOR 'slave_user'@'%'; | +-------------------------------------------------+ (root@localhost) [(none)]> SHOW GRANTS FOR 'madc'@'127.0.0.1'; +--------------------------------------------------------------------------- ----------------------------------+ | Grants for madc@stripped | +--------------------------------------------------------------------------- ----------------------------------+ | GRANT USAGE ON *.* TO 'madc'@'127.0.0.1' IDENTIFIED BY PASSWORD '*3A4AE615A4AC13515847C40F6F34892B51A6D209' | +--------------------------------------------------------------------------- ----------------------------------+ 1 row in set (0.00 sec) (root@localhost) [(none)]> REVOKE ALL PRIVILEGES FROM 'madc'@'127.0.0.1'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM 'madc'@'127.0.0.1'' at line 1 (root@localhost) [(none)]> REVOKE ALL PRIVILEGES ON 'madc'@'127.0.0.1'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''madc'@'127.0.0.1'' at line 1 (root@localhost) [(none)]> REVOKE ALL PRIVILEGES ON *.* FROM 'madc'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) (root@localhost) [(none)]> SHOW GRANTS FOR 'madc'@'127.0.0.1'; +--------------------------------------------------------------------------- ----------------------------------+ | Grants for madc@stripped | +--------------------------------------------------------------------------- ----------------------------------+ | GRANT USAGE ON *.* TO 'madc'@'127.0.0.1' IDENTIFIED BY PASSWORD '*3A4AE615A4AC13515847C40F6F34892B51A6D209' | +--------------------------------------------------------------------------- ----------------------------------+ 1 row in set (0.00 sec) (root@localhost) [(none)]> REVOKE ALL PRIVILEGES ON `panasonic_elog`.* FROM 'madc'@'127.0.0.1'; ERROR 1141 (42000): There is no such grant defined for user 'madc' on host '127.0.0.1' (root@localhost) [(none)]> SHOW GRANTS FOR 'madc'@'127.0.0.1'; +--------------------------------------------------------------------------- ----------------------------------+ | Grants for madc@stripped | +--------------------------------------------------------------------------- ----------------------------------+ | GRANT USAGE ON *.* TO 'madc'@'127.0.0.1' IDENTIFIED BY PASSWORD '*3A4AE615A4AC13515847C40F6F34892B51A6D209' | +--------------------------------------------------------------------------- ----------------------------------+ 1 row in set (0.00 sec) (root@localhost) [(none)]> REVOKE ALL PRIVILEGES ON `panasonic_elog`.* FROM 'madc'@'127.0.0.1' IDENTIFIED BY 'madc'; ERROR 1141 (42000): There is no such grant defined for user 'madc' on host '127.0.0.1' (root@localhost) [(none)]> FLUSH privileges; Query OK, 0 rows affected (0.00 sec) (root@localhost) [(none)]> SHOW GRANTS FOR 'madc'@'127.0.0.1'; +--------------------------------------------------------------------------- ----------------------------------+ | Grants for madc@stripped | +--------------------------------------------------------------------------- ----------------------------------+ | GRANT USAGE ON *.* TO 'madc'@'127.0.0.1' IDENTIFIED BY PASSWORD '*3A4AE615A4AC13515847C40F6F34892B51A6D209' | +--------------------------------------------------------------------------- ----------------------------------+ 1 row in set (0.00 sec) (root@localhost) [(none)]> SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants; +-------------------------------------------------+ | mygrants | +-------------------------------------------------+ | SHOW GRANTS FOR ''@'localhost'; | | SHOW GRANTS FOR ''@'pse05'; | | SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; | | SHOW GRANTS FOR 'madc'@'127.0.0.1'; | | SHOW GRANTS FOR 'madc'@'localhost'; | | SHOW GRANTS FOR 'root'@'127.0.0.1'; | | SHOW GRANTS FOR 'root'@'localhost'; | | SHOW GRANTS FOR 'slave'@'10.10.10.%'; | | SHOW GRANTS FOR 'slave_user'@'%'; | +-------------------------------------------------+