#!/usr/bin/env python3
# -*- coding: utf-8; py-indent-offset: 4 -*-
#
# Author:  Linuxfabrik GmbH, Zurich, Switzerland
# Contact: info (at) linuxfabrik (dot) ch
#          https://www.linuxfabrik.ch/
# License: The Unlicense, see LICENSE file.

# https://github.com/Linuxfabrik/monitoring-plugins/blob/main/CONTRIBUTING.md

"""See the check's README for more details."""

import argparse
import random
import string
import sys

import lib.args
import lib.base
import lib.db_mysql
import lib.txt
from lib.globals import STATE_OK, STATE_UNKNOWN

__author__ = 'Linuxfabrik GmbH, Zurich/Switzerland'
__version__ = '2026040801'

DESCRIPTION = """Checks MySQL/MariaDB user security settings, including accounts with empty passwords,
accounts accessible from any host, and accounts with excessive privileges.
Alerts on insecure account configurations."""

DEFAULT_DEFAULTS_FILE = '/var/spool/icinga2/.my.cnf'
DEFAULT_DEFAULTS_GROUP = 'client'
DEFAULT_SERVERITY = 'warn'
DEFAULT_TIMEOUT = 3


def parse_args():
    """Parse command line arguments using argparse."""
    parser = argparse.ArgumentParser(description=DESCRIPTION)

    parser.add_argument(
        '-V',
        '--version',
        action='version',
        version=f'%(prog)s: v{__version__} by {__author__}',
    )

    parser.add_argument(
        '--always-ok',
        help=lib.args.help('--always-ok'),
        dest='ALWAYS_OK',
        action='store_true',
        default=False,
    )

    parser.add_argument(
        '--defaults-file',
        help='MySQL/MariaDB cnf file to read parameters like user, host and password from (instead of specifying them on the command line). '
        'Example: `/var/spool/icinga2/.my.cnf`. '
        'Default: %(default)s',
        dest='DEFAULTS_FILE',
        default=DEFAULT_DEFAULTS_FILE,
    )

    parser.add_argument(
        '--defaults-group',
        help=lib.args.help('--defaults-group') + ' Default: %(default)s',
        dest='DEFAULTS_GROUP',
        default=DEFAULT_DEFAULTS_GROUP,
    )

    parser.add_argument(
        '--severity',
        help='Severity for alerts that do not depend on thresholds. One of "warn" or "crit". '
        'Default: %(default)s',
        dest='SEVERITY',
        default=DEFAULT_SERVERITY,
        choices=['warn', 'crit'],
    )

    parser.add_argument(
        '--timeout',
        help=lib.args.help('--timeout') + ' Default: %(default)s (seconds)',
        dest='TIMEOUT',
        type=int,
        default=DEFAULT_TIMEOUT,
    )

    args, _ = parser.parse_known_args()
    return args


