From: Daevid Vincent
Date: July 22 2009 7:58pm
Subject: How do you show ALL grants for a username?
List-Archive: http://lists.mysql.com/mysql/218265
Message-Id: <83638774659F4A1B889F728F339DA349@mascorp.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

(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 ''@'pse01';                     |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'madc'@'%';                     |
| SHOW GRANTS FOR 'madc'@'10.10.10.%';            |
| SHOW GRANTS FOR 'madc'@'10.10.10.42';           |
| 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'@'%';               |
+-------------------------------------------------+

But how do I see all the grants that "madc" has? I would have expected the %
wildcard to work, but mysql uses it as a literal!?

(root@localhost) [(none)]> SHOW GRANTS FOR 'madc'@'%';
+---------------------------------------------------------------------------
-----------------------------------------------------+
| Grants for madc@%
|
+---------------------------------------------------------------------------
-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'madc'@'%' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `misc`.* TO 'madc'@'%' WITH GRANT OPTION
|
| GRANT ALL PRIVILEGES ON `elog`.* TO 'madc'@'%' WITH GRANT OPTION
|
+---------------------------------------------------------------------------
-----------------------------------------------------+

(root@localhost) [(none)]> SHOW GRANTS FOR 'madc';
+---------------------------------------------------------------------------
-----------------------------------------------------+
| Grants for madc@%
|
+---------------------------------------------------------------------------
-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'madc'@'%' IDENTIFIED BY PASSWORD
'*3A4AE615A4AC13515847C40F6F34892B51A6D209' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `misc`.* TO 'madc'@'%' WITH GRANT OPTION
|
| GRANT ALL PRIVILEGES ON `elog`.* TO 'madc'@'%' WITH GRANT OPTION
|
+---------------------------------------------------------------------------
-----------------------------------------------------+

(root@localhost) [(none)]> SHOW GRANTS FOR 'madc'@;
ERROR 1141 (42000): There is no such grant defined for user 'madc' on host
''