Unfortunately, there’s no a single MySQL command to display all privileges granted to all users, but that information can be gathered with the following procedure:

1. Login as root (or another user with enough privileges) to mysql command line:

mysql -u root -p

2. List all users defined in mysql table:

mysql> select user,host from mysql.user;
+--------+-----------+ 
| user   | host      | 
+--------+-----------+ 
| root   | localhost | 
| prueba | localhost | 
+--------+-----------+ 
4 rows in set (0.08 sec)

3. Show grants for the specific user you want to see:

mysql> show grants for prueba@localhost;
+---------------------------------------------+
| Grants for prueba@%                         |
+---------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'prueba'@'%' |
+---------------------------------------------+

NOTE: You can get the above information for all users using this shell command (assumes you’re executing using root and no password is required to access mysql):

# mysql -B -N -e  "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;" | mysql $@ | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/#-- \1 /;/#--/{x;p;x;}'

If you need to have a more detailed information about the grants, you can query the database information_schema:

mysql> SELECT * FROM information_schema.user_privileges;
+-------------------------+---------------+-------------------------+--------------+
| GRANTEE                 | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+-------------------------+---------------+-------------------------+--------------+
| 'root'@'localhost'      | def           | SELECT                  | YES          |
| 'root'@'localhost'      | def           | INSERT                  | YES          |
| 'root'@'localhost'      | def           | UPDATE                  | YES          |
| 'root'@'localhost'      | def           | DELETE                  | YES          |
| 'root'@'localhost'      | def           | CREATE                  | YES          |
| 'root'@'localhost'      | def           | DROP                    | YES          |
| 'root'@'localhost'      | def           | RELOAD                  | YES          |
| 'root'@'localhost'      | def           | SHUTDOWN                | YES          |
| 'root'@'localhost'      | def           | PROCESS                 | YES          |
| 'root'@'localhost'      | def           | FILE                    | YES          |
| 'root'@'localhost'      | def           | REFERENCES              | YES          |
| 'root'@'localhost'      | def           | INDEX                   | YES          |
| 'root'@'localhost'      | def           | ALTER                   | YES          |
| 'root'@'localhost'      | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost'      | def           | SUPER                   | YES          |
| 'root'@'localhost'      | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost'      | def           | LOCK TABLES             | YES          |
| 'root'@'localhost'      | def           | EXECUTE                 | YES          |
| 'root'@'localhost'      | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost'      | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost'      | def           | CREATE VIEW             | YES          |
| 'root'@'localhost'      | def           | SHOW VIEW               | YES          |
| 'root'@'localhost'      | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost'      | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost'      | def           | CREATE USER             | YES          |
| 'root'@'localhost'      | def           | EVENT                   | YES          |
| 'root'@'localhost'      | def           | TRIGGER                 | YES          |
| 'root'@'localhost'      | def           | CREATE TABLESPACE       | YES          |
| 'prueba'@'localhost'    | def           | SELECT                  | NO           |
| 'prueba'@'localhost'    | def           | INSERT                  | NO           |
| 'prueba'@'localhost'    | def           | UPDATE                  | NO           |
| 'prueba'@'localhost'    | def           | DELETE                  | NO           |
| 'prueba'@'localhost'    | def           | CREATE                  | NO           |
| 'prueba'@'localhost'    | def           | DROP                    | NO           |
| 'prueba'@'localhost'    | def           | RELOAD                  | NO           |
| 'prueba'@'localhost'    | def           | SHUTDOWN                | NO           |
| 'prueba'@'localhost'    | def           | PROCESS                 | NO           |
| 'prueba'@'localhost'    | def           | FILE                    | NO           |
| 'prueba'@'localhost'    | def           | REFERENCES              | NO           |
| 'prueba'@'localhost'    | def           | INDEX                   | NO           |
| 'prueba'@'localhost'    | def           | ALTER                   | NO           |
| 'prueba'@'localhost'    | def           | SHOW DATABASES          | NO           |
| 'prueba'@'localhost'    | def           | SUPER                   | NO           |
| 'prueba'@'localhost'    | def           | CREATE TEMPORARY TABLES | NO           |
| 'prueba'@'localhost'    | def           | LOCK TABLES             | NO           |
| 'prueba'@'localhost'    | def           | EXECUTE                 | NO           |
| 'prueba'@'localhost'    | def           | REPLICATION SLAVE       | NO           |
| 'prueba'@'localhost'    | def           | REPLICATION CLIENT      | NO           |
| 'prueba'@'localhost'    | def           | CREATE VIEW             | NO           |
| 'prueba'@'localhost'    | def           | SHOW VIEW               | NO           |
| 'prueba'@'localhost'    | def           | CREATE ROUTINE          | NO           |
| 'prueba'@'localhost'    | def           | ALTER ROUTINE           | NO           |
| 'prueba'@'localhost'    | def           | CREATE USER             | NO           |
| 'prueba'@'localhost'    | def           | EVENT                   | NO           |
| 'prueba'@'localhost'    | def           | TRIGGER                 | NO           |
| 'prueba'@'localhost'    | def           | CREATE TABLESPACE       | NO           |
+-------------------------+---------------+-------------------------+--------------+
56 rows in set (0.00 sec)
Share!