Mysql User and privileges

Check list of user:

mysql> SELECT user FROM mysql.user;

+-------------+

| user |

+-------------+

| dev_ro |

| root |

| app_parking |

| groubinet |

| parking |

| root |

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

+----------------------------+---------------+-------------------------+--------------+

+----------------------------+---------------+-------------------------+--------------+

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