Brevet/dev/provisioning/ansible/roles/mariadb/tasks/config/secure-installation.yml

---
#- name: Ensure default user is present.
#  mysql_user:
#    name: "{{ mysql_user_name }}"
#    host: 'localhost'
#    password: "{{ mysql_user_password }}"
#    priv: '*.*:ALL,GRANT'
#    state: present
#  when: mysql_user_name != mysql_root_username
#
## Has to be after the password assignment, for idempotency.
#- name: Copy user-my.cnf file with password credentials.
#  template:
#    src: "user-my.cnf.j2"
#    dest: "/root/.my.cnf"
#    owner: "{{ mysql_user_name }}"
#    mode: 0600
#  when: mysql_user_name != mysql_root_username

- name: Disallow root login remotely
  command: 'mysql -NBe "{{ item }}" -S {{ mariadb_socket }}'
  with_items:
    - DELETE FROM mysql.user WHERE User='{{ mysql_root_username }}' AND Host NOT IN ('localhost', '127.0.0.1', '::1')
  changed_when: false

- name: Get list of hosts for the root user.
  command: 'mysql -NBe "{{ item }}" -S {{ mariadb_socket }}'
  with_items:
    - SELECT Host FROM mysql.user WHERE User='{{ mysql_root_username }}' ORDER BY (Host='localhost') ASC
  register: mysql_root_hosts
  changed_when: false
  check_mode: false

# Set root password for MySQL
- name: Update MySQL root password for localhost root account
  shell: >
    mysql -NBe
    'SET PASSWORD FOR "{{ mysql_root_username }}"@"{{ item }}" = PASSWORD("{{ mysql_root_password }}"); FLUSH PRIVILEGES;'
  with_items: "{{ mysql_root_hosts.stdout_lines|default([]) }}"

# Has to be after the root password assignment, for idempotency.
- name: Copy .my.cnf file with root password credentials.
  template:
    src: "root-my.cnf.j2"
    dest: "/root/.my.cnf"
    owner: root
    group: root
    mode: 0600

- name: Get list of hosts for the anonymous user.
  command: 'mysql -NBe "{{ item }}" -S {{ mariadb_socket }}'
  with_items:
    - SELECT Host FROM mysql.user WHERE User = ''
  register: mysql_anonymous_hosts
  changed_when: false
  check_mode: false

- name: Remove anonymous MySQL users.
  mysql_user:
    name: ""
    host: "{{ item }}"
    state: absent
  with_items: "{{ mysql_anonymous_hosts.stdout_lines|default([]) }}"

- name: Remove mysql users.
  mysql_user:
    name: "{{ item }}"
    login_unix_socket: "{{ mariadb_socket }}"
    state: absent
  with_items:
    - mysql

- name: Remove MySQL test database.
  mysql_db: 
    name: test
    state: absent
    login_unix_socket: "{{ mariadb_socket }}"