msg": "Error granting privileges, invalid priv string: ALL" #462

razerrazer · 2022-11-06T21:18:13Z

SUMMARY

When I run my playbook I try to set up a simple user with a database from your FAQ.

Creating a database : works

But when it tries to set a user with privileges it doesn't work.
I've tried like 100 different types of 'bob.*:ALL' etc.. but nothing works

with and without "" and ''

ISSUE TYPE

Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible [core 2.13.5]

COLLECTION VERSION

Collection        Version
----------------- -------
community.general 5.7.0
community.mysql       3.5.1
also tested it with: community.mysql       3.5.0

CONFIGURATION

#Ansible config:
- name: Create .my.cnf
  template:
   src: "templates/my.cnf.j2"
   dest: "/root/.my.cnf"
   owner: root
   group: root
   mode: 0600

- name: Removes anonymous user account for localhost
  mysql_user:
    name: ''
    host: localhost
    state: absent

- name: Create database user with name bob
  mysql_user:
    name: bob
    password: {{my_db_password}}
    priv: '*.*:ALL'
    state: present

OS / ENVIRONMENT

Ansible Host: Linux Mint 21
Target:  Debian 11 / MariaDB 10.9

ACTUAL RESULTS

The full traceback is:
"/tmp/ansible_mysql_user_payload_p_u38pra/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/user.py", line 725, in privileges_grant
    raise InvalidPrivsError("Error granting privileges, invalid priv string: %s" % priv_string)
fatal: [172.16.200.37]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "append_privs": false,
            "ca_cert": null,
            "check_hostname": null,
            "check_implicit_admin": false,
            "client_cert": null,
            "client_key": null,
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "encrypted": false,
            "force_context": false,
            "host": "localhost",
            "host_all": false,
            "login_host": "localhost",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": null,
            "name": "bob",
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "plugin": null,
            "plugin_auth_string": null,
            "plugin_hash_string": null,
            "priv": "*.*:ALL",
            "resource_limits": null,
            "sql_log_bin": true,
            "state": "present",
            "subtract_privs": false,
            "tls_requires": null,
            "update_password": "always",
            "user": "bob"
        }
    },
    "msg": "Error granting privileges, invalid priv string: ALL"
}

The text was updated successfully, but these errors were encountered:

razerrazer · 2022-11-09T10:54:11Z

Same issue with MariaDB 10.5.15

Anyone ?

razerrazer · 2022-11-20T17:52:31Z

I can confirm this is a bug when you trying to install mysql user on a Debian 11 machine.

It's working as it should in Rocky Linux v9 without any problems.
So i guess this is something you have to take a deeper look at.

markuman · 2023-02-08T08:30:37Z

If the ALL priv is supported depends on the database provider.
In case of AWS RDS (as a database provider), ALL is also not available. The only way to work around this is to name every needed privilege.

Basically you name the privileges in a ALL variable and replace the ALL in a string.

---
- name: prepare nextcloud container
  hosts: localhost
  connection: local

  tasks:
    - name: test
      vars:
        mysql_parameters: &mysql_params
          login_user: root
          login_password: mariadb
          login_host: 127.0.0.1
          login_port: 33066
        ALL: "ALTER,ALTER ROUTINE,CREATE,CREATE ROUTINE,CREATE TEMPORARY TABLES,CREATE VIEW,DELETE,DROP,EVENT,EXECUTE,INDEX,INSERT,LOCK TABLES,REFERENCES,SELECT,SHOW VIEW,TRIGGER,UPDATE"

      block:
        - name: start nextcloud container
          register: out
          community.docker.docker_container:
            name: mariadb_test_container
            image: mariadb:10.5.15
            state: started
            auto_remove: true
            recreate: true
            container_default_behavior: no_defaults
            published_ports:
              - "127.0.0.1:{{ mysql_parameters.login_port }}:3306"
            env:
              MARIADB_ROOT_PASSWORD: "{{ mysql_parameters.login_password }}"

        - name: mariadb container needs some sec for initialization
          pause:
            seconds: 10

        - name: create user with two grants
          mysql_user:
            <<: *mysql_params
            name: testuser
            password: testpassword
            update_password: on_create
            priv: "{{ '*.*:ALL' | replace('ALL', ALL) }}"
            state: present

      always:
        - name: stop mariadb test container
          community.docker.docker_container:
            name: mariadb_test_container
            state: absent

