Show MySQL grants overview

show_mysql_grants.sh

#! /bin/sh
# Simple script to show MySQL grants overview
# Copyright (C) 2025 Matous Jan Fialka, <https://mjf.cz/>
# Released under the terms of the "MIT" license

mysql -b -u'root' -p << \EOT |
SELECT
	`user`   AS `user`,
	`host`   AS `host`,
	'Global' AS `scope`,
	'-'      AS `database`,
	'-'      AS `table`,
	'-'      AS `column`,
	'-'      AS `routine`,
	'-'      AS `privileges`
FROM
	`mysql`.`db`

UNION ALL

SELECT
	`user`     AS `user`,
	`host`     AS `host`,
	'Database' AS `scope`,
	`db`       AS `database`,
	'-'        AS `table`,
	'-'        AS `column`,
	'-'        AS `routine`,

	CONCAT('MASK ',
		-- DQL
		IF(`select_priv`           = 'Y', 's', '-'),
		IF(`show_view_priv`        = 'Y', 'S', '-'), ' ',
		-- DML
		IF(`insert_priv`           = 'Y', 'i', '-'),
		IF(`update_priv`           = 'Y', 'u', '-'),
		IF(`delete_priv`           = 'Y', 'd', '-'),
		IF(`execute_priv`          = 'Y', 'x', '-'), ' ',
		IF(`lock_tables_priv`      = 'Y', 'l', '-'),
		-- DDL
		IF(`create_priv`           = 'Y', 'c', '-'),
		IF(`create_routine_priv`   = 'Y', 'r', '-'),
		IF(`create_tmp_table_priv` = 'Y', 't', '-'),
		IF(`create_view_priv`      = 'Y', 'v', '-'),
		IF(`alter_priv`            = 'Y', 'A', '-'),
		IF(`alter_routine_priv`    = 'Y', 'R', '-'),
		IF(`drop_priv`             = 'Y', 'D', '-'),
		IF(`event_priv`            = 'Y', 'e', '-'),
		IF(`index_priv`            = 'Y', 'i', '-'),
		IF(`trigger_priv`          = 'Y', 't', '-'),
		IF(`references_priv`       = 'Y', 'k', '-'), ' ',
		-- DCL
		IF(`grant_priv`            = 'Y', 'g', '-')) AS `privileges`
FROM
	`mysql`.`db`

UNION ALL

SELECT
	`user`              AS `user`,
	`host`              AS `host`,
	'Table'             AS `scope`,
	`db`                AS `database`,
	`table_name`        AS `table`,
	'-'                 AS `column`,
	'-'                 AS `routine`,
	UPPER(`table_priv`) AS `privileges`
FROM
	`mysql`.`tables_priv`
WHERE
	`table_priv`

UNION ALL

SELECT
	`user`               AS `user`,
	`host`               AS `host`,
	'Column'             AS `scope`,
	`db`                 AS `database`,
	`table_name`         AS `table`,
	`column_name`        AS `column`,
	'-'                  AS `routine`,
	UPPER(`column_priv`) AS `privileges`
FROM
	`mysql`.`columns_priv`

UNION ALL

SELECT
	`user`             AS `user`,
	`host`             AS `host`,
	'Routine'          AS `scope`,
	`db`               AS `database`,
	'-'                AS `table`,
	'-'                AS `column`,
	`routine_name`     AS `routine`,
	UPPER(`proc_priv`) AS `privileges`
FROM
	`mysql`.`procs_priv`
EOT

awk '
BEGIN {
	FS = "\t"
}

NR == 1 {
	$0 = toupper($0)
}

{
	n = NF > n ? NF : n
	for(i = 1; i <= NF; i++) {
		len = length($i)
		if(len > max[i])
			max[i] = len
		data[NR, i] = $i
	}
	rows = NR
}

END {
	for (r = 1; r <= rows; r++) {
		for(c = 1; c <= n; c++) {
			f = data[r, c]
			printf "%-*s", max[c], f
			if(c < n)
				printf "  "
		}
		print ""
	}
}'

cat << EOT

Legend:
  DQL	(s) SELECT, (v) SHOW VIEW
  DML	(i) INSERT, (u) UPDATE, (d) DELETE, (x) EXECUTE, (l) LOCK TABLES,
  DDL	(c) CREATE, (r) CREATE ROUTINE, (t) CREATE TEMPORARY TABLE,
	(v) CREATE VIEW, (A) ALTER, (R) ALTER ROUTINE, (D) DROP, (e) EVENT,
	(i) INDEX, (t) TRIGGER, (r) REFERENCES
  DCL	(g) GRANT
EOT

# vi: ft=sh