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