CODEWITHSUNDEEP
mysqlansible
jdog
jdog

Reputation: 2539

GRANT PROCESS with Ansible mysql_user module

I need to grant ALL PRIVILEGES on a specific database as well as PROCESS rights to a mysql user.

I have tried a number of combinations, but none of them work:

mysql_user: priv="{{ item.name | regex_replace ('[.-]', '_') }}.*:ALL/PROCESS" login_host="{{mysql.endpoint.address}}" login_port=3306 login_user="{{mysql_root_user}}" login_password="{{mysql_root_password}}" name="{{item.user}}" password="{{item.password}}" host=% state=present

Result: "invalid privileges string: list index out of range"

mysql_user: priv="{{ item.name | regex_replace ('[.-]','_') }}.*:ALL/{{ item.name | regex_replace ('[.-]','_') }}.*:PROCESS" login_host="{{mysql.endpoint.address}}" login_port=3306 login_user="{{mysql_root_user}}" login_password="{{mysql_root_password}}" name="{{item.user}}" password="{{item.password}}" host=%  state=present

Result: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql_user: priv="{{ item.name | regex_replace ('[.-'_') }}.*:ALL,PROCESS" login_host="{{mysql.endpoint.address}}" login_port=3306 login_user="{{mysql_root_user}}" login_password="{{mysql_root_password}}" name="{{item.user}}" password="{{item.password}}" host=%  state=present

(from https://stackoverflow.com/a/50785241/123594 - I'm feeling desperate)

Result: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROCESS ON ...

How do I GRANT PROCESS rights to a user with Ansible mysql_user?

Upvotes: 1

Views: 4401

Answers (2)

β.εηοιτ.βε
β.εηοιτ.βε

Reputation: 39169

Your issue is coming from the fact that, unlike the other question your are pointing at, you are trying to GRANT the PROCESS privilege to a specific database, and this is not possible in MySQL.

An example from the MySQL command line right away:

mysql> GRANT PROCESS ON my_db.* TO usr;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

This happens because the PROCESS privilege is a global one, so it can only be granted to *.*.

Administrative privileges enable users to manage operation of the MySQL server. These privileges are global because they are not specific to a particular database.

Source: https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

So you will have to separate the GRANT for PROCESS and for ALL.

This can still be achieved in a single task using this syntax:

Multiple privileges can be specified by separating each one using a forward slash: db.table:priv/db.table:priv.

Source: https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_user_module.html#parameter-priv

So your privileges ends up being:

"{{ item.name | regex_replace ('[.-]', '_') }}.*:ALL/*.*:PROCESS"

Here is a playbook demonstrating this:

- hosts: all
  gather_facts: no
      
  tasks:
    - mysql_user: 
        priv: >-
          {{ item.name | regex_replace ('[.-]', '_') }}.*:ALL/ 
          *.*:PROCESS 
        login_host: "{{ mysql.endpoint.address }}" 
        login_port: 3306 
        login_user: "{{ mysql_root_user }}" 
        login_password: "{{ mysql_root_password }}" 
        name: "{{ item.user }}" 
        password: "{{ item.password }}" 
        host: "%" 
        state: present
      loop: 
        - name: my.db
          user: usr
          password: pwd
      vars: 
        mysql_root_user: root
        mysql_root_password: root
        mysql:
          endpoint:
            address: mysql

Which give the recap:

PLAY [all] **********************************************************************************************************

TASK [mysql_user] ***************************************************************************************************
changed: [localhost] => (item={'name': 'my.db', 'user': 'usr', 'password': 'pwd'})

PLAY RECAP **********************************************************************************************************
localhost                  : ok=1    changed=1    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

And with the privileges:

mysql> show grants for usr;
+------------------------------------------------+
| Grants for usr@%                               |
+------------------------------------------------+
| GRANT PROCESS ON *.* TO `usr`@`%`              |
| GRANT ALL PRIVILEGES ON `my_db`.* TO `usr`@`%` |
+------------------------------------------------+
2 rows in set (0.00 sec)

Upvotes: 1

NRE
NRE

Reputation: 674

It's looks not so bad, but not easy to help you and debug with your variables.

Here is what I have to create MySQL users:

  mysql_user:
    name: "{{ db_user }}"
    host: "{{ item }}"
    login_password: "{{ rootpw }}"
    login_unix_socket: /var/lib/mysql/mysql.sock
    login_user: root
    password: "{{ db_pwd }}"
    priv: '{{ db_name }}.*:ALL,GRANT'
    state: present

Upvotes: 0

Related Questions