sql_show_grants
NAME
sql_show_grants: generate complete accounts SHOW GRANTS FOR -like outputTYPE
ViewDESCRIPTION
sql_show_grants generates an output similar to that of SHOW GRANTS FOR..., for all existing accounts. It also includes account information, hence it is easy to filter results by account properties.
MySQL does not provide with a similar feature. It only provides SHOW GRANTS FOR for a given account, and does not provide with the complete grants table. Also, it is not an SQL query, and so cannot be subjected to filtering, grouping, ordering, etc.
In fact, 3rd party tools, such as mk-show-grants are often used to interrogate MySQL as for the set of accounts, then listing the grants for those accounts. This view generates similar output.
This view builds upon the sql_grants results.
STRUCTURE
mysql> DESC common_schema.sql_show_grants; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | GRANTEE | varchar(81) | NO | | | | | user | char(16) | NO | | | | | host | char(60) | NO | | | | | sql_grants | longtext | YES | | NULL | | +------------+-------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- GRANTEE: grantee's account
- user: account user part
- host: account host part
- sql_grants: The entire
GRANT set of privileges for building the account; similar to the output of SHOW GRANTS FOR
EXAMPLES
Show grants for users called 'world_user':
mysql> SELECT * FROM common_schema.sql_show_grants WHERE user='world_user'; +--------------------------+------------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANTEE | user | host | sql_grants | +--------------------------+------------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 'world_user'@'localhost' | world_user | localhost | GRANT USAGE ON *.* TO 'world_user'@'localhost' IDENTIFIED BY PASSWORD ''; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `world`.* TO 'world_user'@'localhost'; | +--------------------------+------------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Dump grants into external file:
mysql> SELECT sql_grants FROM common_schema.sql_show_grants INTO OUTFILE '/tmp/grants.sql'; bash$ cat /tmp/grants.sql GRANT USAGE ON *.* TO 'apps'@'%' IDENTIFIED BY PASSWORD ''; GRANT DELETE, INSERT, SELECT, UPDATE ON `test`.* TO 'apps'@'%'; GRANT SELECT (description, film_id, title), UPDATE (description) ON `sakila`.`film` TO 'apps'@'%' WITH GRANT OPTION; GRANT USAGE ON *.* TO 'gromit'@'localhost' IDENTIFIED BY PASSWORD ''; GRANT DELETE, INSERT, SELECT, UPDATE ON `world`.`City` TO 'gromit'@'localhost' WITH GRANT OPTION; GRANT USAGE ON *.* TO 'monitoring_user'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; GRANT PROCESS ON *.* TO 'monitoring_user'@'localhost';