1#!/usr/local/bin/python3.8 2# -*- coding: utf-8 -*- 3 4# Copyright: (c) 2012, Mark Theunissen <mark.theunissen@gmail.com> 5# Sponsored by Four Kitchens http://fourkitchens.com. 6# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) 7 8from __future__ import absolute_import, division, print_function 9__metaclass__ = type 10 11DOCUMENTATION = r''' 12--- 13module: mysql_user 14short_description: Adds or removes a user from a MySQL database 15description: 16 - Adds or removes a user from a MySQL database. 17options: 18 name: 19 description: 20 - Name of the user (role) to add or remove. 21 type: str 22 required: true 23 password: 24 description: 25 - Set the user's password. 26 type: str 27 encrypted: 28 description: 29 - Indicate that the 'password' field is a `mysql_native_password` hash. 30 type: bool 31 default: no 32 host: 33 description: 34 - The 'host' part of the MySQL username. 35 type: str 36 default: localhost 37 host_all: 38 description: 39 - Override the host option, making ansible apply changes 40 to all hostnames for a given user. 41 - This option cannot be used when creating users. 42 type: bool 43 default: no 44 priv: 45 description: 46 - "MySQL privileges string in the format: C(db.table:priv1,priv2)." 47 - "Multiple privileges can be specified by separating each one using 48 a forward slash: C(db.table:priv/db.table:priv)." 49 - The format is based on MySQL C(GRANT) statement. 50 - Database and table names can be quoted, MySQL-style. 51 - If column privileges are used, the C(priv1,priv2) part must be 52 exactly as returned by a C(SHOW GRANT) statement. If not followed, 53 the module will always report changes. It includes grouping columns 54 by permission (C(SELECT(col1,col2)) instead of C(SELECT(col1),SELECT(col2))). 55 - Can be passed as a dictionary (see the examples). 56 - Supports GRANTs for procedures and functions (see the examples). 57 type: raw 58 append_privs: 59 description: 60 - Append the privileges defined by priv to the existing ones for this 61 user instead of overwriting existing ones. 62 type: bool 63 default: no 64 tls_requires: 65 description: 66 - Set requirement for secure transport as a dictionary of requirements (see the examples). 67 - Valid requirements are SSL, X509, SUBJECT, ISSUER, CIPHER. 68 - SUBJECT, ISSUER and CIPHER are complementary, and mutually exclusive with SSL and X509. 69 - U(https://mariadb.com/kb/en/securing-connections-for-client-and-server/#requiring-tls). 70 type: dict 71 version_added: 1.0.0 72 sql_log_bin: 73 description: 74 - Whether binary logging should be enabled or disabled for the connection. 75 type: bool 76 default: yes 77 state: 78 description: 79 - Whether the user should exist. 80 - When C(absent), removes the user. 81 type: str 82 choices: [ absent, present ] 83 default: present 84 check_implicit_admin: 85 description: 86 - Check if mysql allows login as root/nopassword before trying supplied credentials. 87 - If success, passed I(login_user)/I(login_password) will be ignored. 88 type: bool 89 default: no 90 update_password: 91 description: 92 - C(always) will update passwords if they differ. 93 - C(on_create) will only set the password for newly created users. 94 type: str 95 choices: [ always, on_create ] 96 default: always 97 plugin: 98 description: 99 - User's plugin to authenticate (``CREATE USER user IDENTIFIED WITH plugin``). 100 type: str 101 version_added: '0.1.0' 102 plugin_hash_string: 103 description: 104 - User's plugin hash string (``CREATE USER user IDENTIFIED WITH plugin AS plugin_hash_string``). 105 type: str 106 version_added: '0.1.0' 107 plugin_auth_string: 108 description: 109 - User's plugin auth_string (``CREATE USER user IDENTIFIED WITH plugin BY plugin_auth_string``). 110 type: str 111 version_added: '0.1.0' 112 resource_limits: 113 description: 114 - Limit the user for certain server resources. Provided since MySQL 5.6 / MariaDB 10.2. 115 - "Available options are C(MAX_QUERIES_PER_HOUR: num), C(MAX_UPDATES_PER_HOUR: num), 116 C(MAX_CONNECTIONS_PER_HOUR: num), C(MAX_USER_CONNECTIONS: num)." 117 - Used when I(state=present), ignored otherwise. 118 type: dict 119 version_added: '0.1.0' 120 121notes: 122 - "MySQL server installs with default I(login_user) of C(root) and no password. 123 To secure this user as part of an idempotent playbook, you must create at least two tasks: 124 1) change the root user's password, without providing any I(login_user)/I(login_password) details, 125 2) drop a C(~/.my.cnf) file containing the new root credentials. 126 Subsequent runs of the playbook will then succeed by reading the new credentials from the file." 127 - Currently, there is only support for the C(mysql_native_password) encrypted password hash module. 128 - Supports (check_mode). 129 130seealso: 131- module: community.mysql.mysql_info 132- name: MySQL access control and account management reference 133 description: Complete reference of the MySQL access control and account management documentation. 134 link: https://dev.mysql.com/doc/refman/8.0/en/access-control.html 135- name: MySQL provided privileges reference 136 description: Complete reference of the MySQL provided privileges documentation. 137 link: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html 138 139author: 140- Jonathan Mainguy (@Jmainguy) 141- Benjamin Malynovytch (@bmalynovytch) 142- Lukasz Tomaszkiewicz (@tomaszkiewicz) 143extends_documentation_fragment: 144- community.mysql.mysql 145 146''' 147 148EXAMPLES = r''' 149- name: Removes anonymous user account for localhost 150 community.mysql.mysql_user: 151 name: '' 152 host: localhost 153 state: absent 154 155- name: Removes all anonymous user accounts 156 community.mysql.mysql_user: 157 name: '' 158 host_all: yes 159 state: absent 160 161- name: Create database user with name 'bob' and password '12345' with all database privileges 162 community.mysql.mysql_user: 163 name: bob 164 password: 12345 165 priv: '*.*:ALL' 166 state: present 167 168- name: Create database user using hashed password with all database privileges 169 community.mysql.mysql_user: 170 name: bob 171 password: '*EE0D72C1085C46C5278932678FBE2C6A782821B4' 172 encrypted: yes 173 priv: '*.*:ALL' 174 state: present 175 176- name: Create database user with password and all database privileges and 'WITH GRANT OPTION' 177 community.mysql.mysql_user: 178 name: bob 179 password: 12345 180 priv: '*.*:ALL,GRANT' 181 state: present 182 183- name: Create user with password, all database privileges and 'WITH GRANT OPTION' in db1 and db2 184 community.mysql.mysql_user: 185 state: present 186 name: bob 187 password: 12345dd 188 priv: 189 'db1.*': 'ALL,GRANT' 190 'db2.*': 'ALL,GRANT' 191 192# Use 'PROCEDURE' instead of 'FUNCTION' to apply GRANTs for a MySQL procedure instead. 193- name: Grant a user the right to execute a function 194 community.mysql.mysql_user: 195 name: readonly 196 password: 12345 197 priv: 198 FUNCTION my_db.my_function: EXECUTE 199 state: present 200 201# Note that REQUIRESSL is a special privilege that should only apply to *.* by itself. 202# Setting this privilege in this manner is deprecated. 203# Use 'tls_requires' instead. 204- name: Modify user to require SSL connections 205 community.mysql.mysql_user: 206 name: bob 207 append_privs: yes 208 priv: '*.*:REQUIRESSL' 209 state: present 210 211- name: Modify user to require TLS connection with a valid client certificate 212 community.mysql.mysql_user: 213 name: bob 214 tls_requires: 215 x509: 216 state: present 217 218- name: Modify user to require TLS connection with a specific client certificate and cipher 219 community.mysql.mysql_user: 220 name: bob 221 tls_requires: 222 subject: '/CN=alice/O=MyDom, Inc./C=US/ST=Oregon/L=Portland' 223 cipher: 'ECDHE-ECDSA-AES256-SHA384' 224 225- name: Modify user to no longer require SSL 226 community.mysql.mysql_user: 227 name: bob 228 tls_requires: 229 230- name: Ensure no user named 'sally'@'localhost' exists, also passing in the auth credentials 231 community.mysql.mysql_user: 232 login_user: root 233 login_password: 123456 234 name: sally 235 state: absent 236 237# check_implicit_admin example 238- name: > 239 Ensure no user named 'sally'@'localhost' exists, also passing in the auth credentials. 240 If mysql allows root/nopassword login, try it without the credentials first. 241 If it's not allowed, pass the credentials 242 community.mysql.mysql_user: 243 check_implicit_admin: yes 244 login_user: root 245 login_password: 123456 246 name: sally 247 state: absent 248 249- name: Ensure no user named 'sally' exists at all 250 community.mysql.mysql_user: 251 name: sally 252 host_all: yes 253 state: absent 254 255- name: Specify grants composed of more than one word 256 community.mysql.mysql_user: 257 name: replication 258 password: 12345 259 priv: "*.*:REPLICATION CLIENT" 260 state: present 261 262- name: Revoke all privileges for user 'bob' and password '12345' 263 community.mysql.mysql_user: 264 name: bob 265 password: 12345 266 priv: "*.*:USAGE" 267 state: present 268 269# Example privileges string format 270# mydb.*:INSERT,UPDATE/anotherdb.*:SELECT/yetanotherdb.*:ALL 271 272- name: Example using login_unix_socket to connect to server 273 community.mysql.mysql_user: 274 name: root 275 password: abc123 276 login_unix_socket: /var/run/mysqld/mysqld.sock 277 278- name: Example of skipping binary logging while adding user 'bob' 279 community.mysql.mysql_user: 280 name: bob 281 password: 12345 282 priv: "*.*:USAGE" 283 state: present 284 sql_log_bin: no 285 286- name: Create user 'bob' authenticated with plugin 'AWSAuthenticationPlugin' 287 community.mysql.mysql_user: 288 name: bob 289 plugin: AWSAuthenticationPlugin 290 plugin_hash_string: RDS 291 priv: '*.*:ALL' 292 state: present 293 294- name: Limit bob's resources to 10 queries per hour and 5 connections per hour 295 community.mysql.mysql_user: 296 name: bob 297 resource_limits: 298 MAX_QUERIES_PER_HOUR: 10 299 MAX_CONNECTIONS_PER_HOUR: 5 300 301# Example .my.cnf file for setting the root password 302# [client] 303# user=root 304# password=n<_665{vS43y 305''' 306 307RETURN = '''#''' 308 309from ansible.module_utils.basic import AnsibleModule 310from ansible_collections.community.mysql.plugins.module_utils.database import SQLParseError 311from ansible_collections.community.mysql.plugins.module_utils.mysql import ( 312 mysql_connect, mysql_driver, mysql_driver_fail_msg, mysql_common_argument_spec 313) 314from ansible_collections.community.mysql.plugins.module_utils.user import ( 315 convert_priv_dict_to_str, 316 get_impl, 317 get_mode, 318 handle_requiressl_in_priv_string, 319 InvalidPrivsError, 320 limit_resources, 321 get_valid_privs, 322 privileges_unpack, 323 sanitize_requires, 324 user_add, 325 user_delete, 326 user_exists, 327 user_mod, 328) 329from ansible.module_utils._text import to_native 330 331 332# =========================================== 333# Module execution. 334# 335 336 337def main(): 338 argument_spec = mysql_common_argument_spec() 339 argument_spec.update( 340 user=dict(type='str', required=True, aliases=['name']), 341 password=dict(type='str', no_log=True), 342 encrypted=dict(type='bool', default=False), 343 host=dict(type='str', default='localhost'), 344 host_all=dict(type="bool", default=False), 345 state=dict(type='str', default='present', choices=['absent', 'present']), 346 priv=dict(type='raw'), 347 tls_requires=dict(type='dict'), 348 append_privs=dict(type='bool', default=False), 349 check_implicit_admin=dict(type='bool', default=False), 350 update_password=dict(type='str', default='always', choices=['always', 'on_create'], no_log=False), 351 sql_log_bin=dict(type='bool', default=True), 352 plugin=dict(default=None, type='str'), 353 plugin_hash_string=dict(default=None, type='str'), 354 plugin_auth_string=dict(default=None, type='str'), 355 resource_limits=dict(type='dict'), 356 ) 357 module = AnsibleModule( 358 argument_spec=argument_spec, 359 supports_check_mode=True, 360 ) 361 login_user = module.params["login_user"] 362 login_password = module.params["login_password"] 363 user = module.params["user"] 364 password = module.params["password"] 365 encrypted = module.boolean(module.params["encrypted"]) 366 host = module.params["host"].lower() 367 host_all = module.params["host_all"] 368 state = module.params["state"] 369 priv = module.params["priv"] 370 tls_requires = sanitize_requires(module.params["tls_requires"]) 371 check_implicit_admin = module.params["check_implicit_admin"] 372 connect_timeout = module.params["connect_timeout"] 373 config_file = module.params["config_file"] 374 append_privs = module.boolean(module.params["append_privs"]) 375 update_password = module.params['update_password'] 376 ssl_cert = module.params["client_cert"] 377 ssl_key = module.params["client_key"] 378 ssl_ca = module.params["ca_cert"] 379 check_hostname = module.params["check_hostname"] 380 db = '' 381 sql_log_bin = module.params["sql_log_bin"] 382 plugin = module.params["plugin"] 383 plugin_hash_string = module.params["plugin_hash_string"] 384 plugin_auth_string = module.params["plugin_auth_string"] 385 resource_limits = module.params["resource_limits"] 386 if priv and not isinstance(priv, (str, dict)): 387 module.fail_json(msg="priv parameter must be str or dict but %s was passed" % type(priv)) 388 389 if priv and isinstance(priv, dict): 390 priv = convert_priv_dict_to_str(priv) 391 392 if priv and "REQUIRESSL" in priv: 393 priv, tls_requires = handle_requiressl_in_priv_string(module, priv, tls_requires) 394 395 if mysql_driver is None: 396 module.fail_json(msg=mysql_driver_fail_msg) 397 398 cursor = None 399 try: 400 if check_implicit_admin: 401 try: 402 cursor, db_conn = mysql_connect(module, "root", "", config_file, ssl_cert, ssl_key, ssl_ca, db, 403 connect_timeout=connect_timeout, check_hostname=check_hostname) 404 except Exception: 405 pass 406 407 if not cursor: 408 cursor, db_conn = mysql_connect(module, login_user, login_password, config_file, ssl_cert, ssl_key, ssl_ca, db, 409 connect_timeout=connect_timeout, check_hostname=check_hostname) 410 except Exception as e: 411 module.fail_json(msg="unable to connect to database, check login_user and login_password are correct or %s has the credentials. " 412 "Exception message: %s" % (config_file, to_native(e))) 413 414 if not sql_log_bin: 415 cursor.execute("SET SQL_LOG_BIN=0;") 416 417 get_impl(cursor) 418 419 if priv is not None: 420 try: 421 mode = get_mode(cursor) 422 except Exception as e: 423 module.fail_json(msg=to_native(e)) 424 try: 425 valid_privs = get_valid_privs(cursor) 426 priv = privileges_unpack(priv, mode, valid_privs) 427 except Exception as e: 428 module.fail_json(msg="invalid privileges string: %s" % to_native(e)) 429 430 if state == "present": 431 if user_exists(cursor, user, host, host_all): 432 try: 433 if update_password == "always": 434 changed, msg = user_mod(cursor, user, host, host_all, password, encrypted, 435 plugin, plugin_hash_string, plugin_auth_string, 436 priv, append_privs, tls_requires, module) 437 else: 438 changed, msg = user_mod(cursor, user, host, host_all, None, encrypted, 439 plugin, plugin_hash_string, plugin_auth_string, 440 priv, append_privs, tls_requires, module) 441 442 except (SQLParseError, InvalidPrivsError, mysql_driver.Error) as e: 443 module.fail_json(msg=to_native(e)) 444 else: 445 if host_all: 446 module.fail_json(msg="host_all parameter cannot be used when adding a user") 447 try: 448 changed = user_add(cursor, user, host, host_all, password, encrypted, 449 plugin, plugin_hash_string, plugin_auth_string, 450 priv, tls_requires, module.check_mode) 451 if changed: 452 msg = "User added" 453 454 except (SQLParseError, InvalidPrivsError, mysql_driver.Error) as e: 455 module.fail_json(msg=to_native(e)) 456 457 if resource_limits: 458 changed = limit_resources(module, cursor, user, host, resource_limits, module.check_mode) or changed 459 460 elif state == "absent": 461 if user_exists(cursor, user, host, host_all): 462 changed = user_delete(cursor, user, host, host_all, module.check_mode) 463 msg = "User deleted" 464 else: 465 changed = False 466 msg = "User doesn't exist" 467 module.exit_json(changed=changed, user=user, msg=msg) 468 469 470if __name__ == '__main__': 471 main() 472