Last active
October 15, 2025 07:38
-
-
Save mjf/8148de7fd2bc8bbecfa32429346e06b5 to your computer and use it in GitHub Desktop.
Show MySQL grants overview
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #! /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 | |
| cols=0 | |
| if [ -t 1 ]; then | |
| cols="$(tput cols)" | |
| fi | |
| sed "s|@|${*:-TRUE}|g" << \EOT | | |
| WITH `results` AS ( | |
| 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`, | |
| 'Proxy' AS `scope`, | |
| '-' AS `database`, | |
| '-' AS `table`, | |
| '-' AS `column`, | |
| '-' AS `routine`, | |
| '-' AS `privileges` | |
| FROM | |
| `mysql`.`proxies_priv` | |
| 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', 'v', '-'), ' ', | |
| -- DML | |
| IF(`insert_priv` = 'Y', 'i', '-'), | |
| IF(`update_priv` = 'Y', 'u', '-'), | |
| IF(`delete_priv` = 'Y', 'd', '-'), | |
| IF(`execute_priv` = 'Y', 'e', '-'), | |
| -- 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', 'o', '-'), | |
| IF(`drop_priv` = 'Y', 'd', '-'), | |
| IF(`event_priv` = 'Y', 'e', '-'), | |
| IF(`index_priv` = 'Y', 'i', '-'), | |
| IF(`trigger_priv` = 'Y', 'g', '-'), | |
| IF(`references_priv` = 'Y', 'f', '-'), ' ', | |
| -- DCL | |
| IF(`grant_priv` = 'Y', 'g', '-'), ' ', | |
| -- TCL | |
| IF(`lock_tables_priv` = 'Y', 'l', '-')) 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` | |
| ) | |
| SELECT | |
| * | |
| FROM | |
| `results` | |
| WHERE | |
| @ | |
| ORDER BY | |
| `database`, | |
| `host`, | |
| `user`, | |
| `scope`, | |
| `table`, | |
| `column`, | |
| `routine` | |
| EOT | |
| mysql -u'root' -p |awk -vwidth="$cols" ' | |
| BEGIN { | |
| FS = "\t" | |
| width = width == "" ? 72 : width | |
| pad = 3 | |
| } | |
| { | |
| if(NR == 1) | |
| $0 = toupper($0) | |
| if(NF > ncols) | |
| ncols = NF | |
| r = ++nrows | |
| for(j = 1; j <= NF; j++) { | |
| s = $j | |
| cell[r, j] = s | |
| w = length(s) | |
| if(w > col_max[j]) | |
| col_max[j] = w | |
| } | |
| for(j = NF + 1; j <= ncols; j++) | |
| cell[r, j] = nil | |
| } | |
| END { | |
| if(nrows == 0) | |
| exit | |
| nowrap = (width == 0) | |
| lr = 1 | |
| rowsz[1] = 0 | |
| cmax = 0 | |
| if(!dfs(1)) { | |
| delete rowsz | |
| delete gw | |
| delete row_of | |
| delete pos_of | |
| lr = ncols | |
| cmax = 1 | |
| for(i = 1; i <= ncols; i++) { | |
| rowsz[i] = 1 | |
| row_of[i] = i | |
| pos_of[i] = 1 | |
| if(col_max[i] > gw[1]) | |
| gw[1] = col_max[i] | |
| } | |
| } | |
| offset[1] = 0 | |
| for(c = 2; c <= cmax; c++) { | |
| offset[c] = offset[c - 1] + gw[c - 1] + pad | |
| } | |
| for(i = 1; i <= ncols; i++) { | |
| rr = row_of[i] | |
| cc = pos_of[i] | |
| idx_at[rr, cc] = i | |
| } | |
| for(r = 1; r <= nrows; r++) { | |
| for(rr = 1; rr <= lr; rr++) { | |
| last_cc = 0 | |
| rc = rowsz[rr] + 0 | |
| for(cc = rc; cc >= 1; cc--) { | |
| i = idx_at[rr, cc] | |
| s = (i ? cell[r, i] : nil) | |
| if(s) { | |
| last_cc = cc | |
| break | |
| } | |
| } | |
| if(last_cc == 0) { | |
| print nil | |
| continue | |
| } | |
| out = nil | |
| for(cc = 1; cc <= last_cc; cc++) { | |
| need = offset[cc] + 0 | |
| cur = length(out) | |
| if(cur < need) | |
| out = out sp(need - cur) | |
| i = idx_at[rr, cc] | |
| s = (i ? cell[r, i] : nil) | |
| out = out s | |
| } | |
| print out | |
| } | |
| if(r < nrows && lr > 1) | |
| print nil | |
| } | |
| } | |
| function row_width(idx, m, s, c) { | |
| m = rowsz[idx] + 0 | |
| if(m <= 0) | |
| return 0 | |
| s = 0 | |
| for(c = 1; c <= m; c++) | |
| s += (gw[c] + 0) | |
| if(m > 1) | |
| s += (m - 1) * pad | |
| return s | |
| } | |
| function rows_fit( idx, m, w) { | |
| if(nowrap) | |
| return 1 | |
| for(idx = 1; idx <= lr; idx++) { | |
| m = rowsz[idx] + 0 | |
| if(m > 0) { | |
| w = row_width(idx) | |
| if(w > width && m > 1) | |
| return 0 | |
| } | |
| } | |
| return 1 | |
| } | |
| function dfs(i, p, old_len, old_lr, old_cmax, old_gwp, need) { | |
| if(i > ncols) | |
| return 1 | |
| p = (rowsz[lr] + 0) + 1 | |
| old_len = rowsz[lr] + 0 | |
| old_lr = lr | |
| old_cmax = cmax | |
| old_gwp = gw[p] + 0 | |
| row_of[i] = lr | |
| pos_of[i] = p | |
| rowsz[lr] = p | |
| if(p > cmax) | |
| cmax = p | |
| need = col_max[i] + 0 | |
| if(need > (gw[p] + 0)) | |
| gw[p] = need | |
| if(rows_fit() && dfs(i + 1)) | |
| return 1 | |
| gw[p] = old_gwp | |
| rowsz[lr] = old_len | |
| cmax = old_cmax | |
| delete row_of[i] | |
| delete pos_of[i] | |
| if(old_len > 0) { | |
| lr = lr + 1 | |
| rowsz[lr] = 0 | |
| if(dfs(i)) | |
| return 1 | |
| delete rowsz[lr] | |
| lr = old_lr | |
| } | |
| return 0 | |
| } | |
| function sp(n, s) { | |
| s = nil | |
| while (length(s) < n) | |
| s = s " " | |
| return s | |
| }' | |
| [ ${PIPESTATUS[1]} -eq 0 ] && cat << \EOT | |
| Legend | |
| MASK = DQL DML DDL DCL TCL | |
| DQL = <s>elect, show <v>iew | |
| DML = <i>nsert, <u>pdate, <d>elete, <e>xecute | |
| DDL = <c>reate, create <r>outine, create <t>emporary table, | |
| create <v>iew, <a>lter, alter r<o>utine, <d>rop, <e>vent, | |
| <i>ndex, tri<g>ger, re<f>erences | |
| DCL = <g>rant | |
| TCL = <l>ock tables | |
| EOT | |
| # vi: ft=sh |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment