Mysql Grant root permission for any host

Question

I am trying to install MySQL using salt-stack on an ec2 instance. I would like to add grant for root user. It should be accessible by any host using password.

Here's my state file:

mysql-server:
  pkg:
    - installed
    - pkgs:
      - mysql-server
      - python-mysqldb
  service:
    - running
    - name: mysql
    - enable: True
    - require:
      - pkg: mysql-server
    - watch:
      - file: /etc/mysql/my.cnf
  mysql_user:
    - present
    - name: root
    - password: {{ pillar['mysql']['server']['root_password'] }}
    - require:
      - service: mysql

/srv/.my.cnf:
  file:
    - managed
    - source: salt://files/mysql/app-my.cnf
    - user: app
    - group: app
    - mode: 0600
    - template: jinja
    - require:
      - user: app

/root/.my.cnf:
  file:
    - managed
    - source: salt://files/mysql/root-my.cnf
    - user: root
    - group: root
    - mode: 0600
    - template: jinja

{{pillar.default_database.name}}:
  mysql_database.present

mysql-app-user:
  mysql_user.present:
    - name: app
    - host: '%'
    - password: {{ pillar['mysql']['server']['root_password'] }}
    - connection_user: root
    - connection_pass: {{ pillar['mysql']['server']['root_password'] }}
    - connection_charset: utf8
    - saltenv:
      - LC_ALL: "en_US.utf8"
    - require:
      - service: mysql

mysql-app-local-user:
  mysql_user.present:
    - name: app
    - host: localhost
    - password: {{ pillar['mysql']['server']['root_password'] }}
    - connection_user: root
    - connection_pass: {{ pillar['mysql']['server']['root_password'] }}
    - connection_charset: utf8
    - saltenv:
      - LC_ALL: "en_US.utf8"
    - require:
      - service: mysql

mysql_root_remote_grant:
   mysql_grants.present:
    - grant: all privileges
    - database: '*.*'
    - user: root

mysql_app_remote_grant:
   mysql_grants.present:
    - grant: all privileges
    - database: '*.*'
    - user: app

mysql_app_local_grant:
   mysql_grants.present:
    - grant: all privileges
    - database: '*.*'
    - user: app
    - host: localhost

This part gives access to any host but without password.

mysql_root_remote_grant:
   mysql_grants.present:
    - grant: all privileges
    - database: '*.*'
    - user: root

This works, but it grants access to root from any host without password. I don't know what I am doing wrong here. How to make root accessible from any host using password with salt-stack install?

Answer 1

Here's how I solved it, I just had to add another root-mysql_user.present for %(any host) as below.

mysql-root-user-remote:
  mysql_user.present:
    - name: root
    - host: '%'
    - password: {{ pillar['mysql']['server']['root_password'] }}
    - connection_user: root
    - connection_pass: {{ pillar['mysql']['server']['root_password'] }}
    - connection_charset: utf8
    - saltenv:
      - LC_ALL: "en_US.utf8"
    - require:
      - service: mysql

So there are two mysql_user.present declaration of root user one for localhost and another for any host. After this mysql_grant.present works.