#! /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