How do you remove a user from the grant table?!!

How do you remove a user from the grant table?!!

am 22.07.2009 21:52:14 von Daevid Vincent

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@127.0.0.1
|
+----------------------------------------------------------- ----------------
----------------------------------+
| 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@127.0.0.1
|
+----------------------------------------------------------- ----------------
----------------------------------+
| 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@127.0.0.1
|
+----------------------------------------------------------- ----------------
----------------------------------+
| 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@127.0.0.1
|
+----------------------------------------------------------- ----------------
----------------------------------+
| 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@127.0.0.1
|
+----------------------------------------------------------- ----------------
----------------------------------+
| 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'@'%'; |
+-------------------------------------------------+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: How do you remove a user from the grant table?!!

am 24.07.2009 07:03:33 von Sudhir Menon

--000e0cd30470b07439046f6c86f3
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

*mysql> create user 'test'@'localhost' identified by 'pass';*
Query OK, 0 rows affected (0.00 sec)

*mysql> GRANT CREATE, DELETE ON *.* TO 'test'@'localhost';*
Query OK, 0 rows affected (0.00 sec)

*mysql> select * from information_schema.user_privileges where grantee like
"'test'@'localhost'";*
+--------------------+---------------+----------------+----- ---------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------+---------------+----------------+----- ---------+
| 'test'@'localhost' | NULL | DELETE | NO |
| 'test'@'localhost' | NULL | CREATE | NO |
+--------------------+---------------+----------------+----- ---------+
2 rows in set (0.00 sec)

*mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'test'@'localhost';*
Query OK, 0 rows affected (0.00 sec)

*mysql> select * from information_schema.user_privileges where grantee like
"'test'@'localhost'";*
+--------------------+---------------+----------------+----- ---------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------+---------------+----------------+----- ---------+
| 'test'@'localhost' | NULL | USAGE | NO |
+--------------------+---------------+----------------+----- ---------+
*
REVOKEremoves
privileges, but does not drop
mysql.user table entries. To remove a user account entirely, use DROP
USER

mysql> drop user 'test'@'localhost;

mysql> select * from information_schema.user_privileges where grantee like
"'test'@'localhost'";
Empty set (0.00 sec)
*

--000e0cd30470b07439046f6c86f3--