security_views

SYNOPSIS

Security views: views providing information on grants and privileges.

  • routine_privileges: INFORMATION_SCHEMA-like view on routines privileges
  • similar_grants: similar_grants: Listing GRANTEEs sharing the same set of privileges (i.e. share same role)
  • sql_accounts: Generate SQL statements to block/release accounts. Provide info on accounts
  • sql_grants: Generate SQL GRANT/REVOKE statements for existing accounts; provide with GRANT metadata
  • sql_show_grants: Generate complete accounts SHOW GRANTS FOR -like output

DESCRIPTION

There are several inconsistencies and missing pieces with regard to security related information in MySQL. These views compensate for: missing INFORMATION_SCHEMA routines privileges; missing SHOW GRANTS for all accounts; SQL generation for GRANT and REVOKE statements.

Closely related are Security routines and processlist_grantees.

EXAMPLES

Show grants for users called 'world_user':

mysql> SELECT sql_grants FROM common_schema.sql_show_grants WHERE user='world_user' \G
*************************** 1. row ***************************
sql_grants: 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';

Block all accounts for user 'gromit':

mysql> CALL eval("SELECT sql_block_account FROM sql_accounts WHERE USER = 'gromit'");

 
common_schema documentation