add mysql options to mysql_user module

[mohsenSy]

ISSUE TYPE

• Feature Idea

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible 2.4.0 (devel 2420bca0b5) last updated 2017/07/10 11:06:47 (GMT +300)
  config file = /etc/ansible/ansible.cfg
  configured module search path = [u'/home/mohsen/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /home/mohsen/SourceCode/ansible/lib/ansible
  executable location = /home/mohsen/SourceCode/ansible/bin/ansible
  python version = 2.7.12 (default, Nov 19 2016, 06:48:10) [GCC 5.4.0 20160609]

OS / ENVIRONMENT

Ubuntu16.04 managing Ubuntu14.04

SUMMARY

When creating a new user using mysql_user module I cannot add extra options to user created such as MAX_USER_CONNECTIONS,MAX_CONNECTIONS_PER_HOUR etc...

STEPS TO REPRODUCE

This can be used to setup resource limits for new users as described in this web page: https://dev.mysql.com/doc/refman/5.7/en/user-resources.html

   mysql_user:
      name: mohsen
      password: mohsen_pass
      state: present
      options:
         - MAX_USER_CONNECTIONS: 50

EXPECTED RESULTS

A new user called mohsen should be created and it must be limited to 50 total connections

[ansibot]

cc @Jmainguy
click here for bot help

[Nemie]

Hi, any updates on this?
We are building our mysqld_exporter role for prometheus and we are stuck on trying to set MAX_USER_CONNECTIONS with mysql_user plugin. For now we have a workaround, but would be nice to do everything in the same task instead of needing subset of tasks to complement the user privileges.

[chookhong]

I'm having the same issue in my mysqld_exporter role. Unable to set the MAX_USER_CONNECTIONS.

[petarnikolovski]

I'm also building mysqld_exporter role, it would be great to have this feature. Something like this could be a workaround:

- name: Check if mysqld_exporter user exists
  command: "mysql -u mysqld_exporter -p{{ mysqld_exporter_password }}"
  register: mysqld_exporter_user
  ignore_errors: true
  no_log: true
  changed_when: false

- name: Create mysqld_exporter user
  command: mysql -u root -e "CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY '{{ password }}' WITH MAX_USER_CONNECTIONS 3;"
  when: mysqld_exporter_user.rc != 0

- name: Copy .my.cnf file
  template:
    src: .my.cnf.j2
    dest: /etc/mysqld_exporter/.my.cnf
    owner: mysqld_exporter
    group: mysqld_exporter
    mode: 0600

- name: Make sure mysqld_exporter user has correct privileges on databases
  mysql_user:
    name: mysqld_exporter
    host: localhost
    state: present
    priv: *.*:PROCESS,REPLICATION CLIENT

[fraff]

I would also use this option.

[drzraf]

Another aspect is that IDENTIFIED VIA/WITH should be supported (eg unix_socket)
See https://dev.mysql.com/doc/refman/5.5/en/create-user.html , https://wiki.debian.org/MySql
About Prometheus, see also: prometheus/mysqld_exporter#233