List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:July 22 2009 7:52pm
Subject:How do you remove a user from the grant table?!!
View as plain text  
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'@'%';               |
+-------------------------------------------------+

Thread
How do you remove a user from the grant table?!!Daevid Vincent23 Jul
RE: How do you remove a user from the grant table?!!Sudhir Menon24 Jul