With Ansible, we can manage MySQL users by simply indicating the necessary parameters and executing the playbook.
To manage MySQL with
Ansible
, we need to first install certain dependencies on all servers to be managed, depending on the operating system:
emerge -av dev-python/mysql-python
apt install python-mysqldb
Creamos los directorios necesarios para Ansible:
mkdir /etc/ansible/
chown -R root:kr0m /etc/ansible/
chmod 775 /etc/ansible/
chown -R root:kr0m /etc/ansible/
chmod 775 /etc/ansible/
Creamos un grupo de servidores llamado test:
vi /etc/ansible/hosts
[test]
SERVER1
SERVER2
We check the current users:
mysql> SELECT user, host FROM user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
Generamos un playbook que añadirá un usuario llamado bob desde la ip 1.1.1.1 con password 12345 el cual podrá realizar SELECTs sobre todas las tablas de la base de datos test:
vi mysqlUsers.yml
- hosts: test
tasks:
- name: Set bob mysql user grants
mysql_user:
host: 1.1.1.1
name: bob
password: 12345
priv: "test.*:SELECT"
state: present
Aplicamos el playbook:
ansible-playbook mysqlUsers.yml
Volvemos a comprobar los usuario y los grants:
mysql> SELECT user, host FROM user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| bob | 1.1.1.1 |
| debian-sys-maint | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
mysql> SHOW GRANTS FOR bob@'1.1.1.1';
+---------------------------------------------+
| Grants for bob@1.1.1.1 |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'bob'@'1.1.1.1' |
| GRANT SELECT ON `test`.* TO 'bob'@'1.1.1.1' |
+---------------------------------------------+