sql_grants
NAME
sql_grants: generate SQL GRANT/REVOKE statements for existing accounts; provide with GRANT metadataTYPE
ViewDESCRIPTION
sql_grants presents metadata for existing accounts, and generates SQL queries for granting/revoking their privileges set.
It is a one-stop-shop for getting the set of privileges per account, per privilege level (db, schema, table, column, routine). The view lists the set of privileges per account in several formats:
- In comma delimited format (e.g. SELECT, INSERT, UPDATE, EXECUTE)
- In GRANT syntax
- In REVOKE syntax
The original mysql privileges tables, or the INFORMATION_SCHEMA *_PRIVILEGES views make for a per-domain distinction of privileges: a table for per-schema privileges; a table for per-table privileges, etc.
The only existing alternative to that is the SHOW GRANTS FOR command. Alas, it is not a proper SQL query, and does not provide with structured result.
The sql_grants view provides with structured results, easily filtered or searched.
This view builds on routine_privileges.
STRUCTURE
mysql> DESC common_schema.sql_grants; +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+-------+ | GRANTEE | varchar(81) | NO | | | | | user | char(16) | NO | | | | | host | char(60) | NO | | | | | priv_level | varchar(133) | NO | | | | | priv_level_name | varchar(7) | NO | | | | | object_schema | varchar(64) | YES | | NULL | | | object_name | varchar(64) | YES | | NULL | | | current_privileges | mediumtext | YES | | NULL | | | IS_GRANTABLE | varchar(3) | YES | | NULL | | | sql_grant | longtext | YES | | NULL | | | sql_revoke | longtext | YES | | NULL | | | sql_drop_user | varchar(91) | NO | | | | +--------------------+--------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- GRANTEE: grantee's account
- user: account user part
- host: account host part
- priv_level: the domain on which the privileges are set (e.g. *.*, sakila.*)
- priv_level_name: description of priv_level: 'user', 'schema', 'table', 'column', 'routine'
- object_schema: name of schema in which object lies. Applies for table, column, routine; otherwise NULL
- object_name: name of object for which grants apply. Applies for schema, table, column, routine; otherwise NULL
- current_privileges: comma delimited list of privileges assigned to account on current privilege level
- IS_GRANTABLE: does current account have the GRANT privileges on this domain? 'Yes' or 'NO'
- sql_grant:
A GRANT query to generate current set of privileges.
Use with eval() to apply query. - sql_revoke:
A REVOKE query to revoke current set of privileges.
Use with eval() to apply query. - sql_drop_user:
A DROP USER query to drop account.
Use with eval() to apply query.
The view is in 1st normal form. The sql_drop_user column applies to a grantee in general, unrelated to the current domain.
EXAMPLES
Generate all content for the 'apps' user:
mysql> SELECT * FROM common_schema.sql_grants WHERE user = 'apps'\G
*************************** 1. row ***************************
GRANTEE: 'apps'@'%'
user: apps
host: %
priv_level: *.*
priv_level_name: user
object_schema: NULL
object_name: NULL
current_privileges: USAGE
IS_GRANTABLE: NO
sql_grant: GRANT USAGE ON *.* TO 'apps'@'%' IDENTIFIED BY PASSWORD ''
sql_revoke:
sql_drop_user: DROP USER 'apps'@'%'
*************************** 2. row ***************************
GRANTEE: 'apps'@'%'
user: apps
host: %
priv_level: `test`.*
priv_level_name: schema
object_schema: NULL
object_name: test
current_privileges: DELETE, INSERT, SELECT, UPDATE
IS_GRANTABLE: NO
sql_grant: GRANT DELETE, INSERT, SELECT, UPDATE ON `test`.* TO 'apps'@'%'
sql_revoke: REVOKE DELETE, INSERT, SELECT, UPDATE ON `test`.* FROM 'apps'@'%'
sql_drop_user: DROP USER 'apps'@'%'
*************************** 3. row ***************************
GRANTEE: 'apps'@'%'
user: apps
host: %
priv_level: `sakila`.`film`
priv_level_name: column
object_schema: sakila
object_name: film
current_privileges: SELECT (description, film_id, title), UPDATE (description)
IS_GRANTABLE: YES
sql_grant: GRANT SELECT (description, film_id, title), UPDATE (description) ON `sakila`.`film` TO 'apps'@'%' WITH GRANT OPTION
sql_revoke: REVOKE SELECT (description, film_id, title), UPDATE (description), GRANT OPTION ON `sakila`.`film` FROM 'apps'@'%'
sql_drop_user: DROP USER 'apps'@'%'
Show privileges per domain for 'other_user'@'localhost'
mysql> SELECT priv_level, current_privileges FROM common_schema.sql_grants WHERE GRANTEE = '\'other_user\'@\'localhost\''; +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | priv_level | current_privileges | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | *.* | USAGE | | `world`.* | 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 | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Generate REVOKE statements for all users:
mysql> SELECT sql_revoke FROM common_schema.sql_grants WHERE sql_revoke != ''; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sql_revoke | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | REVOKE DELETE, INSERT, SELECT, UPDATE ON `test`.* FROM 'apps'@'%' | | REVOKE SELECT (description, film_id, title), UPDATE (description), GRANT OPTION ON `sakila`.`film` FROM 'apps'@'%' | | REVOKE PROCESS ON *.* FROM 'monitoring_user'@'localhost' | | REVOKE 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`.* FROM 'other_user'@'localhost' | | REVOKE REPLICATION SLAVE ON *.* FROM 'replication'@'10.0.0.%' | | REVOKE ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE, GRANT OPTION ON *.* FROM 'root'@'127.0.0.1' | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+