So imho, this is not a community.mysql issue at first place.

laurent-indermuehle · 2023-03-16T12:54:39Z

@razerrazer have you tried with ALL PRIVILEGES instead of ALL. I think ALL is an alias and is not present on all mysql flavors. So normally you would write GRANT ALL PRIVILEGES TO db.* ON user@localhost.

redeyesdemonkyo · 2023-07-27T20:07:40Z

For what is worth I had this error but the issue was related to the account ansible was using to login to MySQL (v8) to try to grant that ALL priv did not actually have the grant permission. Had to first give the login account grant permission and after that ansible was able to create the users with ALL priv

Andersson007 · 2023-08-10T08:18:21Z

For what is worth I had this error but the issue was related to the account ansible was using to login to MySQL (v8) to try to grant that ALL priv did not actually have the grant permission. Had to first give the login account grant permission and after that ansible was able to create the users with ALL priv

@redeyesdemonkyo thanks for sharing the solution! So

Can we close the issue?
Should the doc be improved anyhow or it's fine as is?

arLevi · 2023-11-19T13:34:23Z

In my case, this particular instance of mysql was part of a replication cluster
which was configured with a --super-read-only flag/variable.

In that case, no Ansible commands can be run on it
See:
https://www.percona.com/blog/using-the-super_read_only-system-variable/
which says:

It is well known that replica servers in a master/slave configuration,
to avoid breaking replication due to duplicate keys, missing rows or
other similar issues, should not receive write queries.

pschiffe · 2024-02-23T22:57:11Z

I see this error now with mariadb:11.3 official docker image as a server (user and db name is the same):

failed: [host] (item={'key': 'user', 'value': 'pw'}) => {"ansible_loop_var": "item", "changed": false, "item": {"key": "user", "value": "pw"}, "msg": "Error granting privileges, invalid priv string: ALL PRIVILEGES , params: ('user', '%'), query: GRANT ALL PRIVILEGES ON `user`.* TO %s@%s , exception: (1044, \"Access denied for user 'root'@'%' to database 'user'\")."}

Tried both ALL and ALL PRIVILEGES variants.

mariadb:11.2 works fine without this error.

laurent-indermuehle · 2024-02-24T19:40:38Z

Hi @pschiffe and thanks for reporting the issue on 11.3.

I believe we test only long term support versions, which are atm 10.4, 10.5, 10.6 and 10.11. The v11 is still kind of a beta. I'm not saying we won't fix an issue on v11, because at some point they will release a LTS on that branch too. Just saying we haven't tested it yet.

@pschiffe, could you paste the whole community.mysql.mysql_user task so we can see what you tried to achieve please. It could be that the user you're using doesn't have enough rights to create a new user?

pschiffe · 2024-02-24T20:07:02Z

Yeah, I understand, thanks for getting back to me. Here's the task. The user is root, and before this task, I'm creating other users with privileges like priv: '*.*:RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR, CONNECTION ADMIN' and priv: '*.*:SELECT, PROCESS, REPLICATION CLIENT, RELOAD' and these tasks works. The issue is only with ALL priv:

- name: Create db users
  community.mysql.mysql_user:
    name: '{{ item.key }}'
    password: '{{ item.value }}'
    host: '%'
    priv: '{{ item.key }}.*:ALL'
    login_user: root
    login_password: '{{ mariadb_root_pw }}'
    login_host: '{{ db_result.container.NetworkSettings.Networks[mariadb_network_name].IPAddress }}'
    state: present
  loop: '{{ mariadb_users | dict2items }}'

laurent-indermuehle · 2024-02-25T19:56:51Z

What grants do root@'%' have?
Can you paste the results of SHOW GRANTS FOR root@'%'; please @pschiffe?

pschiffe · 2024-02-26T14:53:46Z

Mariadb 11.2:

MariaDB [(none)]> SHOW GRANTS FOR root@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '******' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

