Hello,
I want to create MySQL users on several db server. Each of servers has several databases. Users can get access to databases from different hosts and with different rights. I’ve described variables in the dictionary which presented below.
Is it possible to get access to each items in the dictionary?
I’ve tried to do that via with_subelements. But unfortunately it can operate only with two iterations. My trial presented under the dictionary.
Please, could you give me a piece of advice, how I can do it.
dbservers:
server1:
name: “server1”
databases:
server1db1:
name: “server1db1”
collation: “utf8_general_ci”
encoding: “utf8”
state: “present”
server1_db2:
name: “server1db2”
collation: “utf8_general_ci”
encoding: “utf8”
state: “present”
users:
server1user1:
name: “server1user1”
pass: “server1user1_pass”
state: “present”
hosts:
localhost:
name: “localhost”
append_privs: “yes”
privs: “.:EVENT, PROCESS, REPLICATION CLIENT”
server1user2:
name: “server1user2”
pass: “server1_user2_pass”
state: “present”
hosts:
hostip1:
name: “10.0.0.1”
append_privs: “yes”
privs: “.:PROCESS, SUPER”
hostip2:
name: “10.0.1.185”
append_privs: “yes”
privs: “server1_db2.:USAGE"
server2:
name: “server2”
databases:
server2db1:
name: “server2db1”
collation: “utf8_general_ci”
encoding: “utf8”
state: “present”
server2_db2:
name: “server2db2”
collation: “utf8_general_ci”
encoding: “utf8”
state: “present”
server2_db3:
name: “server2db3”
collation: “utf8_general_ci”
encoding: “utf8”
state: “present”
users:
server2user1:
name: “server2user1”
pass: “server2user1_pass”
state: “present”
hosts:
localhost:
name: “localhost”
append_privs: “yes”
privs: ".*:EVENT, PROCESS, REPLICATION CLIENT”
server2user2:
name: “server2user2”
pass: “server2user2_pass”
state: “present”
hosts:
all:
name: “%”
append_privs: “yes”
privs:
- “server2_db1.*:USAGE”
- “server2_db3.*:ALL”
My trial:
- name: Ensure MySQL users are present.
mysql_user:
name: “{{ item.1.name }}”
password: “{{ item.1.pass|default ‘Pa$$word’ }}”
state: “{{ item.1.state|default ‘yes’ }}”
host: “{{ item.2.name|default “localhost” }}”
priv: “{{ item.2.privs|default “.:USAGE” }}”
append_privs: “{{ item.2.append_privs|default “yes” }}”
with_subelements: - dbservers
- users
- hosts
when: (inventory_hostname == “{{ item.0.name }}”)