Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mysql

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?
like image 266
Ikar Pohorský Avatar asked Apr 09 '14 09:04

Ikar Pohorský


People also ask

How do I grant MySQL privileges?

To GRANT ALL privileges to a user , allowing that user full control over a specific database , use the following syntax: mysql> GRANT ALL PRIVILEGES ON database_name. * TO 'username'@'localhost';

How do I grant super privileges in MySQL 8?

To add super privileges to MySQL database, the following is the syntax. mysql> GRANT SUPER ON *. * TO user@'localhost' IDENTIFIED BY 'passwordName'; After executing the above query, do not forget to end it with the following command.

How do I update MySQL privileges?

You can't currently change a user's privileges in the control panel, so to do so you need to use a command-line MySQL client like mysql . After you create a user in the cluster, connect to the cluster as doadmin or another admin user.


2 Answers

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]
like image 144
Ikar Pohorský Avatar answered Oct 02 '22 13:10

Ikar Pohorský


There's no need for tricks with lists, you can set multiple privileges separated by a slash:

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

or shorter:

- name: Set user privileges
  mysql_user: user={{ mysql_user }} 
  password={{ mysql_password }} 
  state=present
   priv='somedatabase.*:ALL/someotherdatabase.*:ALL/*.*:SUPER,RELOAD,SHOW DATABASES'
like image 31
Zoltán Avatar answered Oct 02 '22 12:10

Zoltán