mysql: show grants of all users
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)
Leave a Reply