Check list of user:
mysql> SELECT user FROM mysql.user;
+-------------+
| user |
+-------------+
| dev_ro |
| root |
| root |
| app_parking |
| groubinet |
| parking |
| root |
| groubinet |
| groubinet |
+-------------+
Here we have more than one rows for same user because these users can connect from multiple hosts.
mysql> SELECT user, host FROM mysql.user;
+-------------+--------------+
| user | host |
+-------------+--------------+
| dev_ro | 10.0.85.81 |
| root | 127.0.0.1 |
| root | ::1 |
| app_parking | localhost |
| groubinet | localhost |
| parking | localhost |
| root | localhost |
| groubinet | log1.ipx.com |
| groubinet | log2.ipx.com |
+-------------+--------------+
Displaying privileges for a specific MySQL user using SHOW GRANTS
mysql> SHOW GRANTS FOR 'groubinet'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for groubinet@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'groubinet'@'localhost' IDENTIFIED BY PASSWORD '*****************************' |
| GRANT ALL PRIVILEGES ON `parking`.* TO 'groubinet'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Displaying privileges for all MySQL users using SHOW GRANTS
mysql> SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;
+------------------------------------------------------+
| CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') |
+------------------------------------------------------+
| SHOW GRANTS FOR 'dev_ro'@'10.0.85.81'; |
| SHOW GRANTS FOR 'root'@'127.0.0.1'; |
| SHOW GRANTS FOR 'root'@'::1'; |
| SHOW GRANTS FOR 'app_parking'@'localhost'; |
| SHOW GRANTS FOR 'groubinet'@'localhost'; |
| SHOW GRANTS FOR 'parking'@'localhost'; |
| SHOW GRANTS FOR 'root'@'localhost'; |
| SHOW GRANTS FOR 'groubinet'@'log1.ipx.com'; |
| SHOW GRANTS FOR 'groubinet'@'log2.ipx.com'; |
+------------------------------------------------------+
9 rows in set (0.00 sec)
List Privilege Using information_schema.user_privileges
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 |
| 'root'@'127.0.0.1' | def | SELECT | YES |
| 'root'@'127.0.0.1' | def | INSERT | YES |
| 'root'@'127.0.0.1' | def | UPDATE | YES |
| 'root'@'127.0.0.1' | def | DELETE | YES |
| 'root'@'127.0.0.1' | def | CREATE | YES |
| 'root'@'127.0.0.1' | def | DROP | YES |
| 'root'@'127.0.0.1' | def | RELOAD | YES |
| 'root'@'127.0.0.1' | def | SHUTDOWN | YES |
| 'root'@'127.0.0.1' | def | PROCESS | YES |
| 'root'@'127.0.0.1' | def | FILE | YES |
| 'root'@'127.0.0.1' | def | REFERENCES | YES |
| 'root'@'127.0.0.1' | def | INDEX | YES |
| 'root'@'127.0.0.1' | def | ALTER | YES |
| 'root'@'127.0.0.1' | def | SHOW DATABASES | YES |
| 'root'@'127.0.0.1' | def | SUPER | YES |
| 'root'@'127.0.0.1' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'127.0.0.1' | def | LOCK TABLES | YES |
| 'root'@'127.0.0.1' | def | EXECUTE | YES |
| 'root'@'127.0.0.1' | def | REPLICATION SLAVE | YES |
| 'root'@'127.0.0.1' | def | REPLICATION CLIENT | YES |
| 'root'@'127.0.0.1' | def | CREATE VIEW | YES |
| 'root'@'127.0.0.1' | def | SHOW VIEW | YES |
| 'root'@'127.0.0.1' | def | CREATE ROUTINE | YES |
| 'root'@'127.0.0.1' | def | ALTER ROUTINE | YES |
| 'root'@'127.0.0.1' | def | CREATE USER | YES |
| 'root'@'127.0.0.1' | def | EVENT | YES |
| 'root'@'127.0.0.1' | def | TRIGGER | YES |
| 'root'@'127.0.0.1' | def | CREATE TABLESPACE | YES |
| 'root'@'::1' | def | SELECT | YES |
| 'root'@'::1' | def | INSERT | YES |
| 'root'@'::1' | def | UPDATE | YES |
| 'root'@'::1' | def | DELETE | YES |
| 'root'@'::1' | def | CREATE | YES |
| 'root'@'::1' | def | DROP | YES |
| 'root'@'::1' | def | RELOAD | YES |
| 'root'@'::1' | def | SHUTDOWN | YES |
| 'root'@'::1' | def | PROCESS | YES |
| 'root'@'::1' | def | FILE | YES |
| 'root'@'::1' | def | REFERENCES | YES |
| 'root'@'::1' | def | INDEX | YES |
| 'root'@'::1' | def | ALTER | YES |
| 'root'@'::1' | def | SHOW DATABASES | YES |
| 'root'@'::1' | def | SUPER | YES |
| 'root'@'::1' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'::1' | def | LOCK TABLES | YES |
| 'root'@'::1' | def | EXECUTE | YES |
| 'root'@'::1' | def | REPLICATION SLAVE | YES |
| 'root'@'::1' | def | REPLICATION CLIENT | YES |
| 'root'@'::1' | def | CREATE VIEW | YES |
| 'root'@'::1' | def | SHOW VIEW | YES |
| 'root'@'::1' | def | CREATE ROUTINE | YES |
| 'root'@'::1' | def | ALTER ROUTINE | YES |
| 'root'@'::1' | def | CREATE USER | YES |
| 'root'@'::1' | def | EVENT | YES |
| 'root'@'::1' | def | TRIGGER | YES |
| 'root'@'::1' | def | CREATE TABLESPACE | YES |
| 'parking'@'localhost' | def | FILE | NO |
| 'groubinet'@'localhost' | def | USAGE | NO |
| 'groubinet'@'log1.ipx.com' | def | USAGE | NO |
| 'groubinet'@'log2.ipx.com' | def | USAGE | NO |
| 'app_parking'@'localhost' | def | USAGE | NO |
| 'dev_ro'@'10.0.85.81' | def | USAGE | NO |
+----------------------------+---------------+-------------------------+--------------+
90 rows in set (0.00 sec)