def main():
    """The main function. This is where the magic happens."""

    # logic taken from mysqltuner.pl:security_recommendations(), v1.9.8
    # including variable names

    # parse the command line
    try:
        args = parse_args()
    except SystemExit:
        sys.exit(STATE_UNKNOWN)

    mysql_connection = {
        'defaults_file': args.DEFAULTS_FILE,
        'defaults_group': args.DEFAULTS_GROUP,
        'timeout': args.TIMEOUT,
    }
    conn = lib.base.coe(lib.db_mysql.connect(mysql_connection))
    lib.base.coe(lib.db_mysql.check_select_privileges(conn))

    # init some vars
    msg_header, msg_body = '', ''
    state = STATE_OK

    # Looking for Anonymous users
    sql = """
        select concat(quote(user), "@", quote(host)) as user
        from mysql.user
        where trim(user) = ""
            or user is null;
    """
    users = lib.base.coe(lib.db_mysql.select(conn, sql))
    if len(users):
        state = lib.base.str2state(args.SEVERITY)
        msg_header += (
            f'{len(users)} anonymous user'
            f' {lib.txt.pluralize("account", len(users))}'
            f'{lib.base.state2str(lib.base.str2state(args.SEVERITY), prefix=" ")}'
            f'. '
        )
        msg_body += '\nRemove anonymous users:\n'
        for user in users:
            msg_body += f'* DROP USER {user["user"]};\n'

    # possible password column names:
    pass_column_names = [
        'password',
        'authentication_string',
        'IF(plugin="mysql_native_password", authentication_string, password)',
    ]

    # Looking for users having empty passwords
    # MariaDB 10.4+:
    sql = """
        select concat(quote(user), "@", quote(host)) as user
        from mysql.global_priv
        where user != ""
            and user != "mariadb.sys"
            and user != "mysql.sys"
            and json_contains(priv, '"mysql_native_password"', "$.plugin")
            and json_contains(priv, '""', "$.authentication_string")
            and not json_contains(priv, '"true"', "$.account_locked");
    """
    success, users = lib.db_mysql.select(conn, sql)
    if not success:
        # all other versions:
        # pass_column_name is from a hardcoded list of MySQL-version-specific column names
        for pass_column_name in pass_column_names:
            sql = f"""
                select concat(quote(user), "@", quote(host)) as user
                from mysql.user
                where ({pass_column_name} = "" or {pass_column_name} is null)
                    and user != ""
                    and user != "mariadb.sys"
                    and user != "mysql.sys"
                    /*!50501 and plugin not in ("auth_socket", "unix_socket", "win_socket", "auth_pam_compat") */
                    /*!80000 and account_locked = "N" and password_expired = "N" */;
            """  # nosec B608
            success, users = lib.db_mysql.select(conn, sql)
            if success:
                break
    if success and len(users):
        state = lib.base.str2state(args.SEVERITY)
        msg_header += (
            f'{len(users)}'
            f' {lib.txt.pluralize("user", len(users))}'
            f' without password'
            f'{lib.base.state2str(state, prefix=" ")}'
            f'. '
        )
        msg_body += '\nSet user passwords:\n'
        for user in users:
            random_pw = ''.join(
                random.choice(string.ascii_letters + string.digits) for i in range(40)
            )
            msg_body += (
                f'* SET PASSWORD FOR {user["user"]} = PASSWORD("{random_pw}");\n'
            )

    # Looking for users with user / uppercase / capitalise user as password
    # does not work on MySQL 8
    # pass_column_name is from a hardcoded list of MySQL-version-specific column names
    for pass_column_name in pass_column_names:
        sql = f"""
            select concat(quote(user), "@", quote(host)) as user
            from mysql.user
            where user != ""
                and user != "mariadb.sys"
                and user != "mysql.sys"
                and (
                    cast({pass_column_name} as binary) = password(user)
                    or cast({pass_column_name} as binary) = password(upper(user))
                    or cast({pass_column_name} as binary) = password(concat(upper(left(user, 1)), substring(user, 2, length(user))))
                );
        """  # nosec B608
        success, users = lib.db_mysql.select(conn, sql)
        if success:
            break
    if success and len(users):
        state = lib.base.str2state(args.SEVERITY)
        msg_header += (
            f'{len(users)}'
            f' {lib.txt.pluralize("user", len(users))}'
            f' with username as password'
            f'{lib.base.state2str(state, prefix=" ")}'
            f'. '
        )
        msg_body += '\nChange user passwords:\n'
        for user in users:
            random_pw = ''.join(
                random.choice(string.ascii_letters + string.digits) for i in range(40)
            )
            msg_body += (
                f'* SET PASSWORD FOR {user["user"]} = PASSWORD("{random_pw}");\n'
            )

    # Looking for users without hostname restriction
    sql = """
        select concat(quote(user), "@", quote(host)) as user
        from mysql.user
        where host = "%";
    """
    users = lib.base.coe(lib.db_mysql.select(conn, sql))
    if len(users):
        state = lib.base.str2state(args.SEVERITY)
        msg_header += (
            f'{len(users)}'
            f' {lib.txt.pluralize("account", len(users))}'
            f' without hostname restriction'
            f'{lib.base.state2str(lib.base.str2state(args.SEVERITY), prefix=" ")}'
            f'. '
        )
        msg_body += '\nRestrict users:\n'
        for user in users:
            renamed = user['user'].replace(
                "'%",
                "'LimitedIPRangeOrLocalhost",
            )
            msg_body += f'* RENAME USER {user["user"]} TO {renamed};\n'

    lib.db_mysql.close(conn)

    msg = msg_header + '\n' + msg_body
    if not msg.strip():
        msg = 'Everything is ok.'

    # over and out
    lib.base.oao(msg, state, always_ok=args.ALWAYS_OK)


if __name__ == '__main__':
    try:
        main()
    except Exception:
        lib.base.cu()
