How do you show ALL grants for a username?

(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
''


--
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 show ALL grants for a username?

--=-eZASkSMDi1ZXBTHpYo0e
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Use information_schema!

select * from information_schema.user_privileges where grantee like
"'madc'@%";

Should get you what you need.

John Daisley
Email: john.daisley@butterflysystems.co.uk
Mobile: +44 (0)7812 451238

MySQL Certified Database Administrator (CMDBA)
MySQL Certified Developer (CMDEV)
MySQL Certified Associate (CMA)
Comptia A+ Certified Professional IT Technician

-------

Life's journey is not to arrive at the grave safely in a well preserved
body, but rather to slide in sideways, thoroughly used up, totally worn
out and screaming "Wow! what a ride!"



















On Wed, 2009-07-22 at 12:58 -0700, Daevid Vincent wrote:

> (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
> ''
>
>

--=-eZASkSMDi1ZXBTHpYo0e--