My solution was to use ansible mysql_user module.
It maintains the user permissions without revoking everything.
Grant a few permissions in test
db on testtable
table:
playbook.yaml
content:
---
- name:
hosts: localhost
vars:
ansible_python_interpreter: /usr/bin/python3
tasks:
- name: Test user
community.mysql.mysql_user:
name: testuser
password: testpassword
priv:
'test.testtable': SELECT,INSERT,ALTER
login_host: 127.0.0.1
login_user: root
login_password: root
state: present
Run ansible-playbook playbook.yaml
Then revoke ALTER
by removing from priv
:
'test.testtable': SELECT,INSERT
run again ansible-playbook playbook.yaml
I checked mysql logs to make sure it does not revoke all, before grant:
2022-10-10T14:34:54.911222Z 1632 Query SHOW GRANTS FOR 'testuser'@'localhost'
2022-10-10T14:34:54.911797Z 1632 Query REVOKE ALTER ON `test`.`testtable` FROM 'testuser'@'localhost'
2022-10-10T14:34:54.947049Z 1632 Query FLUSH PRIVILEGES
2022-10-10T14:34:54.951583Z 1632 Query SHOW GRANTS FOR 'testuser'@'localhost'