---
- name: Install mysql
  ansible.builtin.apt:
    pkg:
      - mysql-server
      - python3-mysqldb
    state: present
    update_cache: true
    cache_valid_time: 3600

- name: Ensure the mysql service is enabled and started
  ansible.builtin.service:
    name: mysql
    state: started
    enabled: true

- name: Update mysql root password
  vars: &ansible_interpreter
    ansible_python_interpreter: /usr/bin/python3
  mysql_user:
    name: root
    host: "%"
    login_user: root
    login_password: "{{ mysql_root_password }}"
    check_implicit_admin: true
    priv: "*.*:ALL,GRANT"

- name: Create sample database
  vars: *ansible_interpreter
  mysql_db:
    name: sample
    state: present
    login_user: root

- name: Copy sample data
  ansible.builtin.copy:
    src: create_table.sql
    dest: /tmp/create_table.sql

- name: Insert sample data into database
  vars: *ansible_interpreter
  mysql_db:
    name: sample
    state: import
    target: /tmp/create_table.sql
    login_user: root
    login_password: "{{ mysql_root_password }}"

- name: Make MySQL listen on all interfaces 
  ansible.builtin.lineinfile:
    path: /etc/mysql/mysql.conf.d/mysqld.cnf
    regexp: '^bind-address\s+=\s+.*$'
    line: "bind-address = 0.0.0.0"
    state: present
    backup: true
  notify: Restart MySQL

- name: Create a user that has access to sample db - Only root should have access to database
  vars: *ansible_interpreter
  mysql_user:
    name: "{{ vunerable_user_username }}"
    password: "{{ vunerable_user_password }}"
    host: "%"
    priv: "sample.*:ALL"
    login_user: root
    login_password: "{{ mysql_root_password }}"
    state: present

- name: Create a new system user
  ansible.builtin.user:
    name: "malicious"
    password: "malicious"
    state: present

- name: Set new user as owner of MySQL configuration files - Config should be owned by the root user
  ansible.builtin.file:
    path: "/etc/mysql"
    owner: "malicious"
    recurse: true
  notify: Restart MySQL

- name: Set the environment variable for mysql password - password should not be stored in env variable
  ansible.builtin.blockinfile:
    dest: /etc/environment
    block: |
      MYSQL_PWD={{ mysql_root_password }}
      EDITOR=vim

- name: Create test database - all test databases should not be present
  vars: *ansible_interpreter
  mysql_db:
    name: test 
    state: present
    login_user: root

- name: Create user without password - all users should have a password
  vars: *ansible_interpreter
  mysql_user:
    name: "i_have_no_password"
    password: ""
    host: "%"
    priv: "*.*:ALL,GRANT" # Only root should have GRANT 
    login_user: root
    login_password: "{{ mysql_root_password }}"
    state: present


- name: Create anonymous user - all users should have a username
  vars: *ansible_interpreter
  mysql_user:
    name: ""
    password: ""
    host: "%"
    priv: "*.*:ALL,GRANT"
    login_user: root
    login_password: "{{ mysql_root_password }}"
    state: present