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) |