tlu/salt/modules/_states/mysql.py

import salt.exceptions

def root_password(name, password):
    """
    Set the mysql/mariadb root password

    password
        the password to user for root
    """
    ret = {
            "name": name,
            "changes": {},
            "result": False,
            "comment": ""
        }

    if __salt__["mysql.check_credentials"]("root", password):
        ret["comment"]="Password is in correct state"
        ret["result"] = True
        return ret

    result = __salt__["mysql.set_root_password"](password)
    if not result["result"]:
        ret["comment"] = result["err"]
        return ret

    ret["changes"].update({"root password": {"old": "######", "new": "******"}})
    ret["result"]=True
    return ret


def user(name, host, password):
    """
    Set the mysql/mariadb root password

        password
        the password to user for root
    """
    ret = {
            "name": name,
            "changes": {},
            "result": False,
            "comment": ""
        }

    #check if user@host exists
    result = __salt__["mysql.execute"]("root", __pillar__['mysql']['root_password'],f'SELECT User, Host FROM mysql.user WHERE User="{name}" AND Host="{host}";')
    if not result["result"]:
        ret["comment"] = result["err"]
        return ret
    if "out" in result:
        #User exists check password
        if __salt__["mysql.check_credentials"](name, password):
            ret["comment"]=f"{name}@{host} is in correct state"
            ret["result"] = True
            return ret
        result = __salt__["mysql.execute"]("root", __pillar__['mysql']['root_password'],f'ALTER USER "{name}"@"{host}" IDENTIFIED BY "{password}";')
        if not result["result"]:
            ret["comment"] = result["err"]
            return ret
        ret["changes"].update({"Password Updated": f"{name}@{host}"})
        ret["result"]=True
        return ret
    #Create user
    result = __salt__["mysql.execute"]("root", __pillar__['mysql']['root_password'],f'CREATE USER "{name}"@"{host}" IDENTIFIED BY "{password}";')
    if not result["result"]:
        ret["comment"] = result["err"]
        return ret
    ret["changes"].update({"Created user": f"{name}@{host}"})
    ret["result"]=True
    return ret

def database(name, users=[]):
    """
    Creates database

    name
        name of the database
    users
        list of user dict
            name: username@localhost
            grant: [ALL]

            or
            name: user2@%
            grant: [CREATE, DELETE, DROP]
    """
    ret = {
            "name": name,
            "changes": {},
            "result": False,
            "comment": ""
        }
    #check if db exists
    result = __salt__["mysql.execute"]("root", __pillar__['mysql']['root_password'],f'USE {name};')
    if not result["result"]:
        result = __salt__["mysql.execute"]("root", __pillar__['mysql']['root_password'],f'CREATE DATABASE {name};')
        if not result["result"]:
            ret["comment"] = result["err"]
            return ret
        ret["changes"].update({"Database created": name})
  
    for user in users:
        result = __salt__["mysql.execute"]("root", __pillar__['mysql']['root_password'],f'SHOW GRANTS FOR {user};')
        if not result["result"]:
            ret["comment"] = result["err"]
            return ret
        have_grants = False
        for row in result["out"].decode("utf-8").split("\n"):
            if f" `{name}`.* " in row:
                have_grants = True
                break
        if not have_grants:
            result = __salt__["mysql.execute"]("root", __pillar__['mysql']['root_password'],f'GRANT ALL PRIVILEGES ON {name}.* to {user};')
            if not result["result"]:
                ret["comment"] = result["err"]
                return ret
            ret["changes"].update({user: "grantad ALL privileges"})

    if len(ret["changes"]):
        ret["comment"] = "Changed"
    else:
        ret["comment"] = "Database is in desired state"

    ret["result"] = True
    return ret
            

     


    ret["comment"] = "Database exists"
    ret["result"] = True
    return ret