Mariadb 11.3:

MariaDB [(none)]> SHOW GRANTS FOR root@'%';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '******' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

Amazing, this is the same DB on a volume, I've changed only the image.

laurent-indermuehle · 2024-02-26T19:14:00Z

Interesting! @pschiffe have you created the root@'%' user using the Ansible module community.mysql.mysql_user in both cases? Or did you created it by other means?

pschiffe · 2024-02-26T20:41:20Z

The root user was created via MARIADB_ROOT_PASSWORD env var given to the mariadb container. The original mariadb version was 11.something.

laurent-indermuehle · 2024-02-27T08:30:31Z

@pschiffe I'm not able to reproduce what you experienced above. Maybe you altered one of the root accounts at some point?
Also, since the container creates 2 accounts, root@localhost and root@'%', be careful which one is used by the Ansible module.

podman run -d -ti --env MARIADB_ROOT_PASSWORD=manager --name mariadb112 mariadb:11.2
podman exec -it mariadb112 bash                                                                                           
root@9d0b8edb089e:/# mariadb -pmanager

MariaDB [(none)]> select user, host from mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| root        | %         |
| healthcheck | 127.0.0.1 |
| healthcheck | ::1       |
| healthcheck | localhost |
| mariadb.sys | localhost |
| root        | localhost |
+-------------+-----------+
6 rows in set (0.001 sec)

MariaDB [(none)]> show grants for root@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

podman run -d -ti --env MARIADB_ROOT_PASSWORD=manager --name mariadb113 mariadb:11.3
podman exec -it mariadb113 bash
root@746a6dc0605e:/# mariadb -pmanager

MariaDB [(none)]> select user, host from mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| root        | %         |
| healthcheck | 127.0.0.1 |
| healthcheck | ::1       |
| healthcheck | localhost |
| mariadb.sys | localhost |
| root        | localhost |
+-------------+-----------+
6 rows in set (0.002 sec)

MariaDB [(none)]> show grants for root@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

pschiffe · 2024-02-27T11:42:43Z

Thank you @laurent-indermuehle for taking the time to look into this. I can confirm I can see the same as you in this case.

To reproduce my issue, do the following:

podman run -d -ti --env MARIADB_ROOT_PASSWORD=manager --name mariadb11 -v mariadb:/var/lib/mysql mariadb:11.2
podman exec -it mariadb11 bash
root@3f0e6649e7b1:/# mariadb -pmanager

MariaDB [(none)]> show grants for root@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

So far, so good. Now exit the container and continue:

podman rm -f mariadb11
podman run -d -ti --env MARIADB_ROOT_PASSWORD=manager --name mariadb11 -v mariadb:/var/lib/mysql mariadb:11.3
podman exec -it mariadb11 bash
root@ad62bd011ef3:/# mariadb -pmanager

MariaDB [(none)]> show grants for root@'%';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

Even after running mariadb-upgrade -pmanager it's the same.

laurent-indermuehle · 2024-02-27T19:03:51Z

@pschiffe thank you for testing again. One thing I left out is that I downloaded the containers from docker.io. I don't know if it's the same image than Quay.io or other registries.

I now see that you're creating the mysql schema with 11.2 then upgrade it to 11.3. But the question is, do the Ansible module fails to creates the new user if you start from a fresh 11.3?

On a side note, it bother me to not be able to find a documentation about why the grants are differents in this case. I see nothing in the script that built the container 11.3: https://github.com/MariaDB/mariadb-docker/blob/master/11.3/Dockerfile neither in the changelog: https://mariadb.com/kb/en/changes-improvements-in-mariadb-11-3/#privileges

pschiffe · 2024-02-27T20:35:10Z

I'm also using images from Docker Hub.

But the question is, do the Ansible module fails to creates the new user if you start from a fresh 11.3?

Ansible module works fine when starting from fresh 11.3.

