Ikar Pohorský Ikar Pohorský - 23 days ago 14
MySQL Question

How to grant MySQL server administration privileges (SUPER, RELOAD...) with ansible?

Is there a way how to grant MySQL administration privileges using Ansible mysql_user module (or using any other module)? I want to set

SUPER
,
RELOAD
and
SHOW DATABASES
privileges to the user along with some other database-specific privs.

Following basic setup works well for me:

- name: Set user privileges
mysql_user:
user={{ mysql_user }}
password={{ mysql_password }}
state=present
priv={{ item }}
with_items:
- 'somedatabase.*:ALL'
- 'someotherdatabase.*:ALL'


...results in:

TASK: [db | Set user privileges]
**********************************************
ok: [dbuser] => (item=somedatabase.*:ALL)
ok: [dbuser] => (item=someotherdatabase.*:ALL)





Following setup keeps saying "changed" and the privileges are not what one would expect:

- name: Set user privileges
mysql_user:
user={{ mysql_user }}
password={{ mysql_password }}
state=present
priv={{ item }}
with_items:
- '*.*:SUPER,RELOAD,SHOW\ DATABASES'
- 'somedatabase.*:ALL'
- 'someotherdatabase.*:ALL'


(repeated) run:

TASK: [db | Set user privileges]
**********************************************
changed: [dbuser] => (item=*.*:SUPER,RELOAD,SHOW\ DATABASES)
changed: [dbuser] => (item=somedatabase.*:ALL)
ok: [dbuser] => (item=someotherdatabase.*:ALL)


results in:

mysql> show grants for 'dbuser'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for dbuser@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dbuser'@'localhost' IDENTIFIED BY PASSWORD '*2046D2DDAE359F311435E8B4D3776EFE13FB584C' |
| GRANT ALL PRIVILEGES ON `somedatabase`.* TO 'dbuser'@'localhost' |
| GRANT ALL PRIVILEGES ON `someotherdatabase`.* TO 'dbuser'@'localhost' |
+---------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)


Does anyone know how to:


  1. set
    SUPER
    ,
    RELOAD
    and
    SHOW DATABASE
    admin. privileges?

  2. make the configuration idempotent?


Answer

Found out the elegant solution after all! First of all the privileges should be defined somewhere as a list:

$ cat group_vars/dbservers
mysql_privileges:
  - 'somedatabase.*:ALL'
  - 'someotherdatabase.*:ALL'
  - '*.*:SUPER,RELOAD,SHOW\ DATABASES'

then the mysql_user plugin does not need to append the privileges, simply use the privileges string mentioned in the documentation in following format: mydb.*:INSERT,UPDATE/anotherdb.*:SELECT/yetanotherdb.*:ALL.

The only trick is how to convert list to the string:

- name: Set user privileges
  mysql_user:
    user={{ mysql_user }}
    password={{ mysql_password }}
    state=present
    priv={{ mysql_privileges|join('/') }}

The repeatable run of the task does not say changed anymore:

TASK: [db | Set user privileges]
**********************************************
ok: [dbuser]