Mysql User and privileges

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)

Comments