I also didn't find any changes related to this behavior. I'm starting to believe that this is a bug in MariaDB directly :(

pschiffe · 2024-02-27T21:11:44Z

I've reported it to the MariaDB bug tracker: https://jira.mariadb.org/browse/MDEV-33554

lahwaacz · 2024-08-23T12:25:59Z

I've reported it to the MariaDB bug tracker: https://jira.mariadb.org/browse/MDEV-33554

According to the report, this was fixed in MariaDB 11.4.2. Can this be closed?

Andersson007 · 2024-08-26T07:37:09Z

@lahwaacz hello, thanks for letting us know!

What the other folks thinks? Can we close the issue?

laurent-indermuehle · 2024-08-26T10:05:02Z

@pschiffe can you confirm this bug is gone on 11.4.2+ ?
@Andersson007 since it's a MariaDB bug, I think we can close this issue. But we could create a workaround for the affected versions, if enough people need it.

pschiffe · 2024-08-26T20:10:08Z

Yep, this is fixed for me now.

Andersson007 · 2024-08-27T08:30:23Z

Thanks everyone!

This was referenced Nov 10, 2022

Display a more informative error when msg": "Error granting privileges, invalid priv string: ALL" is returned. #465

Closed

Display a more informative error when InvalidPrivsError is raised (#465) #466

Merged

Andersson007 closed this as completed Aug 27, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

msg": "Error granting privileges, invalid priv string: ALL" #462

msg": "Error granting privileges, invalid priv string: ALL" #462

razerrazer commented Nov 6, 2022 •

edited

Loading

razerrazer commented Nov 9, 2022

razerrazer commented Nov 20, 2022

markuman commented Feb 8, 2023

laurent-indermuehle commented Mar 16, 2023

redeyesdemonkyo commented Jul 27, 2023

Andersson007 commented Aug 10, 2023

arLevi commented Nov 19, 2023 •

edited

Loading

pschiffe commented Feb 23, 2024

laurent-indermuehle commented Feb 24, 2024

pschiffe commented Feb 24, 2024

laurent-indermuehle commented Feb 25, 2024

pschiffe commented Feb 26, 2024

laurent-indermuehle commented Feb 26, 2024

pschiffe commented Feb 26, 2024

laurent-indermuehle commented Feb 27, 2024

pschiffe commented Feb 27, 2024

laurent-indermuehle commented Feb 27, 2024

pschiffe commented Feb 27, 2024

pschiffe commented Feb 27, 2024

lahwaacz commented Aug 23, 2024

Andersson007 commented Aug 26, 2024

laurent-indermuehle commented Aug 26, 2024

pschiffe commented Aug 26, 2024

Andersson007 commented Aug 27, 2024

msg": "Error granting privileges, invalid priv string: ALL" #462

msg": "Error granting privileges, invalid priv string: ALL" #462

Comments

razerrazer commented Nov 6, 2022 • edited Loading

SUMMARY

ISSUE TYPE

COMPONENT NAME

ANSIBLE VERSION

COLLECTION VERSION

CONFIGURATION

OS / ENVIRONMENT

ACTUAL RESULTS

razerrazer commented Nov 9, 2022

razerrazer commented Nov 20, 2022

markuman commented Feb 8, 2023

laurent-indermuehle commented Mar 16, 2023

redeyesdemonkyo commented Jul 27, 2023

Andersson007 commented Aug 10, 2023

arLevi commented Nov 19, 2023 • edited Loading

pschiffe commented Feb 23, 2024

laurent-indermuehle commented Feb 24, 2024

pschiffe commented Feb 24, 2024

laurent-indermuehle commented Feb 25, 2024

pschiffe commented Feb 26, 2024

laurent-indermuehle commented Feb 26, 2024

pschiffe commented Feb 26, 2024

laurent-indermuehle commented Feb 27, 2024

pschiffe commented Feb 27, 2024

laurent-indermuehle commented Feb 27, 2024

pschiffe commented Feb 27, 2024

pschiffe commented Feb 27, 2024

lahwaacz commented Aug 23, 2024

Andersson007 commented Aug 26, 2024

laurent-indermuehle commented Aug 26, 2024

pschiffe commented Aug 26, 2024

Andersson007 commented Aug 27, 2024

razerrazer commented Nov 6, 2022 •

edited

Loading

arLevi commented Nov 19, 2023 •

edited

Loading