"Fossies" - the Fresh Open Source Software Archive

Member "salt-3002.2/salt/modules/mysql.py" (18 Nov 2020, 88314 Bytes) of package /linux/misc/salt-3002.2.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) Python source code syntax highlighting (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file. For more information about "mysql.py" see the Fossies "Dox" file reference documentation and the latest Fossies "Diffs" side-by-side code changes report: 3002.1_vs_3002.2.

    1 """
    2 Module to provide MySQL compatibility to salt.
    3 
    4 :depends:   - MySQLdb Python module
    5 
    6 .. note::
    7 
    8     On CentOS 5 (and possibly RHEL 5) both MySQL-python and python26-mysqldb
    9     need to be installed.
   10 
   11 :configuration: In order to connect to MySQL, certain configuration is required
   12     in /etc/salt/minion on the relevant minions. Some sample configs might look
   13     like::
   14 
   15         mysql.host: 'localhost'
   16         mysql.port: 3306
   17         mysql.user: 'root'
   18         mysql.pass: ''
   19         mysql.db: 'mysql'
   20         mysql.unix_socket: '/tmp/mysql.sock'
   21         mysql.charset: 'utf8'
   22 
   23     You can also use a defaults file::
   24 
   25         mysql.default_file: '/etc/mysql/debian.cnf'
   26 
   27 .. versionchanged:: 2014.1.0
   28     \'charset\' connection argument added. This is a MySQL charset, not a python one.
   29 .. versionchanged:: 0.16.2
   30     Connection arguments from the minion config file can be overridden on the
   31     CLI by using the arguments defined :mod:`here <salt.states.mysql_user>`.
   32     Additionally, it is now possible to setup a user with no password.
   33 """
   34 
   35 
   36 import copy
   37 import hashlib
   38 import logging
   39 import os
   40 import re
   41 import shlex
   42 import sys
   43 import time
   44 
   45 import salt.utils.data
   46 import salt.utils.files
   47 import salt.utils.stringutils
   48 
   49 # pylint: disable=import-error
   50 from salt.ext.six.moves import range, zip
   51 
   52 try:
   53     # Trying to import MySQLdb
   54     import MySQLdb
   55     import MySQLdb.cursors
   56     import MySQLdb.converters
   57     from MySQLdb.constants import FIELD_TYPE, FLAG
   58     from MySQLdb import OperationalError
   59 except ImportError:
   60     try:
   61         # MySQLdb import failed, try to import PyMySQL
   62         import pymysql
   63 
   64         pymysql.install_as_MySQLdb()
   65         import MySQLdb
   66         import MySQLdb.cursors
   67         import MySQLdb.converters
   68         from MySQLdb.constants import FIELD_TYPE, FLAG
   69         from MySQLdb import OperationalError
   70     except ImportError:
   71         MySQLdb = None
   72 
   73 log = logging.getLogger(__name__)
   74 
   75 # TODO: this is not used anywhere in the code?
   76 __opts__ = {}
   77 
   78 __grants__ = [
   79     "ALL PRIVILEGES",
   80     "ALTER",
   81     "ALTER ROUTINE",
   82     "BACKUP_ADMIN",
   83     "BINLOG_ADMIN",
   84     "CONNECTION_ADMIN",
   85     "CREATE",
   86     "CREATE ROLE",
   87     "CREATE ROUTINE",
   88     "CREATE TABLESPACE",
   89     "CREATE TEMPORARY TABLES",
   90     "CREATE USER",
   91     "CREATE VIEW",
   92     "DELETE",
   93     "DROP",
   94     "DROP ROLE",
   95     "ENCRYPTION_KEY_ADMIN",
   96     "EVENT",
   97     "EXECUTE",
   98     "FILE",
   99     "GRANT OPTION",
  100     "GROUP_REPLICATION_ADMIN",
  101     "INDEX",
  102     "INSERT",
  103     "LOCK TABLES",
  104     "PERSIST_RO_VARIABLES_ADMIN",
  105     "PROCESS",
  106     "REFERENCES",
  107     "RELOAD",
  108     "REPLICATION CLIENT",
  109     "REPLICATION SLAVE",
  110     "REPLICATION_SLAVE_ADMIN",
  111     "RESOURCE_GROUP_ADMIN",
  112     "RESOURCE_GROUP_USER",
  113     "ROLE_ADMIN",
  114     "SELECT",
  115     "SET_USER_ID",
  116     "SHOW DATABASES",
  117     "SHOW VIEW",
  118     "SHUTDOWN",
  119     "SUPER",
  120     "SYSTEM_VARIABLES_ADMIN",
  121     "TRIGGER",
  122     "UPDATE",
  123     "USAGE",
  124     "XA_RECOVER_ADMIN",
  125 ]
  126 
  127 __ssl_options_parameterized__ = ["CIPHER", "ISSUER", "SUBJECT"]
  128 __ssl_options__ = __ssl_options_parameterized__ + ["SSL", "X509"]
  129 
  130 __all_privileges__ = [
  131     "ALTER",
  132     "ALTER ROUTINE",
  133     "BACKUP_ADMIN",
  134     "BINLOG_ADMIN",
  135     "CONNECTION_ADMIN",
  136     "CREATE",
  137     "CREATE ROLE",
  138     "CREATE ROUTINE",
  139     "CREATE TABLESPACE",
  140     "CREATE TEMPORARY TABLES",
  141     "CREATE USER",
  142     "CREATE VIEW",
  143     "DELETE",
  144     "DROP",
  145     "DROP ROLE",
  146     "ENCRYPTION_KEY_ADMIN",
  147     "EVENT",
  148     "EXECUTE",
  149     "FILE",
  150     "GROUP_REPLICATION_ADMIN",
  151     "INDEX",
  152     "INSERT",
  153     "LOCK TABLES",
  154     "PERSIST_RO_VARIABLES_ADMIN",
  155     "PROCESS",
  156     "REFERENCES",
  157     "RELOAD",
  158     "REPLICATION CLIENT",
  159     "REPLICATION SLAVE",
  160     "REPLICATION_SLAVE_ADMIN",
  161     "RESOURCE_GROUP_ADMIN",
  162     "RESOURCE_GROUP_USER",
  163     "ROLE_ADMIN",
  164     "SELECT",
  165     "SET_USER_ID",
  166     "SHOW DATABASES",
  167     "SHOW VIEW",
  168     "SHUTDOWN",
  169     "SUPER",
  170     "SYSTEM_VARIABLES_ADMIN",
  171     "TRIGGER",
  172     "UPDATE",
  173     "XA_RECOVER_ADMIN",
  174 ]
  175 
  176 r'''
  177 DEVELOPER NOTE: ABOUT arguments management, escapes, formats, arguments and
  178 security of SQL.
  179 
  180 A general rule of SQL security is to use queries with _execute call in this
  181 code using args parameter to let MySQLdb manage the arguments proper escaping.
  182 Another way of escaping values arguments could be '{0!r}'.format(), using
  183 __repr__ to ensure things get properly used as strings. But this could lead
  184 to three problems:
  185 
  186  * In ANSI mode, which is available on MySQL, but not by default, double
  187 quotes " should not be used as a string delimiters, in ANSI mode this is an
  188 identifier delimiter (like `).
  189 
  190  * Some rare exploits with bad multibytes management, either on python or
  191 MySQL could defeat this barrier, bindings internal escape functions
  192 should manage theses cases.
  193 
  194  * Unicode strings in Python 2 will include the 'u' before the repr'ed string,
  195    like so:
  196 
  197     Python 2.7.10 (default, May 26 2015, 04:16:29)
  198     [GCC 5.1.0] on linux2
  199     Type "help", "copyright", "credits" or "license" for more information.
  200     >>> u'something something {0!r}'.format(u'foo')
  201     u"something something u'foo'"
  202 
  203 So query with arguments should use a paramstyle defined in PEP249:
  204 
  205 http://www.python.org/dev/peps/pep-0249/#paramstyle
  206 We use pyformat, which means 'SELECT * FROM foo WHERE bar=%(myval)s'
  207 used with {'myval': 'some user input'}
  208 
  209 So far so good. But this cannot be used for identifier escapes. Identifiers
  210 are database names, table names and column names. Theses names are not values
  211 and do not follow the same escape rules (see quote_identifier function for
  212 details on `_ and % escape policies on identifiers). Using value escaping on
  213 identifier could fool the SQL engine (badly escaping quotes and not doubling
  214 ` characters. So for identifiers a call to quote_identifier should be done and
  215 theses identifiers should then be added in strings with format, but without
  216 __repr__ filter.
  217 
  218 Note also that when using query with arguments in _execute all '%' characters
  219 used in the query should get escaped to '%%' fo MySQLdb, but should not be
  220 escaped if the query runs without arguments. This is managed by _execute() and
  221 quote_identifier. This is not the same as escaping '%' to '\%' or '_' to '\%'
  222 when using a LIKE query (example in db_exists), as this escape is there to
  223 avoid having _ or % characters interpreted in LIKE queries. The string parted
  224 of the first query could become (still used with args dictionary for myval):
  225 'SELECT * FROM {0} WHERE bar=%(myval)s'.format(quote_identifier('user input'))
  226 
  227 Check integration tests if you find a hole in theses strings and escapes rules
  228 
  229 Finally some examples to sum up.
  230 
  231 Given a name f_o%o`b'a"r, in python that would be """f_o%o`b'a"r""". I'll
  232 avoid python syntax for clarity:
  233 
  234 The MySQL way of writing this name is:
  235 
  236 value                         : 'f_o%o`b\'a"r' (managed by MySQLdb)
  237 identifier                    : `f_o%o``b'a"r`
  238 db identifier in general GRANT: `f\_o\%o``b'a"r`
  239 db identifier in table GRANT  : `f_o%o``b'a"r`
  240 in mySQLdb, query with args   : `f_o%%o``b'a"r` (as identifier)
  241 in mySQLdb, query without args: `f_o%o``b'a"r` (as identifier)
  242 value in a LIKE query         : 'f\_o\%o`b\'a"r' (quotes managed by MySQLdb)
  243 
  244 And theses could be mixed, in a like query value with args: 'f\_o\%%o`b\'a"r'
  245 '''
  246 
  247 
  248 def __virtual__():
  249     """
  250     Confirm that a python mysql client is installed.
  251     """
  252     return bool(MySQLdb), "No python mysql client installed." if MySQLdb is None else ""
  253 
  254 
  255 def __mysql_hash_password(password):
  256     _password = hashlib.sha1(password.encode()).digest()
  257     _password = "*{}".format(hashlib.sha1(_password).hexdigest().upper())
  258     return _password
  259 
  260 
  261 def __check_table(name, table, **connection_args):
  262     dbc = _connect(**connection_args)
  263     if dbc is None:
  264         return {}
  265     cur = dbc.cursor(MySQLdb.cursors.DictCursor)
  266     s_name = quote_identifier(name)
  267     s_table = quote_identifier(table)
  268     # identifiers cannot be used as values
  269     qry = "CHECK TABLE {}.{}".format(s_name, s_table)
  270     _execute(cur, qry)
  271     results = cur.fetchall()
  272     log.debug(results)
  273     return results
  274 
  275 
  276 def __repair_table(name, table, **connection_args):
  277     dbc = _connect(**connection_args)
  278     if dbc is None:
  279         return {}
  280     cur = dbc.cursor(MySQLdb.cursors.DictCursor)
  281     s_name = quote_identifier(name)
  282     s_table = quote_identifier(table)
  283     # identifiers cannot be used as values
  284     qry = "REPAIR TABLE {}.{}".format(s_name, s_table)
  285     _execute(cur, qry)
  286     results = cur.fetchall()
  287     log.debug(results)
  288     return results
  289 
  290 
  291 def __optimize_table(name, table, **connection_args):
  292     dbc = _connect(**connection_args)
  293     if dbc is None:
  294         return {}
  295     cur = dbc.cursor(MySQLdb.cursors.DictCursor)
  296     s_name = quote_identifier(name)
  297     s_table = quote_identifier(table)
  298     # identifiers cannot be used as values
  299     qry = "OPTIMIZE TABLE {}.{}".format(s_name, s_table)
  300     _execute(cur, qry)
  301     results = cur.fetchall()
  302     log.debug(results)
  303     return results
  304 
  305 
  306 def __password_column(**connection_args):
  307     if "mysql.password_column" in __context__:
  308         return __context__["mysql.password_column"]
  309 
  310     dbc = _connect(**connection_args)
  311     if dbc is None:
  312         return "Password"
  313     cur = dbc.cursor()
  314     qry = (
  315         "SELECT column_name from information_schema.COLUMNS "
  316         "WHERE table_schema=%(schema)s and table_name=%(table)s "
  317         "and column_name=%(column)s"
  318     )
  319     args = {"schema": "mysql", "table": "user", "column": "Password"}
  320     _execute(cur, qry, args)
  321     if int(cur.rowcount) > 0:
  322         __context__["mysql.password_column"] = "Password"
  323     else:
  324         __context__["mysql.password_column"] = "authentication_string"
  325 
  326     return __context__["mysql.password_column"]
  327 
  328 
  329 def __get_auth_plugin(user, host, **connection_args):
  330     dbc = _connect(**connection_args)
  331     if dbc is None:
  332         return []
  333     cur = dbc.cursor(MySQLdb.cursors.DictCursor)
  334     try:
  335         qry = "SELECT plugin FROM mysql.user WHERE User=%(user)s and Host=%(host)s"
  336         args = {"user": user, "host": host}
  337         _execute(cur, qry, args)
  338     except MySQLdb.OperationalError as exc:
  339         err = "MySQL Error {}: {}".format(*exc.args)
  340         __context__["mysql.error"] = err
  341         log.error(err)
  342         return "mysql_native_password"
  343     results = cur.fetchall()
  344     log.debug(results)
  345 
  346     if results:
  347         return results[0].get("plugin", "mysql_native_password")
  348     else:
  349         return "mysql_native_password"
  350 
  351 
  352 def _connect(**kwargs):
  353     """
  354     wrap authentication credentials here
  355     """
  356     connargs = dict()
  357 
  358     def _connarg(name, key=None, get_opts=True):
  359         """
  360         Add key to connargs, only if name exists in our kwargs or,
  361         if get_opts is true, as mysql.<name> in __opts__ or __pillar__
  362 
  363         If get_opts is true, evaluate in said order - kwargs, opts
  364         then pillar. To avoid collision with other functions,
  365         kwargs-based connection arguments are prefixed with 'connection_'
  366         (i.e. 'connection_host', 'connection_user', etc.).
  367         """
  368         if key is None:
  369             key = name
  370 
  371         if name in kwargs:
  372             connargs[key] = kwargs[name]
  373         elif get_opts:
  374             prefix = "connection_"
  375             if name.startswith(prefix):
  376                 try:
  377                     name = name[len(prefix) :]
  378                 except IndexError:
  379                     return
  380             val = __salt__["config.option"]("mysql.{}".format(name), None)
  381             if val is not None:
  382                 connargs[key] = val
  383 
  384     # If a default file is explicitly passed to kwargs, don't grab the
  385     # opts/pillar settings, as it can override info in the defaults file
  386     if "connection_default_file" in kwargs:
  387         get_opts = False
  388     else:
  389         get_opts = True
  390 
  391     _connarg("connection_host", "host", get_opts)
  392     _connarg("connection_user", "user", get_opts)
  393     _connarg("connection_pass", "passwd", get_opts)
  394     _connarg("connection_port", "port", get_opts)
  395     _connarg("connection_db", "db", get_opts)
  396     _connarg("connection_conv", "conv", get_opts)
  397     _connarg("connection_unix_socket", "unix_socket", get_opts)
  398     _connarg("connection_default_file", "read_default_file", get_opts)
  399     _connarg("connection_default_group", "read_default_group", get_opts)
  400     # MySQLdb states that this is required for charset usage
  401     # but in fact it's more than it's internally activated
  402     # when charset is used, activating use_unicode here would
  403     # retrieve utf8 strings as unicode() objects in salt
  404     # and we do not want that.
  405     # _connarg('connection_use_unicode', 'use_unicode')
  406     connargs["use_unicode"] = False
  407     _connarg("connection_charset", "charset")
  408     # Ensure MySQldb knows the format we use for queries with arguments
  409     MySQLdb.paramstyle = "pyformat"
  410 
  411     for key in copy.deepcopy(connargs):
  412         if not connargs[key]:
  413             del connargs[key]
  414 
  415     if (
  416         connargs.get("passwd", True) is None
  417     ):  # If present but set to None. (Extreme edge case.)
  418         log.warning("MySQL password of None found. Attempting passwordless login.")
  419         connargs.pop("passwd")
  420     try:
  421         dbc = MySQLdb.connect(**connargs)
  422     except OperationalError as exc:
  423         err = "MySQL Error {}: {}".format(*exc.args)
  424         __context__["mysql.error"] = err
  425         log.error(err)
  426         return None
  427     except MySQLdb.err.InternalError as exc:
  428         err = "MySQL Error {}: {}".format(*exc.args)
  429         __context__["mysql.error"] = err
  430         log.error(err)
  431         return None
  432 
  433     dbc.autocommit(True)
  434     return dbc
  435 
  436 
  437 def _grant_to_tokens(grant):
  438     """
  439 
  440     This should correspond fairly closely to the YAML rendering of a
  441     mysql_grants state which comes out as follows:
  442 
  443      OrderedDict([
  444         ('whatever_identifier',
  445          OrderedDict([
  446             ('mysql_grants.present',
  447              [
  448               OrderedDict([('database', 'testdb.*')]),
  449               OrderedDict([('user', 'testuser')]),
  450               OrderedDict([('grant', 'ALTER, SELECT, LOCK TABLES')]),
  451               OrderedDict([('host', 'localhost')])
  452              ]
  453             )
  454          ])
  455         )
  456      ])
  457 
  458     :param grant: An un-parsed MySQL GRANT statement str, like
  459         "GRANT SELECT, ALTER, LOCK TABLES ON `mydb`.* TO 'testuser'@'localhost'"
  460         or a dictionary with 'qry' and 'args' keys for 'user' and 'host'.
  461     :return:
  462         A Python dict with the following keys/values:
  463             - user: MySQL User
  464             - host: MySQL host
  465             - grant: [grant1, grant2] (ala SELECT, USAGE, etc)
  466             - database: MySQL DB
  467     """
  468     log.debug("_grant_to_tokens entry '%s'", grant)
  469     dict_mode = False
  470     if isinstance(grant, dict):
  471         dict_mode = True
  472         # Everything coming in dictionary form was made for a MySQLdb execute
  473         # call and contain a '%%' escaping of '%' characters for MySQLdb
  474         # that we should remove here.
  475         grant_sql = grant.get("qry", "undefined").replace("%%", "%")
  476         sql_args = grant.get("args", {})
  477         host = sql_args.get("host", "undefined")
  478         user = sql_args.get("user", "undefined")
  479     else:
  480         grant_sql = grant
  481         user = ""
  482     # the replace part is for presence of ` character in the db name
  483     # the shell escape is \` but mysql escape is ``. Spaces should not be
  484     # exploded as users or db names could contain spaces.
  485     # Examples of splitting:
  486     # "GRANT SELECT, LOCK TABLES, UPDATE, CREATE ON `test ``(:=saltdb)`.*
  487     #                                   TO 'foo'@'localhost' WITH GRANT OPTION"
  488     # ['GRANT', 'SELECT', ',', 'LOCK', 'TABLES', ',', 'UPDATE', ',', 'CREATE',
  489     #  'ON', '`test `', '`(:=saltdb)`', '.', '*', 'TO', "'foo'", '@',
  490     # "'localhost'", 'WITH', 'GRANT', 'OPTION']
  491     #
  492     # 'GRANT SELECT, INSERT, UPDATE, CREATE ON `te s.t\'"sa;ltdb`.`tbl ``\'"xx`
  493     #                                   TO \'foo \' bar\'@\'localhost\''
  494     # ['GRANT', 'SELECT', ',', 'INSERT', ',', 'UPDATE', ',', 'CREATE', 'ON',
  495     #  '`te s.t\'"sa;ltdb`', '.', '`tbl `', '`\'"xx`', 'TO', "'foo '", "bar'",
  496     #  '@', "'localhost'"]
  497     #
  498     # "GRANT USAGE ON *.* TO 'user \";--,?:&/\\'@'localhost'"
  499     # ['GRANT', 'USAGE', 'ON', '*', '.', '*', 'TO', '\'user ";--,?:&/\\\'',
  500     #  '@', "'localhost'"]
  501     lex = shlex.shlex(grant_sql)
  502     lex.quotes = "'`"
  503     lex.whitespace_split = False
  504     lex.commenters = ""
  505     lex.wordchars += '"'
  506     exploded_grant = list(lex)
  507     grant_tokens = []
  508     multiword_statement = []
  509     position_tracker = 1  # Skip the initial 'GRANT' word token
  510     database = ""
  511     phrase = "grants"
  512     column = False
  513     current_grant = ""
  514 
  515     for token in exploded_grant[position_tracker:]:
  516 
  517         if token == "," and phrase == "grants":
  518             position_tracker += 1
  519             continue
  520 
  521         if token == "(" and phrase == "grants":
  522             position_tracker += 1
  523             column = True
  524             continue
  525 
  526         if token == ")" and phrase == "grants":
  527             position_tracker += 1
  528             column = False
  529             continue
  530 
  531         if token == "ON" and phrase == "grants":
  532             phrase = "db"
  533             position_tracker += 1
  534             continue
  535 
  536         elif token == "TO" and phrase == "tables":
  537             phrase = "user"
  538             position_tracker += 1
  539             continue
  540 
  541         elif token == "@" and phrase == "pre-host":
  542             phrase = "host"
  543             position_tracker += 1
  544             continue
  545 
  546         if phrase == "grants":
  547             # Read-ahead
  548             if (
  549                 exploded_grant[position_tracker + 1] == ","
  550                 or exploded_grant[position_tracker + 1] == "ON"
  551                 or exploded_grant[position_tracker + 1] in ["(", ")"]
  552             ):
  553                 # End of token detected
  554                 if multiword_statement:
  555                     multiword_statement.append(token)
  556                     grant_tokens.append(" ".join(multiword_statement))
  557                     multiword_statement = []
  558                 else:
  559                     if not column:
  560                         current_grant = token
  561                     else:
  562                         token = "{}.{}".format(current_grant, token)
  563                     grant_tokens.append(token)
  564             else:  # This is a multi-word, ala LOCK TABLES
  565                 multiword_statement.append(token)
  566 
  567         elif phrase == "db":
  568             # the shlex splitter may have split on special database characters `
  569             database += token
  570             # Read-ahead
  571             try:
  572                 if exploded_grant[position_tracker + 1] == ".":
  573                     phrase = "tables"
  574             except IndexError:
  575                 break
  576 
  577         elif phrase == "tables":
  578             database += token
  579 
  580         elif phrase == "user":
  581             if dict_mode:
  582                 break
  583             else:
  584                 user += token
  585                 # Read-ahead
  586                 if exploded_grant[position_tracker + 1] == "@":
  587                     phrase = "pre-host"
  588 
  589         elif phrase == "host":
  590             host = token
  591             break
  592 
  593         position_tracker += 1
  594 
  595     try:
  596         if not dict_mode:
  597             user = user.strip("'")
  598             host = host.strip("'")
  599         log.debug(
  600             "grant to token '%s'::'%s'::'%s'::'%s'", user, host, grant_tokens, database
  601         )
  602     except UnboundLocalError:
  603         host = ""
  604 
  605     return dict(user=user, host=host, grant=grant_tokens, database=database)
  606 
  607 
  608 def quote_identifier(identifier, for_grants=False):
  609     r"""
  610     Return an identifier name (column, table, database, etc) escaped for MySQL
  611 
  612     This means surrounded by "`" character and escaping this character inside.
  613     It also means doubling the '%' character for MySQLdb internal usage.
  614 
  615     :param identifier: the table, column or database identifier
  616 
  617     :param for_grants: is False by default, when using database names on grant
  618      queries you should set it to True to also escape "_" and "%" characters as
  619      requested by MySQL. Note that theses characters should only be escaped when
  620      requesting grants on the database level (`my\_\%db`.*) but not for table
  621      level grants (`my_%db`.`foo`)
  622 
  623     CLI Example:
  624 
  625     .. code-block:: bash
  626 
  627         salt '*' mysql.quote_identifier 'foo`bar'
  628     """
  629     if for_grants:
  630         return (
  631             "`"
  632             + identifier.replace("`", "``").replace("_", r"\_").replace("%", r"%%")
  633             + "`"
  634         )
  635     else:
  636         return "`" + identifier.replace("`", "``").replace("%", "%%") + "`"
  637 
  638 
  639 def _execute(cur, qry, args=None):
  640     """
  641     Internal wrapper around MySQLdb cursor.execute() function
  642 
  643     MySQLDb does not apply the same filters when arguments are used with the
  644     query. For example '%' characters on the query must be encoded as '%%' and
  645     will be restored as '%' when arguments are applied. But when there're no
  646     arguments the '%%' is not managed. We cannot apply Identifier quoting in a
  647     predictable way if the query are not always applying the same filters. So
  648     this wrapper ensure this escape is not made if no arguments are used.
  649     """
  650     if args is None or args == {}:
  651         qry = qry.replace("%%", "%")
  652         log.debug("Doing query: %s", qry)
  653         return cur.execute(qry)
  654     else:
  655         log.debug("Doing query: %s args: %s ", qry, repr(args))
  656         return cur.execute(qry, args)
  657 
  658 
  659 def _sanitize_comments(content):
  660     # Remove comments which might affect line by line parsing
  661     # Regex should remove any text beginning with # (or --) not inside of ' or "
  662     content = re.sub(
  663         r"""(['"](?:[^'"]+|(?<=\\)['"])*['"])|#[^\n]*""",
  664         lambda m: m.group(1) or "",
  665         content,
  666         re.S,
  667     )
  668     content = re.sub(
  669         r"""(['"](?:[^'"]+|(?<=\\)['"])*['"])|--[^\n]*""",
  670         lambda m: m.group(1) or "",
  671         content,
  672         re.S,
  673     )
  674     cleaned = ""
  675     for line in content.splitlines():
  676         line = line.strip()
  677         if line != "":
  678             cleaned += line + "\n"
  679     return cleaned
  680 
  681 
  682 def query(database, query, **connection_args):
  683     """
  684     Run an arbitrary SQL query and return the results or
  685     the number of affected rows.
  686 
  687     CLI Example:
  688 
  689     .. code-block:: bash
  690 
  691         salt '*' mysql.query mydb "UPDATE mytable set myfield=1 limit 1"
  692 
  693     Return data:
  694 
  695     .. code-block:: python
  696 
  697         {'query time': {'human': '39.0ms', 'raw': '0.03899'}, 'rows affected': 1L}
  698 
  699     CLI Example:
  700 
  701     .. code-block:: bash
  702 
  703         salt '*' mysql.query mydb "SELECT id,name,cash from users limit 3"
  704 
  705     Return data:
  706 
  707     .. code-block:: python
  708 
  709         {'columns': ('id', 'name', 'cash'),
  710             'query time': {'human': '1.0ms', 'raw': '0.001'},
  711             'results': ((1L, 'User 1', Decimal('110.000000')),
  712                         (2L, 'User 2', Decimal('215.636756')),
  713                         (3L, 'User 3', Decimal('0.040000'))),
  714             'rows returned': 3L}
  715 
  716     CLI Example:
  717 
  718     .. code-block:: bash
  719 
  720         salt '*' mysql.query mydb 'INSERT into users values (null,"user 4", 5)'
  721 
  722     Return data:
  723 
  724     .. code-block:: python
  725 
  726         {'query time': {'human': '25.6ms', 'raw': '0.02563'}, 'rows affected': 1L}
  727 
  728     CLI Example:
  729 
  730     .. code-block:: bash
  731 
  732         salt '*' mysql.query mydb 'DELETE from users where id = 4 limit 1'
  733 
  734     Return data:
  735 
  736     .. code-block:: python
  737 
  738         {'query time': {'human': '39.0ms', 'raw': '0.03899'}, 'rows affected': 1L}
  739 
  740     Jinja Example: Run a query on ``mydb`` and use row 0, column 0's data.
  741 
  742     .. code-block:: jinja
  743 
  744         {{ salt['mysql.query']('mydb', 'SELECT info from mytable limit 1')['results'][0][0] }}
  745     """
  746     # Doesn't do anything about sql warnings, e.g. empty values on an insert.
  747     # I don't think it handles multiple queries at once, so adding "commit"
  748     # might not work.
  749 
  750     # The following 3 lines stops MySQLdb from converting the MySQL results
  751     # into Python objects. It leaves them as strings.
  752     orig_conv = MySQLdb.converters.conversions
  753     conv_iter = iter(orig_conv)
  754     conv = dict(zip(conv_iter, [str] * len(orig_conv)))
  755 
  756     # some converters are lists, do not break theses
  757     conv_mysqldb = {"MYSQLDB": True}
  758     if conv_mysqldb.get(MySQLdb.__package__.upper()):
  759         conv[FIELD_TYPE.BLOB] = [
  760             (FLAG.BINARY, str),
  761         ]
  762         conv[FIELD_TYPE.STRING] = [
  763             (FLAG.BINARY, str),
  764         ]
  765         conv[FIELD_TYPE.VAR_STRING] = [
  766             (FLAG.BINARY, str),
  767         ]
  768         conv[FIELD_TYPE.VARCHAR] = [
  769             (FLAG.BINARY, str),
  770         ]
  771 
  772     connection_args.update({"connection_db": database, "connection_conv": conv})
  773     dbc = _connect(**connection_args)
  774     if dbc is None:
  775         return {}
  776     cur = dbc.cursor()
  777     start = time.time()
  778     log.debug("Using db: %s to run query %s", database, query)
  779     try:
  780         affected = _execute(cur, query)
  781     except OperationalError as exc:
  782         err = "MySQL Error {}: {}".format(*exc.args)
  783         __context__["mysql.error"] = err
  784         log.error(err)
  785         return False
  786     results = cur.fetchall()
  787     elapsed = time.time() - start
  788     if elapsed < 0.200:
  789         elapsed_h = str(round(elapsed * 1000, 1)) + "ms"
  790     else:
  791         elapsed_h = str(round(elapsed, 2)) + "s"
  792 
  793     ret = {}
  794     ret["query time"] = {"human": elapsed_h, "raw": str(round(elapsed, 5))}
  795     select_keywords = ["SELECT", "SHOW", "DESC"]
  796     select_query = False
  797     for keyword in select_keywords:
  798         if query.upper().strip().startswith(keyword):
  799             select_query = True
  800             break
  801     if select_query:
  802         ret["rows returned"] = affected
  803         columns = ()
  804         for column in cur.description:
  805             columns += (column[0],)
  806         ret["columns"] = columns
  807         ret["results"] = results
  808         return ret
  809     else:
  810         ret["rows affected"] = affected
  811         return ret
  812 
  813 
  814 def file_query(database, file_name, **connection_args):
  815     """
  816     Run an arbitrary SQL query from the specified file and return the
  817     the number of affected rows.
  818 
  819     .. versionadded:: 2017.7.0
  820 
  821     database
  822 
  823         database to run script inside
  824 
  825     file_name
  826 
  827         File name of the script.  This can be on the minion, or a file that is reachable by the fileserver
  828 
  829     CLI Example:
  830 
  831     .. code-block:: bash
  832 
  833         salt '*' mysql.file_query mydb file_name=/tmp/sqlfile.sql
  834         salt '*' mysql.file_query mydb file_name=salt://sqlfile.sql
  835 
  836     Return data:
  837 
  838     .. code-block:: python
  839 
  840         {'query time': {'human': '39.0ms', 'raw': '0.03899'}, 'rows affected': 1L}
  841 
  842     """
  843     if any(
  844         file_name.startswith(proto)
  845         for proto in ("salt://", "http://", "https://", "swift://", "s3://")
  846     ):
  847         file_name = __salt__["cp.cache_file"](file_name)
  848 
  849     if os.path.exists(file_name):
  850         with salt.utils.files.fopen(file_name, "r") as ifile:
  851             contents = salt.utils.stringutils.to_unicode(ifile.read())
  852     else:
  853         log.error('File "%s" does not exist', file_name)
  854         return False
  855 
  856     query_string = ""
  857     ret = {
  858         "rows returned": 0,
  859         "columns": [],
  860         "results": [],
  861         "rows affected": 0,
  862         "query time": {"raw": 0},
  863     }
  864 
  865     contents = _sanitize_comments(contents)
  866     # Walk the each line of the sql file to get accurate row affected results
  867     for line in contents.splitlines():
  868         if not re.search(r"[^-;]+;", line):  # keep appending lines that don't end in ;
  869             query_string = query_string + line
  870         else:
  871             query_string = (
  872                 query_string + line
  873             )  # append lines that end with ; and run query
  874             query_result = query(database, query_string, **connection_args)
  875             query_string = ""
  876 
  877             if query_result is False:
  878                 # Fail out on error
  879                 return False
  880 
  881             if "query time" in query_result:
  882                 ret["query time"]["raw"] += float(query_result["query time"]["raw"])
  883             if "rows returned" in query_result:
  884                 ret["rows returned"] += query_result["rows returned"]
  885             if "columns" in query_result:
  886                 ret["columns"].append(query_result["columns"])
  887             if "results" in query_result:
  888                 ret["results"].append(query_result["results"])
  889             if "rows affected" in query_result:
  890                 ret["rows affected"] += query_result["rows affected"]
  891 
  892     ret["query time"]["human"] = str(round(float(ret["query time"]["raw"]), 2)) + "s"
  893     ret["query time"]["raw"] = round(float(ret["query time"]["raw"]), 5)
  894 
  895     # Remove empty keys in ret
  896     ret = {k: v for k, v in ret.items() if v}
  897 
  898     return ret
  899 
  900 
  901 def status(**connection_args):
  902     """
  903     Return the status of a MySQL server using the output from the ``SHOW
  904     STATUS`` query.
  905 
  906     CLI Example:
  907 
  908     .. code-block:: bash
  909 
  910         salt '*' mysql.status
  911     """
  912     dbc = _connect(**connection_args)
  913     if dbc is None:
  914         return {}
  915     cur = dbc.cursor()
  916     qry = "SHOW STATUS"
  917     try:
  918         _execute(cur, qry)
  919     except OperationalError as exc:
  920         err = "MySQL Error {}: {}".format(*exc.args)
  921         __context__["mysql.error"] = err
  922         log.error(err)
  923         return {}
  924 
  925     ret = {}
  926     for _ in range(cur.rowcount):
  927         row = cur.fetchone()
  928         ret[row[0]] = row[1]
  929     return ret
  930 
  931 
  932 def version(**connection_args):
  933     """
  934     Return the version of a MySQL server using the output from the ``SELECT
  935     VERSION()`` query.
  936 
  937     CLI Example:
  938 
  939     .. code-block:: bash
  940 
  941         salt '*' mysql.version
  942     """
  943     if "mysql.version" in __context__:
  944         return __context__["mysql.version"]
  945 
  946     dbc = _connect(**connection_args)
  947     if dbc is None:
  948         return ""
  949     cur = dbc.cursor()
  950     qry = "SELECT VERSION()"
  951     try:
  952         _execute(cur, qry)
  953     except MySQLdb.OperationalError as exc:
  954         err = "MySQL Error {}: {}".format(*exc.args)
  955         __context__["mysql.error"] = err
  956         log.error(err)
  957         return ""
  958 
  959     try:
  960         __context__["mysql.version"] = salt.utils.data.decode(cur.fetchone()[0])
  961         return __context__["mysql.version"]
  962     except IndexError:
  963         return ""
  964 
  965 
  966 def slave_lag(**connection_args):
  967     """
  968     Return the number of seconds that a slave SQL server is lagging behind the
  969     master, if the host is not a slave it will return -1.  If the server is
  970     configured to be a slave for replication but slave IO is not running then
  971     -2 will be returned. If there was an error connecting to the database or
  972     checking the slave status, -3 will be returned.
  973 
  974     CLI Example:
  975 
  976     .. code-block:: bash
  977 
  978         salt '*' mysql.slave_lag
  979     """
  980     dbc = _connect(**connection_args)
  981     if dbc is None:
  982         return -3
  983     cur = dbc.cursor(MySQLdb.cursors.DictCursor)
  984     qry = "show slave status"
  985     try:
  986         _execute(cur, qry)
  987     except MySQLdb.OperationalError as exc:
  988         err = "MySQL Error {}: {}".format(*exc.args)
  989         __context__["mysql.error"] = err
  990         log.error(err)
  991         return -3
  992 
  993     results = cur.fetchone()
  994     if cur.rowcount == 0:
  995         # Server is not a slave if master is not defined.  Return empty tuple
  996         # in this case.  Could probably check to see if Slave_IO_Running and
  997         # Slave_SQL_Running are both set to 'Yes' as well to be really really
  998         # sure that it is a slave.
  999         return -1
 1000     else:
 1001         if results["Slave_IO_Running"] == "Yes":
 1002             return results["Seconds_Behind_Master"]
 1003         else:
 1004             # Replication is broken if you get here.
 1005             return -2
 1006 
 1007 
 1008 def free_slave(**connection_args):
 1009     """
 1010     Frees a slave from its master.  This is a WIP, do not use.
 1011 
 1012     CLI Example:
 1013 
 1014     .. code-block:: bash
 1015 
 1016         salt '*' mysql.free_slave
 1017     """
 1018     slave_db = _connect(**connection_args)
 1019     if slave_db is None:
 1020         return ""
 1021     slave_cur = slave_db.cursor(MySQLdb.cursors.DictCursor)
 1022     slave_cur.execute("show slave status")
 1023     slave_status = slave_cur.fetchone()
 1024     master = {"host": slave_status["Master_Host"]}
 1025 
 1026     try:
 1027         # Try to connect to the master and flush logs before promoting to
 1028         # master.  This may fail if the master is no longer available.
 1029         # I am also assuming that the admin password is the same on both
 1030         # servers here, and only overriding the host option in the connect
 1031         # function.
 1032         master_db = _connect(**master)
 1033         if master_db is None:
 1034             return ""
 1035         master_cur = master_db.cursor()
 1036         master_cur.execute("flush logs")
 1037         master_db.close()
 1038     except MySQLdb.OperationalError:
 1039         pass
 1040 
 1041     slave_cur.execute("stop slave")
 1042     slave_cur.execute("reset master")
 1043     slave_cur.execute("change master to MASTER_HOST=" "")
 1044     slave_cur.execute("show slave status")
 1045     results = slave_cur.fetchone()
 1046 
 1047     if results is None:
 1048         return "promoted"
 1049     else:
 1050         return "failed"
 1051 
 1052 
 1053 # Database related actions
 1054 def db_list(**connection_args):
 1055     """
 1056     Return a list of databases of a MySQL server using the output
 1057     from the ``SHOW DATABASES`` query.
 1058 
 1059     CLI Example:
 1060 
 1061     .. code-block:: bash
 1062 
 1063         salt '*' mysql.db_list
 1064     """
 1065     dbc = _connect(**connection_args)
 1066     if dbc is None:
 1067         return []
 1068     cur = dbc.cursor()
 1069     qry = "SHOW DATABASES"
 1070     try:
 1071         _execute(cur, qry)
 1072     except MySQLdb.OperationalError as exc:
 1073         err = "MySQL Error {}: {}".format(*exc.args)
 1074         __context__["mysql.error"] = err
 1075         log.error(err)
 1076         return []
 1077 
 1078     ret = []
 1079     results = cur.fetchall()
 1080     for dbs in results:
 1081         ret.append(dbs[0])
 1082 
 1083     log.debug(ret)
 1084     return ret
 1085 
 1086 
 1087 def alter_db(name, character_set=None, collate=None, **connection_args):
 1088     """
 1089     Modify database using ``ALTER DATABASE %(dbname)s CHARACTER SET %(charset)s
 1090     COLLATE %(collation)s;`` query.
 1091 
 1092     CLI Example:
 1093 
 1094     .. code-block:: bash
 1095 
 1096         salt '*' mysql.alter_db testdb charset='latin1'
 1097     """
 1098     dbc = _connect(**connection_args)
 1099     if dbc is None:
 1100         return []
 1101     cur = dbc.cursor()
 1102     existing = db_get(name, **connection_args)
 1103     qry = "ALTER DATABASE `{}` CHARACTER SET {} COLLATE {};".format(
 1104         name.replace("%", r"\%").replace("_", r"\_"),
 1105         character_set or existing.get("character_set"),
 1106         collate or existing.get("collate"),
 1107     )
 1108     args = {}
 1109     _execute(cur, qry, args)
 1110 
 1111 
 1112 def db_get(name, **connection_args):
 1113     """
 1114     Return a list of databases of a MySQL server using the output
 1115     from the ``SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM
 1116     INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='dbname';`` query.
 1117 
 1118     CLI Example:
 1119 
 1120     .. code-block:: bash
 1121 
 1122         salt '*' mysql.db_get test
 1123     """
 1124     dbc = _connect(**connection_args)
 1125     if dbc is None:
 1126         return []
 1127     cur = dbc.cursor()
 1128     qry = (
 1129         "SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM "
 1130         "INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=%(dbname)s;"
 1131     )
 1132     args = {"dbname": name}
 1133     _execute(cur, qry, args)
 1134     if cur.rowcount:
 1135         rows = cur.fetchall()
 1136         return {"character_set": rows[0][0], "collate": rows[0][1]}
 1137     return {}
 1138 
 1139 
 1140 def db_tables(name, **connection_args):
 1141     """
 1142     Shows the tables in the given MySQL database (if exists)
 1143 
 1144     CLI Example:
 1145 
 1146     .. code-block:: bash
 1147 
 1148         salt '*' mysql.db_tables 'database'
 1149     """
 1150     if not db_exists(name, **connection_args):
 1151         log.info("Database '%s' does not exist", name)
 1152         return False
 1153 
 1154     dbc = _connect(**connection_args)
 1155     if dbc is None:
 1156         return []
 1157     cur = dbc.cursor()
 1158     s_name = quote_identifier(name)
 1159     # identifiers cannot be used as values
 1160     qry = "SHOW TABLES IN {}".format(s_name)
 1161     try:
 1162         _execute(cur, qry)
 1163     except MySQLdb.OperationalError as exc:
 1164         err = "MySQL Error {}: {}".format(*exc.args)
 1165         __context__["mysql.error"] = err
 1166         log.error(err)
 1167         return []
 1168 
 1169     ret = []
 1170     results = cur.fetchall()
 1171     for table in results:
 1172         ret.append(table[0])
 1173     log.debug(ret)
 1174     return ret
 1175 
 1176 
 1177 def db_exists(name, **connection_args):
 1178     """
 1179     Checks if a database exists on the MySQL server.
 1180 
 1181     CLI Example:
 1182 
 1183     .. code-block:: bash
 1184 
 1185         salt '*' mysql.db_exists 'dbname'
 1186     """
 1187     dbc = _connect(**connection_args)
 1188     if dbc is None:
 1189         return False
 1190     cur = dbc.cursor()
 1191     # Warn: here db identifier is not backtyped but should be
 1192     #  escaped as a string value. Note also that LIKE special characters
 1193     # '_' and '%' should also be escaped.
 1194     args = {"dbname": name}
 1195     qry = "SHOW DATABASES LIKE %(dbname)s;"
 1196     try:
 1197         _execute(cur, qry, args)
 1198     except MySQLdb.OperationalError as exc:
 1199         err = "MySQL Error {}: {}".format(*exc.args)
 1200         __context__["mysql.error"] = err
 1201         log.error(err)
 1202         return False
 1203     cur.fetchall()
 1204     return cur.rowcount == 1
 1205 
 1206 
 1207 def db_create(name, character_set=None, collate=None, **connection_args):
 1208     """
 1209     Adds a databases to the MySQL server.
 1210 
 1211     name
 1212         The name of the database to manage
 1213 
 1214     character_set
 1215         The character set, if left empty the MySQL default will be used
 1216 
 1217     collate
 1218         The collation, if left empty the MySQL default will be used
 1219 
 1220     CLI Example:
 1221 
 1222     .. code-block:: bash
 1223 
 1224         salt '*' mysql.db_create 'dbname'
 1225         salt '*' mysql.db_create 'dbname' 'utf8' 'utf8_general_ci'
 1226     """
 1227     # check if db exists
 1228     if db_exists(name, **connection_args):
 1229         log.info("DB '%s' already exists", name)
 1230         return False
 1231 
 1232     # db doesn't exist, proceed
 1233     dbc = _connect(**connection_args)
 1234     if dbc is None:
 1235         return False
 1236     cur = dbc.cursor()
 1237     s_name = quote_identifier(name)
 1238     # identifiers cannot be used as values
 1239     qry = "CREATE DATABASE IF NOT EXISTS {}".format(s_name)
 1240     args = {}
 1241     if character_set is not None:
 1242         qry += " CHARACTER SET %(character_set)s"
 1243         args["character_set"] = character_set
 1244     if collate is not None:
 1245         qry += " COLLATE %(collate)s"
 1246         args["collate"] = collate
 1247     qry += ";"
 1248 
 1249     try:
 1250         if _execute(cur, qry, args):
 1251             log.info("DB '%s' created", name)
 1252             return True
 1253     except MySQLdb.OperationalError as exc:
 1254         err = "MySQL Error {}: {}".format(*exc.args)
 1255         __context__["mysql.error"] = err
 1256         log.error(err)
 1257     return False
 1258 
 1259 
 1260 def db_remove(name, **connection_args):
 1261     """
 1262     Removes a databases from the MySQL server.
 1263 
 1264     CLI Example:
 1265 
 1266     .. code-block:: bash
 1267 
 1268         salt '*' mysql.db_remove 'dbname'
 1269     """
 1270     # check if db exists
 1271     if not db_exists(name, **connection_args):
 1272         log.info("DB '%s' does not exist", name)
 1273         return False
 1274 
 1275     if name in ("mysql", "information_scheme"):
 1276         log.info("DB '%s' may not be removed", name)
 1277         return False
 1278 
 1279     # db does exists, proceed
 1280     dbc = _connect(**connection_args)
 1281     if dbc is None:
 1282         return False
 1283     cur = dbc.cursor()
 1284     s_name = quote_identifier(name)
 1285     # identifiers cannot be used as values
 1286     qry = "DROP DATABASE {};".format(s_name)
 1287     try:
 1288         _execute(cur, qry)
 1289     except MySQLdb.OperationalError as exc:
 1290         err = "MySQL Error {}: {}".format(*exc.args)
 1291         __context__["mysql.error"] = err
 1292         log.error(err)
 1293         return False
 1294 
 1295     if not db_exists(name, **connection_args):
 1296         log.info("Database '%s' has been removed", name)
 1297         return True
 1298 
 1299     log.info("Database '%s' has not been removed", name)
 1300     return False
 1301 
 1302 
 1303 # User related actions
 1304 def user_list(**connection_args):
 1305     """
 1306     Return a list of users on a MySQL server
 1307 
 1308     CLI Example:
 1309 
 1310     .. code-block:: bash
 1311 
 1312         salt '*' mysql.user_list
 1313     """
 1314     dbc = _connect(**connection_args)
 1315     if dbc is None:
 1316         return []
 1317     cur = dbc.cursor(MySQLdb.cursors.DictCursor)
 1318     try:
 1319         qry = "SELECT User,Host FROM mysql.user"
 1320         _execute(cur, qry)
 1321     except MySQLdb.OperationalError as exc:
 1322         err = "MySQL Error {}: {}".format(*exc.args)
 1323         __context__["mysql.error"] = err
 1324         log.error(err)
 1325         return []
 1326     results = cur.fetchall()
 1327     log.debug(results)
 1328     return results
 1329 
 1330 
 1331 def _mysql_user_exists(
 1332     user,
 1333     host="localhost",
 1334     password=None,
 1335     password_hash=None,
 1336     passwordless=False,
 1337     unix_socket=False,
 1338     password_column=None,
 1339     auth_plugin="mysql_native_password",
 1340     **connection_args
 1341 ):
 1342 
 1343     server_version = salt.utils.data.decode(version(**connection_args))
 1344     compare_version = "8.0.11"
 1345     qry = (
 1346         "SELECT User,Host FROM mysql.user WHERE User = %(user)s AND " "Host = %(host)s"
 1347     )
 1348     args = {}
 1349     args["user"] = user
 1350     args["host"] = host
 1351 
 1352     if salt.utils.data.is_true(passwordless):
 1353         if salt.utils.data.is_true(unix_socket):
 1354             qry += " AND plugin=%(unix_socket)s"
 1355             args["unix_socket"] = "auth_socket"
 1356         else:
 1357             qry += " AND " + password_column + " = ''"
 1358     elif password:
 1359         if salt.utils.versions.version_cmp(server_version, compare_version) >= 0:
 1360             if auth_plugin == "mysql_native_password":
 1361                 _password = __mysql_hash_password(str(password))
 1362                 qry += " AND " + password_column + " = %(password)s"
 1363                 args["password"] = str(_password)
 1364             else:
 1365                 err = "Unable to verify password."
 1366                 log.error(err)
 1367                 __context__["mysql.error"] = err
 1368         else:
 1369             qry += " AND " + password_column + " = PASSWORD(%(password)s)"
 1370             args["password"] = str(password)
 1371     elif password_hash:
 1372         qry += " AND " + password_column + " = %(password)s"
 1373         args["password"] = password_hash
 1374 
 1375     return qry, args
 1376 
 1377 
 1378 def _mariadb_user_exists(
 1379     user,
 1380     host="localhost",
 1381     password=None,
 1382     password_hash=None,
 1383     passwordless=False,
 1384     unix_socket=False,
 1385     password_column=None,
 1386     auth_plugin="mysql_native_password",
 1387     **connection_args
 1388 ):
 1389 
 1390     qry = (
 1391         "SELECT User,Host FROM mysql.user WHERE User = %(user)s AND " "Host = %(host)s"
 1392     )
 1393     args = {}
 1394     args["user"] = user
 1395     args["host"] = host
 1396 
 1397     if salt.utils.data.is_true(passwordless):
 1398         if salt.utils.data.is_true(unix_socket):
 1399             qry += " AND plugin=%(unix_socket)s"
 1400             args["unix_socket"] = "unix_socket"
 1401         else:
 1402             qry += " AND " + password_column + " = ''"
 1403     elif password:
 1404         qry += " AND " + password_column + " = PASSWORD(%(password)s)"
 1405         args["password"] = str(password)
 1406     elif password_hash:
 1407         qry += " AND " + password_column + " = %(password)s"
 1408         args["password"] = password_hash
 1409 
 1410     return qry, args
 1411 
 1412 
 1413 def user_exists(
 1414     user,
 1415     host="localhost",
 1416     password=None,
 1417     password_hash=None,
 1418     passwordless=False,
 1419     unix_socket=False,
 1420     password_column=None,
 1421     **connection_args
 1422 ):
 1423     """
 1424     Checks if a user exists on the MySQL server. A login can be checked to see
 1425     if passwordless login is permitted by omitting ``password`` and
 1426     ``password_hash``, and using ``passwordless=True``.
 1427 
 1428     .. versionadded:: 0.16.2
 1429         The ``passwordless`` option was added.
 1430 
 1431     CLI Example:
 1432 
 1433     .. code-block:: bash
 1434 
 1435         salt '*' mysql.user_exists 'username' 'hostname' 'password'
 1436         salt '*' mysql.user_exists 'username' 'hostname' password_hash='hash'
 1437         salt '*' mysql.user_exists 'username' passwordless=True
 1438         salt '*' mysql.user_exists 'username' password_column='authentication_string'
 1439     """
 1440     run_verify = False
 1441     server_version = salt.utils.data.decode(version(**connection_args))
 1442     if not server_version and password:
 1443         # Did we fail to connect with the user we are checking
 1444         # Its password might have previously change with the same command/state
 1445 
 1446         # Clear the previous error
 1447         __context__["mysql.error"] = None
 1448         connection_args["connection_pass"] = password
 1449 
 1450         server_version = salt.utils.data.decode(version(**connection_args))
 1451         if not server_version:
 1452             last_err = __context__["mysql.error"]
 1453             err = 'MySQL Error: Unable to fetch current server version. Last error was: "{}"'.format(
 1454                 last_err
 1455             )
 1456             log.error(err)
 1457             return False
 1458 
 1459     dbc = _connect(**connection_args)
 1460     # Did we fail to connect with the user we are checking
 1461     # Its password might have previously change with the same command/state
 1462     if (
 1463         dbc is None
 1464         and __context__["mysql.error"].startswith(
 1465             "MySQL Error 1045: Access denied for user '{}'@".format(user)
 1466         )
 1467         and password
 1468     ):
 1469         # Clear the previous error
 1470         __context__["mysql.error"] = None
 1471         connection_args["connection_pass"] = password
 1472         dbc = _connect(**connection_args)
 1473     if dbc is None:
 1474         return False
 1475 
 1476     if not password_column:
 1477         password_column = __password_column(**connection_args)
 1478 
 1479     auth_plugin = __get_auth_plugin(user, host, **connection_args)
 1480 
 1481     cur = dbc.cursor()
 1482     if "MariaDB" in server_version:
 1483         qry, args = _mariadb_user_exists(
 1484             user,
 1485             host,
 1486             password,
 1487             password_hash,
 1488             passwordless,
 1489             unix_socket,
 1490             password_column=password_column,
 1491             auth_plugin=auth_plugin,
 1492             **connection_args
 1493         )
 1494     else:
 1495         qry, args = _mysql_user_exists(
 1496             user,
 1497             host,
 1498             password,
 1499             password_hash,
 1500             passwordless,
 1501             unix_socket,
 1502             password_column=password_column,
 1503             auth_plugin=auth_plugin,
 1504             **connection_args
 1505         )
 1506 
 1507     try:
 1508         _execute(cur, qry, args)
 1509     except MySQLdb.OperationalError as exc:
 1510         err = "MySQL Error {}: {}".format(*exc.args)
 1511         __context__["mysql.error"] = err
 1512         log.error(err)
 1513         return False
 1514 
 1515     return cur.rowcount == 1
 1516 
 1517 
 1518 def user_info(user, host="localhost", **connection_args):
 1519     """
 1520     Get full info on a MySQL user
 1521 
 1522     CLI Example:
 1523 
 1524     .. code-block:: bash
 1525 
 1526         salt '*' mysql.user_info root localhost
 1527     """
 1528     dbc = _connect(**connection_args)
 1529     if dbc is None:
 1530         return False
 1531 
 1532     cur = dbc.cursor(MySQLdb.cursors.DictCursor)
 1533     qry = "SELECT * FROM mysql.user WHERE User = %(user)s AND " "Host = %(host)s"
 1534     args = {}
 1535     args["user"] = user
 1536     args["host"] = host
 1537 
 1538     try:
 1539         _execute(cur, qry, args)
 1540     except MySQLdb.OperationalError as exc:
 1541         err = "MySQL Error {}: {}".format(*exc.args)
 1542         __context__["mysql.error"] = err
 1543         log.error(err)
 1544         return False
 1545     result = cur.fetchone()
 1546     log.debug(result)
 1547     return result
 1548 
 1549 
 1550 def _mysql_user_create(
 1551     user,
 1552     host="localhost",
 1553     password=None,
 1554     password_hash=None,
 1555     allow_passwordless=False,
 1556     unix_socket=False,
 1557     password_column=None,
 1558     auth_plugin="mysql_native_password",
 1559     **connection_args
 1560 ):
 1561 
 1562     server_version = salt.utils.data.decode(version(**connection_args))
 1563     compare_version = "8.0.11"
 1564 
 1565     qry = "CREATE USER %(user)s@%(host)s"
 1566     args = {}
 1567     args["user"] = user
 1568     args["host"] = host
 1569     if password is not None:
 1570         if salt.utils.versions.version_cmp(server_version, compare_version) >= 0:
 1571             args["auth_plugin"] = auth_plugin
 1572             qry += " IDENTIFIED WITH %(auth_plugin)s BY %(password)s"
 1573         else:
 1574             qry += " IDENTIFIED BY %(password)s"
 1575         args["password"] = str(password)
 1576     elif password_hash is not None:
 1577         if salt.utils.versions.version_cmp(server_version, compare_version) >= 0:
 1578             qry += " IDENTIFIED BY %(password)s"
 1579         else:
 1580             qry += " IDENTIFIED BY PASSWORD %(password)s"
 1581         args["password"] = password_hash
 1582     elif salt.utils.data.is_true(allow_passwordless):
 1583         if not plugin_status("auth_socket", **connection_args):
 1584             err = "The auth_socket plugin is not enabled."
 1585             log.error(err)
 1586             __context__["mysql.error"] = err
 1587             qry = False
 1588         else:
 1589             if salt.utils.data.is_true(unix_socket):
 1590                 if host == "localhost":
 1591                     qry += " IDENTIFIED WITH auth_socket"
 1592                 else:
 1593                     log.error("Auth via unix_socket can be set only for host=localhost")
 1594     else:
 1595         log.error(
 1596             "password or password_hash must be specified, unless "
 1597             "allow_passwordless=True"
 1598         )
 1599         qry = False
 1600 
 1601     return qry, args
 1602 
 1603 
 1604 def _mariadb_user_create(
 1605     user,
 1606     host="localhost",
 1607     password=None,
 1608     password_hash=None,
 1609     allow_passwordless=False,
 1610     unix_socket=False,
 1611     password_column=None,
 1612     auth_plugin="mysql_native_password",
 1613     **connection_args
 1614 ):
 1615 
 1616     qry = "CREATE USER %(user)s@%(host)s"
 1617     args = {}
 1618     args["user"] = user
 1619     args["host"] = host
 1620     if password is not None:
 1621         qry += " IDENTIFIED BY %(password)s"
 1622         args["password"] = str(password)
 1623     elif password_hash is not None:
 1624         qry += " IDENTIFIED BY PASSWORD %(password)s"
 1625         args["password"] = password_hash
 1626     elif salt.utils.data.is_true(allow_passwordless):
 1627         if not plugin_status("unix_socket", **connection_args):
 1628             err = "The unix_socket plugin is not enabled."
 1629             log.error(err)
 1630             __context__["mysql.error"] = err
 1631             qry = False
 1632         else:
 1633             if salt.utils.data.is_true(unix_socket):
 1634                 if host == "localhost":
 1635                     qry += " IDENTIFIED VIA unix_socket"
 1636                 else:
 1637                     log.error("Auth via unix_socket can be set only for host=localhost")
 1638     else:
 1639         log.error(
 1640             "password or password_hash must be specified, unless "
 1641             "allow_passwordless=True"
 1642         )
 1643         qry = False
 1644 
 1645     return qry, args
 1646 
 1647 
 1648 def user_create(
 1649     user,
 1650     host="localhost",
 1651     password=None,
 1652     password_hash=None,
 1653     allow_passwordless=False,
 1654     unix_socket=False,
 1655     password_column=None,
 1656     auth_plugin="mysql_native_password",
 1657     **connection_args
 1658 ):
 1659     """
 1660     Creates a MySQL user
 1661 
 1662     host
 1663         Host for which this user/password combo applies
 1664 
 1665     password
 1666         The password to use for the new user. Will take precedence over the
 1667         ``password_hash`` option if both are specified.
 1668 
 1669     password_hash
 1670         The password in hashed form. Be sure to quote the password because YAML
 1671         doesn't like the ``*``. A password hash can be obtained from the mysql
 1672         command-line client like so::
 1673 
 1674             mysql> SELECT PASSWORD('mypass');
 1675             +-------------------------------------------+
 1676             | PASSWORD('mypass')                        |
 1677             +-------------------------------------------+
 1678             | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
 1679             +-------------------------------------------+
 1680             1 row in set (0.00 sec)
 1681 
 1682     allow_passwordless
 1683         If ``True``, then ``password`` and ``password_hash`` can be omitted (or
 1684         set to ``None``) to permit a passwordless login.
 1685 
 1686     unix_socket
 1687         If ``True`` and allow_passwordless is ``True`` then will be used unix_socket auth plugin.
 1688 
 1689     password_column
 1690         The password column to use in the user table.
 1691 
 1692     auth_plugin
 1693         The authentication plugin to use, default is to use the mysql_native_password plugin.
 1694 
 1695     .. versionadded:: 0.16.2
 1696         The ``allow_passwordless`` option was added.
 1697 
 1698     CLI Examples:
 1699 
 1700     .. code-block:: bash
 1701 
 1702         salt '*' mysql.user_create 'username' 'hostname' 'password'
 1703         salt '*' mysql.user_create 'username' 'hostname' password_hash='hash'
 1704         salt '*' mysql.user_create 'username' 'hostname' allow_passwordless=True
 1705     """
 1706     server_version = salt.utils.data.decode(version(**connection_args))
 1707     if not server_version and password:
 1708         # Did we fail to connect with the user we are checking
 1709         # Its password might have previously change with the same command/state
 1710 
 1711         # Clear the previous error
 1712         __context__["mysql.error"] = None
 1713         connection_args["connection_pass"] = password
 1714 
 1715         server_version = salt.utils.data.decode(version(**connection_args))
 1716         if not server_version:
 1717             last_err = __context__["mysql.error"]
 1718             err = 'MySQL Error: Unable to fetch current server version. Last error was: "{}"'.format(
 1719                 last_err
 1720             )
 1721             log.error(err)
 1722             return False
 1723 
 1724     if user_exists(user, host, **connection_args):
 1725         log.info("User '%s'@'%s' already exists", user, host)
 1726         return False
 1727 
 1728     dbc = _connect(**connection_args)
 1729     if dbc is None:
 1730         return False
 1731 
 1732     if not password_column:
 1733         password_column = __password_column(**connection_args)
 1734 
 1735     cur = dbc.cursor()
 1736     if "MariaDB" in server_version:
 1737         qry, args = _mariadb_user_create(
 1738             user,
 1739             host,
 1740             password,
 1741             password_hash,
 1742             allow_passwordless,
 1743             unix_socket,
 1744             password_column=password_column,
 1745             auth_plugin=auth_plugin,
 1746             **connection_args
 1747         )
 1748     else:
 1749         qry, args = _mysql_user_create(
 1750             user,
 1751             host,
 1752             password,
 1753             password_hash,
 1754             allow_passwordless,
 1755             unix_socket,
 1756             password_column=password_column,
 1757             auth_plugin=auth_plugin,
 1758             **connection_args
 1759         )
 1760 
 1761     if isinstance(qry, bool):
 1762         return qry
 1763 
 1764     try:
 1765         _execute(cur, qry, args)
 1766     except MySQLdb.OperationalError as exc:
 1767         err = "MySQL Error {}: {}".format(*exc.args)
 1768         __context__["mysql.error"] = err
 1769         log.error(err)
 1770         return False
 1771 
 1772     if user_exists(
 1773         user,
 1774         host,
 1775         password,
 1776         password_hash,
 1777         password_column=password_column,
 1778         **connection_args
 1779     ):
 1780         msg = "User '{}'@'{}' has been created".format(user, host)
 1781         if not any((password, password_hash)):
 1782             msg += " with passwordless login"
 1783         log.info(msg)
 1784         return True
 1785 
 1786     log.info("User '%s'@'%s' was not created", user, host)
 1787     return False
 1788 
 1789 
 1790 def _mysql_user_chpass(
 1791     user,
 1792     host="localhost",
 1793     password=None,
 1794     password_hash=None,
 1795     allow_passwordless=False,
 1796     unix_socket=None,
 1797     password_column=None,
 1798     auth_plugin="mysql_native_password",
 1799     **connection_args
 1800 ):
 1801     server_version = salt.utils.data.decode(version(**connection_args))
 1802     compare_version = "8.0.11"
 1803 
 1804     args = {}
 1805 
 1806     if password is not None:
 1807         if salt.utils.versions.version_cmp(server_version, compare_version) >= 0:
 1808             password_sql = "%(password)s"
 1809         else:
 1810             password_sql = "PASSWORD(%(password)s)"
 1811         args["password"] = password
 1812     elif password_hash is not None:
 1813         password_sql = "%(password)s"
 1814         args["password"] = password_hash
 1815     elif not salt.utils.data.is_true(allow_passwordless):
 1816         log.error(
 1817             "password or password_hash must be specified, unless "
 1818             "allow_passwordless=True"
 1819         )
 1820         return False
 1821     else:
 1822         password_sql = "''"
 1823 
 1824     args["user"] = user
 1825     args["host"] = host
 1826 
 1827     if salt.utils.versions.version_cmp(server_version, compare_version) >= 0:
 1828         qry = "ALTER USER %(user)s@%(host)s IDENTIFIED BY %(password)s;"
 1829     else:
 1830         qry = (
 1831             "UPDATE mysql.user SET "
 1832             + password_column
 1833             + "="
 1834             + password_sql
 1835             + " WHERE User=%(user)s AND Host = %(host)s;"
 1836         )
 1837     if salt.utils.data.is_true(allow_passwordless) and salt.utils.data.is_true(
 1838         unix_socket
 1839     ):
 1840         if host == "localhost":
 1841             if not plugin_status("auth_socket", **connection_args):
 1842                 err = "The auth_socket plugin is not enabled."
 1843                 log.error(err)
 1844                 __context__["mysql.error"] = err
 1845                 qry = False
 1846             else:
 1847                 args["unix_socket"] = "auth_socket"
 1848                 if (
 1849                     salt.utils.versions.version_cmp(server_version, compare_version)
 1850                     >= 0
 1851                 ):
 1852                     qry = "ALTER USER %(user)s@%(host)s IDENTIFIED WITH %(unix_socket)s AS %(user)s;"
 1853                 else:
 1854                     qry = (
 1855                         "UPDATE mysql.user SET "
 1856                         + password_column
 1857                         + "="
 1858                         + password_sql
 1859                         + ", plugin=%(unix_socket)s"
 1860                         + " WHERE User=%(user)s AND Host = %(host)s;"
 1861                     )
 1862         else:
 1863             log.error("Auth via unix_socket can be set only for host=localhost")
 1864 
 1865     return qry, args
 1866 
 1867 
 1868 def _mariadb_user_chpass(
 1869     user,
 1870     host="localhost",
 1871     password=None,
 1872     password_hash=None,
 1873     allow_passwordless=False,
 1874     unix_socket=None,
 1875     password_column=None,
 1876     auth_plugin="mysql_native_password",
 1877     **connection_args
 1878 ):
 1879 
 1880     server_version = salt.utils.data.decode(version(**connection_args))
 1881     compare_version = "10.4.0"
 1882 
 1883     args = {}
 1884 
 1885     if password is not None:
 1886         password_sql = "PASSWORD(%(password)s)"
 1887         args["password"] = password
 1888     elif password_hash is not None:
 1889         password_sql = "%(password)s"
 1890         args["password"] = password_hash
 1891     elif not salt.utils.data.is_true(allow_passwordless):
 1892         log.error(
 1893             "password or password_hash must be specified, unless "
 1894             "allow_passwordless=True"
 1895         )
 1896         return False
 1897     else:
 1898         password_sql = "''"
 1899 
 1900     args["user"] = user
 1901     args["host"] = host
 1902 
 1903     if salt.utils.versions.version_cmp(server_version, compare_version) >= 0:
 1904         qry = "ALTER USER %(user)s@%(host)s IDENTIFIED BY %(password)s;"
 1905     else:
 1906         qry = (
 1907             "UPDATE mysql.user SET "
 1908             + password_column
 1909             + "="
 1910             + password_sql
 1911             + " WHERE User=%(user)s AND Host = %(host)s;"
 1912         )
 1913     if salt.utils.data.is_true(allow_passwordless) and salt.utils.data.is_true(
 1914         unix_socket
 1915     ):
 1916         if host == "localhost":
 1917             if not plugin_status("unix_socket", **connection_args):
 1918                 err = "The unix_socket plugin is not enabled."
 1919                 log.error(err)
 1920                 __context__["mysql.error"] = err
 1921                 qry = False
 1922             else:
 1923                 args["unix_socket"] = "unix_socket"
 1924                 qry = (
 1925                     "UPDATE mysql.user SET "
 1926                     + password_column
 1927                     + "="
 1928                     + password_sql
 1929                     + ", plugin=%(unix_socket)s"
 1930                     + " WHERE User=%(user)s AND Host = %(host)s;"
 1931                 )
 1932         else:
 1933             log.error("Auth via unix_socket can be set only for host=localhost")
 1934 
 1935     return qry, args
 1936 
 1937 
 1938 def user_chpass(
 1939     user,
 1940     host="localhost",
 1941     password=None,
 1942     password_hash=None,
 1943     allow_passwordless=False,
 1944     unix_socket=None,
 1945     password_column=None,
 1946     **connection_args
 1947 ):
 1948     """
 1949     Change password for a MySQL user
 1950 
 1951     host
 1952         Host for which this user/password combo applies
 1953 
 1954     password
 1955         The password to set for the new user. Will take precedence over the
 1956         ``password_hash`` option if both are specified.
 1957 
 1958     password_hash
 1959         The password in hashed form. Be sure to quote the password because YAML
 1960         doesn't like the ``*``. A password hash can be obtained from the mysql
 1961         command-line client like so::
 1962 
 1963             mysql> SELECT PASSWORD('mypass');
 1964             +-------------------------------------------+
 1965             | PASSWORD('mypass')                        |
 1966             +-------------------------------------------+
 1967             | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
 1968             +-------------------------------------------+
 1969             1 row in set (0.00 sec)
 1970 
 1971     allow_passwordless
 1972         If ``True``, then ``password`` and ``password_hash`` can be omitted (or
 1973         set to ``None``) to permit a passwordless login.
 1974 
 1975     .. versionadded:: 0.16.2
 1976         The ``allow_passwordless`` option was added.
 1977 
 1978     CLI Examples:
 1979 
 1980     .. code-block:: bash
 1981 
 1982         salt '*' mysql.user_chpass frank localhost newpassword
 1983         salt '*' mysql.user_chpass frank localhost password_hash='hash'
 1984         salt '*' mysql.user_chpass frank localhost allow_passwordless=True
 1985     """
 1986     server_version = salt.utils.data.decode(version(**connection_args))
 1987     if not server_version and password:
 1988         # Did we fail to connect with the user we are checking
 1989         # Its password might have previously change with the same command/state
 1990 
 1991         # Clear the previous error
 1992         __context__["mysql.error"] = None
 1993         connection_args["connection_pass"] = password
 1994 
 1995         server_version = salt.utils.data.decode(version(**connection_args))
 1996         if not server_version:
 1997             last_err = __context__["mysql.error"]
 1998             err = 'MySQL Error: Unable to fetch current server version. Last error was: "{}"'.format(
 1999                 last_err
 2000             )
 2001             log.error(err)
 2002             return False
 2003 
 2004     if not user_exists(user, host, **connection_args):
 2005         log.info("User '%s'@'%s' does not exists", user, host)
 2006         return False
 2007 
 2008     dbc = _connect(**connection_args)
 2009 
 2010     if dbc is None:
 2011         return False
 2012 
 2013     if not password_column:
 2014         password_column = __password_column(**connection_args)
 2015 
 2016     auth_plugin = __get_auth_plugin(user, host, **connection_args)
 2017 
 2018     cur = dbc.cursor()
 2019 
 2020     if "MariaDB" in server_version:
 2021         qry, args = _mariadb_user_chpass(
 2022             user,
 2023             host,
 2024             password,
 2025             password_hash,
 2026             allow_passwordless,
 2027             unix_socket,
 2028             password_column=password_column,
 2029             auth_plugin=auth_plugin,
 2030             **connection_args
 2031         )
 2032     else:
 2033         qry, args = _mysql_user_chpass(
 2034             user,
 2035             host,
 2036             password,
 2037             password_hash,
 2038             allow_passwordless,
 2039             unix_socket,
 2040             password_column=password_column,
 2041             auth_plugin=auth_plugin,
 2042             **connection_args
 2043         )
 2044 
 2045     try:
 2046         result = _execute(cur, qry, args)
 2047     except MySQLdb.OperationalError as exc:
 2048         err = "MySQL Error {}: {}".format(*exc.args)
 2049         __context__["mysql.error"] = err
 2050         log.error(err)
 2051         return False
 2052 
 2053     compare_version = "10.4.0" if "MariaDB" in server_version else "8.0.11"
 2054     res = False
 2055     if salt.utils.versions.version_cmp(server_version, compare_version) >= 0:
 2056         _execute(cur, "FLUSH PRIVILEGES;")
 2057         res = True
 2058     else:
 2059         if result:
 2060             _execute(cur, "FLUSH PRIVILEGES;")
 2061             res = True
 2062 
 2063     if res:
 2064         log.info(
 2065             "Password for user '%s'@'%s' has been %s",
 2066             user,
 2067             host,
 2068             "changed" if any((password, password_hash)) else "cleared",
 2069         )
 2070         return True
 2071     else:
 2072         log.info(
 2073             "Password for user '%s'@'%s' was not %s",
 2074             user,
 2075             host,
 2076             "changed" if any((password, password_hash)) else "cleared",
 2077         )
 2078         return False
 2079 
 2080 
 2081 def user_remove(user, host="localhost", **connection_args):
 2082     """
 2083     Delete MySQL user
 2084 
 2085     CLI Example:
 2086 
 2087     .. code-block:: bash
 2088 
 2089         salt '*' mysql.user_remove frank localhost
 2090     """
 2091     if not user_exists(user, host, **connection_args):
 2092         err = "User '%s'@'%s' does not exists", user, host
 2093         __context__["mysql.error"] = err
 2094         log.info(err)
 2095         return False
 2096 
 2097     dbc = _connect(**connection_args)
 2098     if dbc is None:
 2099         return False
 2100 
 2101     cur = dbc.cursor()
 2102     qry = "DROP USER %(user)s@%(host)s"
 2103     args = {}
 2104     args["user"] = user
 2105     args["host"] = host
 2106     try:
 2107         _execute(cur, qry, args)
 2108     except MySQLdb.OperationalError as exc:
 2109         err = "MySQL Error {}: {}".format(*exc.args)
 2110         __context__["mysql.error"] = err
 2111         log.error(err)
 2112         return False
 2113 
 2114     if not user_exists(user, host, **connection_args):
 2115         log.info("User '%s'@'%s' has been removed", user, host)
 2116         return True
 2117 
 2118     log.info("User '%s'@'%s' has NOT been removed", user, host)
 2119     return False
 2120 
 2121 
 2122 def tokenize_grant(grant):
 2123     """
 2124     External wrapper function
 2125     :param grant:
 2126     :return: dict
 2127 
 2128     CLI Example:
 2129 
 2130     .. code-block:: bash
 2131 
 2132         salt '*' mysql.tokenize_grant \
 2133             "GRANT SELECT, INSERT ON testdb.* TO 'testuser'@'localhost'"
 2134     """
 2135     return _grant_to_tokens(grant)
 2136 
 2137 
 2138 # Maintenance
 2139 def db_check(name, table=None, **connection_args):
 2140     """
 2141     Repairs the full database or just a given table
 2142 
 2143     CLI Example:
 2144 
 2145     .. code-block:: bash
 2146 
 2147         salt '*' mysql.db_check dbname
 2148         salt '*' mysql.db_check dbname dbtable
 2149     """
 2150     ret = []
 2151     if table is None:
 2152         # we need to check all tables
 2153         tables = db_tables(name, **connection_args)
 2154         for table in tables:
 2155             log.info("Checking table '%s' in db '%s'..", name, table)
 2156             ret.append(__check_table(name, table, **connection_args))
 2157     else:
 2158         log.info("Checking table '%s' in db '%s'..", name, table)
 2159         ret = __check_table(name, table, **connection_args)
 2160     return ret
 2161 
 2162 
 2163 def db_repair(name, table=None, **connection_args):
 2164     """
 2165     Repairs the full database or just a given table
 2166 
 2167     CLI Example:
 2168 
 2169     .. code-block:: bash
 2170 
 2171         salt '*' mysql.db_repair dbname
 2172     """
 2173     ret = []
 2174     if table is None:
 2175         # we need to repair all tables
 2176         tables = db_tables(name, **connection_args)
 2177         for table in tables:
 2178             log.info("Repairing table '%s' in db '%s'..", name, table)
 2179             ret.append(__repair_table(name, table, **connection_args))
 2180     else:
 2181         log.info("Repairing table '%s' in db '%s'..", name, table)
 2182         ret = __repair_table(name, table, **connection_args)
 2183     return ret
 2184 
 2185 
 2186 def db_optimize(name, table=None, **connection_args):
 2187     """
 2188     Optimizes the full database or just a given table
 2189 
 2190     CLI Example:
 2191 
 2192     .. code-block:: bash
 2193 
 2194         salt '*' mysql.db_optimize dbname
 2195     """
 2196     ret = []
 2197     if table is None:
 2198         # we need to optimize all tables
 2199         tables = db_tables(name, **connection_args)
 2200         for table in tables:
 2201             log.info("Optimizing table '%s' in db '%s'..", name, table)
 2202             ret.append(__optimize_table(name, table, **connection_args))
 2203     else:
 2204         log.info("Optimizing table '%s' in db '%s'..", name, table)
 2205         ret = __optimize_table(name, table, **connection_args)
 2206     return ret
 2207 
 2208 
 2209 # Grants
 2210 def __grant_normalize(grant):
 2211     # MySQL normalizes ALL to ALL PRIVILEGES, we do the same so that
 2212     # grant_exists and grant_add ALL work correctly
 2213     if grant.strip().upper() == "ALL":
 2214         grant = "ALL PRIVILEGES"
 2215 
 2216     # Grants are paste directly in SQL, must filter it
 2217     exploded_grants = __grant_split(grant)
 2218     for chkgrant, _ in exploded_grants:
 2219         if chkgrant.strip().upper() not in __grants__:
 2220             raise Exception("Invalid grant : '{}'".format(chkgrant))
 2221 
 2222     return grant
 2223 
 2224 
 2225 def __grant_split(grant):
 2226     pattern = re.compile(r"([\w\s]+)(\([^)(]*\))?\s*,?")
 2227     return pattern.findall(grant)
 2228 
 2229 
 2230 def __ssl_option_sanitize(ssl_option):
 2231     new_ssl_option = []
 2232 
 2233     # Like most other "salt dsl" YAML structures, ssl_option is a list of single-element dicts
 2234     for opt in ssl_option:
 2235         key = next(iter(opt.keys()))
 2236 
 2237         normal_key = key.strip().upper()
 2238 
 2239         if normal_key not in __ssl_options__:
 2240             raise Exception("Invalid SSL option : '{}'".format(key))
 2241 
 2242         if normal_key in __ssl_options_parameterized__:
 2243             # SSL option parameters (cipher, issuer, subject) are pasted directly to SQL so
 2244             # we need to sanitize for single quotes...
 2245             new_ssl_option.append(
 2246                 "{} '{}'".format(normal_key, opt[key].replace("'", ""))
 2247             )
 2248         # omit if falsey
 2249         elif opt[key]:
 2250             new_ssl_option.append(normal_key)
 2251 
 2252     return " REQUIRE " + " AND ".join(new_ssl_option)
 2253 
 2254 
 2255 def __grant_generate(
 2256     grant,
 2257     database,
 2258     user,
 2259     host="localhost",
 2260     grant_option=False,
 2261     escape=True,
 2262     ssl_option=False,
 2263 ):
 2264     """
 2265     Validate grants and build the query that could set the given grants
 2266 
 2267     Note that this query contains arguments for user and host but not for
 2268     grants or database.
 2269     """
 2270     # TODO: Re-order the grant so it is according to the
 2271     #       SHOW GRANTS for xxx@yyy query (SELECT comes first, etc)
 2272     grant = re.sub(r"\s*,\s*", ", ", grant).upper()
 2273 
 2274     grant = __grant_normalize(grant)
 2275 
 2276     db_part = database.rpartition(".")
 2277     dbc = db_part[0]
 2278     table = db_part[2]
 2279 
 2280     if escape:
 2281         if dbc != "*":
 2282             # _ and % are authorized on GRANT queries and should get escaped
 2283             # on the db name, but only if not requesting a table level grant
 2284             dbc = quote_identifier(dbc, for_grants=(table == "*"))
 2285         if table != "*":
 2286             table = quote_identifier(table)
 2287     # identifiers cannot be used as values, and same thing for grants
 2288     qry = "GRANT {} ON {}.{} TO %(user)s@%(host)s".format(grant, dbc, table)
 2289     args = {}
 2290     args["user"] = user
 2291     args["host"] = host
 2292     if ssl_option and isinstance(ssl_option, list):
 2293         qry += __ssl_option_sanitize(ssl_option)
 2294     if salt.utils.data.is_true(grant_option):
 2295         qry += " WITH GRANT OPTION"
 2296     log.debug("Grant Query generated: %s args %s", qry, repr(args))
 2297     return {"qry": qry, "args": args}
 2298 
 2299 
 2300 def user_grants(user, host="localhost", **connection_args):
 2301     """
 2302     Shows the grants for the given MySQL user (if it exists)
 2303 
 2304     CLI Example:
 2305 
 2306     .. code-block:: bash
 2307 
 2308         salt '*' mysql.user_grants 'frank' 'localhost'
 2309     """
 2310     if not user_exists(user, host, **connection_args):
 2311         log.info("User '%s'@'%s' does not exist", user, host)
 2312         return False
 2313 
 2314     dbc = _connect(**connection_args)
 2315     if dbc is None:
 2316         return False
 2317     cur = dbc.cursor()
 2318     qry = "SHOW GRANTS FOR %(user)s@%(host)s"
 2319     args = {}
 2320     args["user"] = user
 2321     args["host"] = host
 2322     try:
 2323         _execute(cur, qry, args)
 2324     except MySQLdb.OperationalError as exc:
 2325         err = "MySQL Error {}: {}".format(*exc.args)
 2326         __context__["mysql.error"] = err
 2327         log.error(err)
 2328         return False
 2329 
 2330     ret = []
 2331     results = salt.utils.data.decode(cur.fetchall())
 2332     for grant in results:
 2333         tmp = grant[0].split(" IDENTIFIED BY")[0]
 2334         if "WITH GRANT OPTION" in grant[0] and "WITH GRANT OPTION" not in tmp:
 2335             tmp = "{} WITH GRANT OPTION".format(tmp)
 2336         ret.append(tmp)
 2337     log.debug(ret)
 2338     return ret
 2339 
 2340 
 2341 def grant_exists(
 2342     grant,
 2343     database,
 2344     user,
 2345     host="localhost",
 2346     grant_option=False,
 2347     escape=True,
 2348     **connection_args
 2349 ):
 2350     """
 2351     Checks to see if a grant exists in the database
 2352 
 2353     CLI Example:
 2354 
 2355     .. code-block:: bash
 2356 
 2357         salt '*' mysql.grant_exists \
 2358              'SELECT,INSERT,UPDATE,...' 'database.*' 'frank' 'localhost'
 2359     """
 2360 
 2361     server_version = salt.utils.data.decode(version(**connection_args))
 2362     if not server_version:
 2363         last_err = __context__["mysql.error"]
 2364         err = 'MySQL Error: Unable to fetch current server version. Last error was: "{}"'.format(
 2365             last_err
 2366         )
 2367         log.error(err)
 2368         return False
 2369     if "ALL" in grant:
 2370         if (
 2371             salt.utils.versions.version_cmp(server_version, "8.0") >= 0
 2372             and "MariaDB" not in server_version
 2373         ):
 2374             grant = ",".join([i for i in __all_privileges__])
 2375         else:
 2376             grant = "ALL PRIVILEGES"
 2377 
 2378     try:
 2379         target = __grant_generate(grant, database, user, host, grant_option, escape)
 2380     except Exception:  # pylint: disable=broad-except
 2381         log.error("Error during grant generation.")
 2382         return False
 2383 
 2384     grants = user_grants(user, host, **connection_args)
 2385 
 2386     if grants is False:
 2387         log.error(
 2388             "Grant does not exist or may not be ordered properly. In some cases, "
 2389             "this could also indicate a connection error. Check your configuration."
 2390         )
 2391         return False
 2392 
 2393     # Combine grants that match the same database
 2394     _grants = {}
 2395     for grant in grants:
 2396         grant_token = _grant_to_tokens(grant)
 2397         if grant_token["database"] not in _grants:
 2398             _grants[grant_token["database"]] = {
 2399                 "user": grant_token["user"],
 2400                 "database": grant_token["database"],
 2401                 "host": grant_token["host"],
 2402                 "grant": grant_token["grant"],
 2403             }
 2404         else:
 2405             _grants[grant_token["database"]]["grant"].extend(grant_token["grant"])
 2406 
 2407     target_tokens = _grant_to_tokens(target)
 2408     for database, grant_tokens in _grants.items():
 2409         try:
 2410             _grant_tokens = {}
 2411             _target_tokens = {}
 2412 
 2413             _grant_matches = [
 2414                 True if i in grant_tokens["grant"] else False
 2415                 for i in target_tokens["grant"]
 2416             ]
 2417 
 2418             for item in ["user", "database", "host"]:
 2419                 _grant_tokens[item] = (
 2420                     grant_tokens[item]
 2421                     .replace('"', "")
 2422                     .replace("\\", "")
 2423                     .replace("`", "")
 2424                 )
 2425                 _target_tokens[item] = (
 2426                     target_tokens[item]
 2427                     .replace('"', "")
 2428                     .replace("\\", "")
 2429                     .replace("`", "")
 2430                 )
 2431 
 2432             if (
 2433                 _grant_tokens["user"] == _target_tokens["user"]
 2434                 and _grant_tokens["database"] == _target_tokens["database"]
 2435                 and _grant_tokens["host"] == _target_tokens["host"]
 2436                 and all(_grant_matches)
 2437             ):
 2438                 return True
 2439             else:
 2440                 log.debug("grants mismatch '%s'<>'%s'", grant_tokens, target_tokens)
 2441 
 2442         except Exception as exc:  # pylint: disable=broad-except
 2443             # Fallback to strict parsing
 2444             log.exception(exc)
 2445             if grants is not False and target in grants:
 2446                 log.debug("Grant exists.")
 2447                 return True
 2448 
 2449     log.debug("Grant does not exist, or is perhaps not ordered properly?")
 2450     return False
 2451 
 2452 
 2453 def grant_add(
 2454     grant,
 2455     database,
 2456     user,
 2457     host="localhost",
 2458     grant_option=False,
 2459     escape=True,
 2460     ssl_option=False,
 2461     **connection_args
 2462 ):
 2463     """
 2464     Adds a grant to the MySQL server.
 2465 
 2466     For database, make sure you specify database.table or database.*
 2467 
 2468     CLI Example:
 2469 
 2470     .. code-block:: bash
 2471 
 2472         salt '*' mysql.grant_add \
 2473             'SELECT,INSERT,UPDATE,...' 'database.*' 'frank' 'localhost'
 2474     """
 2475     dbc = _connect(**connection_args)
 2476     if dbc is None:
 2477         return False
 2478     cur = dbc.cursor()
 2479 
 2480     # Avoid spaces problems
 2481     grant = grant.strip()
 2482     try:
 2483         qry = __grant_generate(
 2484             grant, database, user, host, grant_option, escape, ssl_option
 2485         )
 2486     except Exception:  # pylint: disable=broad-except
 2487         log.error("Error during grant generation")
 2488         return False
 2489     try:
 2490         _execute(cur, qry["qry"], qry["args"])
 2491     except (MySQLdb.OperationalError, MySQLdb.ProgrammingError) as exc:
 2492         err = "MySQL Error {}: {}".format(*exc.args)
 2493         __context__["mysql.error"] = err
 2494         log.error(err)
 2495         return False
 2496     if grant_exists(
 2497         grant, database, user, host, grant_option, escape, **connection_args
 2498     ):
 2499         log.info(
 2500             "Grant '%s' on '%s' for user '%s' has been added", grant, database, user
 2501         )
 2502         return True
 2503 
 2504     log.info(
 2505         "Grant '%s' on '%s' for user '%s' has NOT been added", grant, database, user
 2506     )
 2507     return False
 2508 
 2509 
 2510 def grant_revoke(
 2511     grant,
 2512     database,
 2513     user,
 2514     host="localhost",
 2515     grant_option=False,
 2516     escape=True,
 2517     **connection_args
 2518 ):
 2519     """
 2520     Removes a grant from the MySQL server.
 2521 
 2522     CLI Example:
 2523 
 2524     .. code-block:: bash
 2525 
 2526         salt '*' mysql.grant_revoke \
 2527             'SELECT,INSERT,UPDATE' 'database.*' 'frank' 'localhost'
 2528     """
 2529     dbc = _connect(**connection_args)
 2530     if dbc is None:
 2531         return False
 2532     cur = dbc.cursor()
 2533 
 2534     grant = __grant_normalize(grant)
 2535 
 2536     if salt.utils.data.is_true(grant_option):
 2537         grant += ", GRANT OPTION"
 2538 
 2539     db_part = database.rpartition(".")
 2540     dbc = db_part[0]
 2541     table = db_part[2]
 2542     if dbc != "*":
 2543         # _ and % are authorized on GRANT queries and should get escaped
 2544         # on the db name, but only if not requesting a table level grant
 2545         s_database = quote_identifier(dbc, for_grants=(table == "*"))
 2546     if dbc == "*":
 2547         # add revoke for *.*
 2548         # before the modification query send to mysql will looks like
 2549         # REVOKE SELECT ON `*`.* FROM %(user)s@%(host)s
 2550         s_database = dbc
 2551     if table != "*":
 2552         table = quote_identifier(table)
 2553     # identifiers cannot be used as values, same thing for grants
 2554     qry = "REVOKE {} ON {}.{} FROM %(user)s@%(host)s;".format(grant, s_database, table)
 2555     args = {}
 2556     args["user"] = user
 2557     args["host"] = host
 2558 
 2559     try:
 2560         _execute(cur, qry, args)
 2561     except MySQLdb.OperationalError as exc:
 2562         err = "MySQL Error {}: {}".format(*exc.args)
 2563         __context__["mysql.error"] = err
 2564         log.error(err)
 2565         return False
 2566 
 2567     if not grant_exists(
 2568         grant, database, user, host, grant_option, escape, **connection_args
 2569     ):
 2570         log.info(
 2571             "Grant '%s' on '%s' for user '%s' has been " "revoked",
 2572             grant,
 2573             database,
 2574             user,
 2575         )
 2576         return True
 2577 
 2578     log.info(
 2579         "Grant '%s' on '%s' for user '%s' has NOT been " "revoked",
 2580         grant,
 2581         database,
 2582         user,
 2583     )
 2584     return False
 2585 
 2586 
 2587 def processlist(**connection_args):
 2588     """
 2589     Retrieves the processlist from the MySQL server via
 2590     "SHOW FULL PROCESSLIST".
 2591 
 2592     Returns: a list of dicts, with each dict representing a process:
 2593 
 2594     .. code-block:: python
 2595 
 2596         {'Command': 'Query',
 2597         'Host': 'localhost',
 2598         'Id': 39,
 2599         'Info': 'SHOW FULL PROCESSLIST',
 2600         'Rows_examined': 0,
 2601         'Rows_read': 1,
 2602         'Rows_sent': 0,
 2603         'State': None,
 2604         'Time': 0,
 2605         'User': 'root',
 2606         'db': 'mysql'}
 2607 
 2608     CLI Example:
 2609 
 2610     .. code-block:: bash
 2611 
 2612         salt '*' mysql.processlist
 2613 
 2614     """
 2615     ret = []
 2616 
 2617     dbc = _connect(**connection_args)
 2618     if dbc is None:
 2619         return []
 2620     cur = dbc.cursor()
 2621     _execute(cur, "SHOW FULL PROCESSLIST")
 2622     hdr = [c[0] for c in cur.description]
 2623     for _ in range(cur.rowcount):
 2624         row = cur.fetchone()
 2625         idx_r = {}
 2626         for idx_j in range(len(hdr)):
 2627             idx_r[hdr[idx_j]] = row[idx_j]
 2628         ret.append(idx_r)
 2629     cur.close()
 2630     return ret
 2631 
 2632 
 2633 def __do_query_into_hash(conn, sql_str):
 2634     """
 2635     Perform the query that is passed to it (sql_str).
 2636 
 2637     Returns:
 2638        results in a dict.
 2639 
 2640     """
 2641     mod = sys._getframe().f_code.co_name
 2642     log.debug("%s<--(%s)", mod, sql_str)
 2643 
 2644     rtn_results = []
 2645 
 2646     try:
 2647         cursor = conn.cursor()
 2648     except MySQLdb.MySQLError:
 2649         log.error("%s: Can't get cursor for SQL->%s", mod, sql_str)
 2650         cursor.close()
 2651         log.debug("%s-->", mod)
 2652         return rtn_results
 2653 
 2654     try:
 2655         _execute(cursor, sql_str)
 2656     except MySQLdb.MySQLError:
 2657         log.error("%s: try to execute : SQL->%s", mod, sql_str)
 2658         cursor.close()
 2659         log.debug("%s-->", mod)
 2660         return rtn_results
 2661 
 2662     qrs = cursor.fetchall()
 2663 
 2664     for row_data in qrs:
 2665         col_cnt = 0
 2666         row = {}
 2667         for col_data in cursor.description:
 2668             col_name = col_data[0]
 2669             row[col_name] = row_data[col_cnt]
 2670             col_cnt += 1
 2671 
 2672         rtn_results.append(row)
 2673 
 2674     cursor.close()
 2675     log.debug("%s-->", mod)
 2676     return rtn_results
 2677 
 2678 
 2679 def get_master_status(**connection_args):
 2680     """
 2681     Retrieves the master status from the minion.
 2682 
 2683     Returns::
 2684 
 2685         {'host.domain.com': {'Binlog_Do_DB': '',
 2686                          'Binlog_Ignore_DB': '',
 2687                          'File': 'mysql-bin.000021',
 2688                          'Position': 107}}
 2689 
 2690     CLI Example:
 2691 
 2692     .. code-block:: bash
 2693 
 2694         salt '*' mysql.get_master_status
 2695 
 2696     """
 2697     mod = sys._getframe().f_code.co_name
 2698     log.debug("%s<--", mod)
 2699     conn = _connect(**connection_args)
 2700     if conn is None:
 2701         return []
 2702     rtnv = __do_query_into_hash(conn, "SHOW MASTER STATUS")
 2703     conn.close()
 2704 
 2705     # check for if this minion is not a master
 2706     if not rtnv:
 2707         rtnv.append([])
 2708 
 2709     log.debug("%s-->%s", mod, len(rtnv[0]))
 2710     return rtnv[0]
 2711 
 2712 
 2713 def get_slave_status(**connection_args):
 2714     """
 2715     Retrieves the slave status from the minion.
 2716 
 2717     Returns::
 2718 
 2719         {'host.domain.com': {'Connect_Retry': 60,
 2720                        'Exec_Master_Log_Pos': 107,
 2721                        'Last_Errno': 0,
 2722                        'Last_Error': '',
 2723                        'Last_IO_Errno': 0,
 2724                        'Last_IO_Error': '',
 2725                        'Last_SQL_Errno': 0,
 2726                        'Last_SQL_Error': '',
 2727                        'Master_Host': 'comet.scion-eng.com',
 2728                        'Master_Log_File': 'mysql-bin.000021',
 2729                        'Master_Port': 3306,
 2730                        'Master_SSL_Allowed': 'No',
 2731                        'Master_SSL_CA_File': '',
 2732                        'Master_SSL_CA_Path': '',
 2733                        'Master_SSL_Cert': '',
 2734                        'Master_SSL_Cipher': '',
 2735                        'Master_SSL_Key': '',
 2736                        'Master_SSL_Verify_Server_Cert': 'No',
 2737                        'Master_Server_Id': 1,
 2738                        'Master_User': 'replu',
 2739                        'Read_Master_Log_Pos': 107,
 2740                        'Relay_Log_File': 'klo-relay-bin.000071',
 2741                        'Relay_Log_Pos': 253,
 2742                        'Relay_Log_Space': 553,
 2743                        'Relay_Master_Log_File': 'mysql-bin.000021',
 2744                        'Replicate_Do_DB': '',
 2745                        'Replicate_Do_Table': '',
 2746                        'Replicate_Ignore_DB': '',
 2747                        'Replicate_Ignore_Server_Ids': '',
 2748                        'Replicate_Ignore_Table': '',
 2749                        'Replicate_Wild_Do_Table': '',
 2750                        'Replicate_Wild_Ignore_Table': '',
 2751                        'Seconds_Behind_Master': 0,
 2752                        'Skip_Counter': 0,
 2753                        'Slave_IO_Running': 'Yes',
 2754                        'Slave_IO_State': 'Waiting for master to send event',
 2755                        'Slave_SQL_Running': 'Yes',
 2756                        'Until_Condition': 'None',
 2757                        'Until_Log_File': '',
 2758                        'Until_Log_Pos': 0}}
 2759 
 2760     CLI Example:
 2761 
 2762     .. code-block:: bash
 2763 
 2764         salt '*' mysql.get_slave_status
 2765 
 2766     """
 2767     mod = sys._getframe().f_code.co_name
 2768     log.debug("%s<--", mod)
 2769     conn = _connect(**connection_args)
 2770     if conn is None:
 2771         return []
 2772     rtnv = __do_query_into_hash(conn, "SHOW SLAVE STATUS")
 2773     conn.close()
 2774 
 2775     # check for if this minion is not a slave
 2776     if not rtnv:
 2777         rtnv.append([])
 2778 
 2779     log.debug("%s-->%s", mod, len(rtnv[0]))
 2780     return rtnv[0]
 2781 
 2782 
 2783 def showvariables(**connection_args):
 2784     """
 2785     Retrieves the show variables from the minion.
 2786 
 2787     Returns::
 2788         show variables full dict
 2789 
 2790     CLI Example:
 2791 
 2792     .. code-block:: bash
 2793 
 2794         salt '*' mysql.showvariables
 2795 
 2796     """
 2797     mod = sys._getframe().f_code.co_name
 2798     log.debug("%s<--", mod)
 2799     conn = _connect(**connection_args)
 2800     if conn is None:
 2801         return []
 2802     rtnv = __do_query_into_hash(conn, "SHOW VARIABLES")
 2803     conn.close()
 2804     if not rtnv:
 2805         rtnv.append([])
 2806 
 2807     log.debug("%s-->%s", mod, len(rtnv[0]))
 2808     return rtnv
 2809 
 2810 
 2811 def showglobal(**connection_args):
 2812     """
 2813     Retrieves the show global variables from the minion.
 2814 
 2815     Returns::
 2816         show global variables full dict
 2817 
 2818     CLI Example:
 2819 
 2820     .. code-block:: bash
 2821 
 2822         salt '*' mysql.showglobal
 2823 
 2824     """
 2825     mod = sys._getframe().f_code.co_name
 2826     log.debug("%s<--", mod)
 2827     conn = _connect(**connection_args)
 2828     if conn is None:
 2829         return []
 2830     rtnv = __do_query_into_hash(conn, "SHOW GLOBAL VARIABLES")
 2831     conn.close()
 2832     if not rtnv:
 2833         rtnv.append([])
 2834 
 2835     log.debug("%s-->%s", mod, len(rtnv[0]))
 2836     return rtnv
 2837 
 2838 
 2839 def verify_login(user, password=None, **connection_args):
 2840     """
 2841     Attempt to login using the provided credentials.
 2842     If successful, return true.  Otherwise, return False.
 2843 
 2844     CLI Example:
 2845 
 2846     .. code-block:: bash
 2847 
 2848         salt '*' mysql.verify_login root password
 2849     """
 2850     # Override the connection args for username and password
 2851     connection_args["connection_user"] = user
 2852     connection_args["connection_pass"] = password
 2853 
 2854     dbc = _connect(**connection_args)
 2855     if dbc is None:
 2856         # Clear the mysql.error if unable to connect
 2857         # if the connection fails, we simply return False
 2858         if "mysql.error" in __context__:
 2859             del __context__["mysql.error"]
 2860         return False
 2861     return True
 2862 
 2863 
 2864 def plugins_list(**connection_args):
 2865     """
 2866     Return a list of plugins and their status
 2867     from the ``SHOW PLUGINS`` query.
 2868 
 2869     CLI Example:
 2870 
 2871     .. code-block:: bash
 2872 
 2873         salt '*' mysql.plugins_list
 2874     """
 2875     dbc = _connect(**connection_args)
 2876     if dbc is None:
 2877         return []
 2878     cur = dbc.cursor()
 2879     qry = "SHOW PLUGINS"
 2880     try:
 2881         _execute(cur, qry)
 2882     except MySQLdb.OperationalError as exc:
 2883         err = "MySQL Error {}: {}".format(*exc.args)
 2884         __context__["mysql.error"] = err
 2885         log.error(err)
 2886         return []
 2887 
 2888     ret = []
 2889     results = cur.fetchall()
 2890     for dbs in results:
 2891         ret.append({"name": dbs[0], "status": dbs[1]})
 2892 
 2893     log.debug(ret)
 2894     return ret
 2895 
 2896 
 2897 def plugin_add(name, soname=None, **connection_args):
 2898     """
 2899     Add a plugina.
 2900 
 2901     CLI Example:
 2902 
 2903     .. code-block:: bash
 2904 
 2905         salt '*' mysql.plugin_add auth_socket
 2906     """
 2907     if not name:
 2908         log.error("Plugin name is required.")
 2909         return False
 2910 
 2911     if plugin_status(name, **connection_args):
 2912         log.error("Plugin %s is already installed.", name)
 2913         return True
 2914 
 2915     dbc = _connect(**connection_args)
 2916     if dbc is None:
 2917         return False
 2918     cur = dbc.cursor()
 2919     qry = "INSTALL PLUGIN {}".format(name)
 2920 
 2921     if soname:
 2922         qry += ' SONAME "{}"'.format(soname)
 2923     else:
 2924         qry += ' SONAME "{}.so"'.format(name)
 2925 
 2926     try:
 2927         _execute(cur, qry)
 2928     except MySQLdb.OperationalError as exc:
 2929         err = "MySQL Error {}: {}".format(*exc.args)
 2930         __context__["mysql.error"] = err
 2931         log.error(err)
 2932         return False
 2933 
 2934     return True
 2935 
 2936 
 2937 def plugin_remove(name, **connection_args):
 2938     """
 2939     Remove a plugin.
 2940 
 2941     CLI Example:
 2942 
 2943     .. code-block:: bash
 2944 
 2945         salt '*' mysql.plugin_remove auth_socket
 2946     """
 2947     if not name:
 2948         log.error("Plugin name is required.")
 2949         return False
 2950 
 2951     if not plugin_status(name, **connection_args):
 2952         log.error("Plugin %s is not installed.", name)
 2953         return True
 2954 
 2955     dbc = _connect(**connection_args)
 2956     if dbc is None:
 2957         return False
 2958     cur = dbc.cursor()
 2959     qry = "UNINSTALL PLUGIN {}".format(name)
 2960     args = {}
 2961     args["name"] = name
 2962 
 2963     try:
 2964         _execute(cur, qry)
 2965     except MySQLdb.OperationalError as exc:
 2966         err = "MySQL Error {}: {}".format(*exc.args)
 2967         __context__["mysql.error"] = err
 2968         log.error(err)
 2969         return False
 2970 
 2971     return True
 2972 
 2973 
 2974 def plugin_status(name, **connection_args):
 2975     """
 2976     Return the status of a plugin.
 2977 
 2978     CLI Example:
 2979 
 2980     .. code-block:: bash
 2981 
 2982         salt '*' mysql.plugin_status auth_socket
 2983     """
 2984     if not name:
 2985         log.error("Plugin name is required.")
 2986         return False
 2987 
 2988     dbc = _connect(**connection_args)
 2989     if dbc is None:
 2990         return ""
 2991     cur = dbc.cursor()
 2992     qry = "SELECT PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = %(name)s"
 2993     args = {}
 2994     args["name"] = name
 2995 
 2996     try:
 2997         _execute(cur, qry, args)
 2998     except MySQLdb.OperationalError as exc:
 2999         err = "MySQL Error {}: {}".format(*exc.args)
 3000         __context__["mysql.error"] = err
 3001         log.error(err)
 3002         return ""
 3003 
 3004     try:
 3005         status = cur.fetchone()
 3006         if status is None:
 3007             return ""
 3008         else:
 3009             return status[0]
 3010     except IndexError:
 3011         return ""