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