mysql_user: Granting all privileges at a global level always results in a change in MySQL 8.0 #77

steveteahan · 2020-12-28T00:22:10Z

SUMMARY

Granting ALL PRIVILEGES at a global level will always result in a change being detected. I came across this issue when trying to re-enable the user_password_update_test.yml test for another change. This appears to be linked to the following change in MySQL 8.0:

In MySQL 8.0 compared to previous series, SHOW GRANTS no longer displays ALL PRIVILEGES in its global-privileges output because the meaning of ALL PRIVILEGES at the global level varies depending on which dynamic privileges are defined. Instead, SHOW GRANTS explictly lists each granted global privilege
(https://dev.mysql.com/doc/refman/8.0/en/show-grants.html)

So, because the expanded list of all privileges doesn't match "ALL PRIVILEGES", a change will always be made when applying *.*:ALL. Privileges at the database and/or table level are unaffected (hopefully the more common use case).

This issue appears to be complicated by the fact that dynamic privileges are defined at runtime as per the docs. This isn't my area of expertise, but my understanding of the implication of this is that we cannot reliably hard-code dynamic privileges to check. SHOW PRIVILEGES seems to get us the list of privileges that would be applied to a user with *.*:ALL (in addition to GRANT, PROXY, and USAGE).

I can think of a couple of options:

This is the desired behavior. It can be added to the documentation and the test below can be added so the intent is clear. Possibly a warning?
Build the list of privileges to check against using SHOW PRIVILEGES when priv is *.*:ALL

Like I mentioned, this isn't my area of expertise, so it is possible that hard-coding the privileges will really work, but purely based on the documentation it seems like it wouldn't.

ISSUE TYPE

Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

Development

CONFIGURATION

Development environment

OS / ENVIRONMENT

Development environment

STEPS TO REPRODUCE

The following test will fail and reproduce the issue:

- vars:
    mysql_parameters: &mysql_params
      login_user: '{{ mysql_user }}'
      login_password: '{{ mysql_password }}'
      login_host: 127.0.0.1
      login_port: '{{ mysql_primary_port }}'

  block:

    - name: Create a user with all privileges
      mysql_user:
        <<: *mysql_params
        name: '{{ user_name_5 }}'
        password: '{{ user_password_5 }}'
        priv: '*.*:ALL'
        state: present
      register: result

    - name: Assert that there was a change because the permissions did not exist previously
      assert:
        that:
          - "result.changed == true"

    - name: Test idempotency when nothing has changed and we're still granting ALL privileges
      mysql_user:
        <<: *mysql_params
        name: '{{ user_name_5 }}'
        password: '{{ user_password_5 }}'
        priv: '*.*:ALL'
        state: present
      register: result

    - name: Assert that there wasn't a change because the user already had ALL privileges
      assert:
        that:
          - "result.changed == false"

    ##########
    # Clean up

    - name: Drop test user
      mysql_user:
        <<: *mysql_params
        name: '{{ user_name_5 }}'
        state: absent

EXPECTED RESULTS

The test above should pass because no change was made to the privileges.

ACTUAL RESULTS

TASK [test_mysql_user : Test idempotency when nothing has changed and we're still granting ALL privileges] ***
task path: /root/ansible_collections/community/mysql/tests/output/.tmp/integration/test_mysql_user-sw27zu6h-ÅÑŚÌβŁÈ/tests/integration/targets/test_mysql_user/tasks/test_grant_all_privileges.yml:33
<testhost> ESTABLISH LOCAL CONNECTION FOR USER: root
<testhost> EXEC /bin/sh -c 'echo ~root && sleep 0'
<testhost> EXEC /bin/sh -c '( umask 77 && mkdir -p "` echo /root/.ansible/tmp `"&& mkdir "` echo /root/.ansible/tmp/ansible-tmp-1609109000.0398564-8365-196033537627436 `" && echo ansible-tmp-1609109000.0398564-8365-196033537627436="` echo /root/.ansible/tmp/ansible-tmp-1609109000.0398564-8365-196033537627436 `" ) && sleep 0'
Using module file /root/ansible_collections/community/mysql/plugins/modules/mysql_user.py
<testhost> PUT /root/.ansible/tmp/ansible-local-101glv7bsmi/tmpd7a9ul1t TO /root/.ansible/tmp/ansible-tmp-1609109000.0398564-8365-196033537627436/AnsiballZ_mysql_user.py
<testhost> EXEC /bin/sh -c 'chmod u+x /root/.ansible/tmp/ansible-tmp-1609109000.0398564-8365-196033537627436/ /root/.ansible/tmp/ansible-tmp-1609109000.0398564-8365-196033537627436/AnsiballZ_mysql_user.py && sleep 0'
<testhost> EXEC /bin/sh -c '/tmp/python-ypi9raas-ansible/python /root/.ansible/tmp/ansible-tmp-1609109000.0398564-8365-196033537627436/AnsiballZ_mysql_user.py && sleep 0'
<testhost> EXEC /bin/sh -c 'rm -f -r /root/.ansible/tmp/ansible-tmp-1609109000.0398564-8365-196033537627436/ > /dev/null 2>&1 && sleep 0'
changed: [testhost] => {
    "changed": true,
    "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,
            "host": "localhost",
            "host_all": false,
            "login_host": "127.0.0.1",
            "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "login_port": 3307,
            "login_unix_socket": null,
            "login_user": "root",
            "name": "db_user5",
            "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",
            "tls_requires": null,
            "update_password": "always",
            "user": "db_user5"
        }
    },
    "msg": "Privileges updated",
    "user": "db_user5"
}

TASK [test_mysql_user : Assert that there wasn't a change because the user already had ALL privileges] ***
task path: /root/ansible_collections/community/mysql/tests/output/.tmp/integration/test_mysql_user-sw27zu6h-ÅÑŚÌβŁÈ/tests/integration/targets/test_mysql_user/tasks/test_grant_all_privileges.yml:42
fatal: [testhost]: FAILED! => {
    "assertion": "result.changed == false",
    "changed": false,
    "evaluated_to": false,
    "msg": "Assertion failed"
}

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

Andersson007 · 2020-12-28T07:36:53Z

This is not my area too. I'm against hard-coding (who will maintain this?),
so IMO better just to describe this nuance in the option description, it could be something very roughly like

- When granting all privileges using C(*.*:ALL) in MySQL 8 and later, the module always reports that the state has been changed.

tgadiev · 2021-02-10T13:40:10Z

I faced the same issue. Idempotence test fails on the following task:

    - name: Update mysql root password for all root accounts
      mysql_user:
        name: root
        host_all: true
        password: '{{ mysql_root_password }}'
        check_implicit_admin: true
        priv: '*.*:ALL,GRANT'
        login_user: '{{ mysql_root_username }}'
        login_password: '{{ mysql_root_password }}'

Jorge-Rodriguez · 2021-02-26T10:25:36Z

Related to #62 #99 #89

According to the docs MySQL 8 no longer reports ALL privileges as ALL but as the explicit list of granted privileges.

This causes the privilege parser to always detect a change in privileges

rsicart · 2021-09-21T09:26:44Z

Same problem here. Someone knows if the fix for this issue is in the roadmap?

I tried to define specific global privileges (static, dynamic) to bypass this ALL PRIVILEGES <-> "list of specific privileges" translation problem, but I've seen that there's a list of hard-coded VALID_PRIVS which doesn't contain all privileges.

Andersson007 · 2021-09-21T10:27:27Z

Same problem here. Someone knows if the fix for this issue is in the roadmap?

Not in mine unfortunately (at least in the nearest future) but I'd be happy to review things if someone picks it up and submits a fix.

I'm thinking of determining the list of possible privileges dynamically (i.e. to fetch them from a server) instead of having the hardcoded ones. Though not sure if it's possible. If anyone shares the SQL query that works on all supported versions, it would be great (if exists).
I always think that it would be nice to get rid of that frozenset when adding a new privilege to it...

Andersson007 · 2021-09-21T10:30:17Z

Though it can be painful to implement OR can lead to breaking changes introduction which i'd generally like to avoid but this frozenset is a really annoying thing.

Andersson007 · 2021-09-23T10:23:24Z

@rsicart , can this issue get closed?

Jorge-Rodriguez · 2021-09-23T10:46:56Z

If I understood the PR correctly, the actual modification checks weren't updated, so I'd say the behaviour remains unchanged

rsicart · 2021-09-23T13:19:30Z

As Tiriel said, PR #217 doesn't solve this is issue, but perhaps fixes this other issue #110

Andersson007 · 2021-09-23T13:30:04Z

Closed that one, thanks for pointing out!

tchernomax · 2022-08-09T17:53:14Z

I don't know if mysql change it's behavior since the report of this issue but for me it me it was more than a "always results in a change".

I use the module to set the root@localhost user privileges. root@localhost is also the user I use to connect to mysql.

But from what I understand, because of the diff between ['ALL', 'GRANT'] and [<all the privileges>, 'GRANT'] the module revoked <all the privileges> and 'GRANT' , then flush privileges, before trying to grant 'ALL' … but at this point my user didn't have any privileges anymore and the GRANT fails.

So now my root@localhost is useless, and I don't have any other user with the GRANT privilege… so I am "locked out" of my mysql.
Fortunately I just started building this instance, so no harm done… but I wouldn't want this to happen in my production environment :)

What I suggest : if 'ALL' is in curr_priv[db_table] then don't revoke any privileges, do the privileges_grant, redo the privileges_get and compare the privileges list with the one before privileges_grant to detect any change.
(need some tweaking for the GRANT OPTION)

Jorge-Rodriguez · 2022-08-09T18:19:53Z

@Andersson007 let's prioritize this, @tchernomax issue is quite serious.

Andersson007 · 2022-08-10T07:17:46Z

@tchernomax thanks for reporting this!

@Andersson007 let's prioritize this, @tchernomax issue is quite serious.

@Jorge-Rodriguez agreed! Would you like to pick it up?

Jorge-Rodriguez · 2022-08-10T08:05:25Z

@tchernomax thanks for reporting this!

@Andersson007 let's prioritize this, @tchernomax issue is quite serious.

@Jorge-Rodriguez agreed! Would you like to pick it up?

I can have a stab at it yes. Although another pair of eyes couldn't hurt.

Andersson007 · 2022-08-10T08:07:58Z

I can have a stab at it yes. Although another pair of eyes couldn't hurt.

Cool, thanks a lot!

ocfmatt · 2022-08-15T15:27:43Z

I've bumped into this bug using MySQL 8.

I have a play updating the root password and ensuring the privileges are correct using

  - name: Ensure root user is present 
    community.mysql.mysql_user:
      name: "{{ mysql.database_root_user }}"
      host: 'localhost'
      password: "{{ mysql.database_root_password }}"
      priv: '*.*:ALL,GRANT'
      state: present
      login_user: root
      login_password: "AnInsecurePassword"

I have plays later on trying to create users and grant permissions to databases however, this wasn't working regardless of what priv vars I tried. After finding this issue I did find the grant option was being removed from the root user in the exact same manner as described above.

Checking MySQL the Grant permission is removed:

mysql> SELECT Host,User,Grant_priv FROM mysql.user;
+---------------------------+------------------+------------+
| Host                      | User             | Grant_priv |
+---------------------------+------------------+------------+
| localhost                 | mysql.infoschema | N          |
| localhost                 | mysql.session    | N          |
| localhost                 | mysql.sys        | N          |
| localhost                 | root             | N          |
+---------------------------+------------------+------------+
4 rows in set (0.00 sec)

I did still have all other permissions as the root user so I was able to add grant back to root using an SQL update query and confirm the permission was effectively restored. @tchernomax the update query below will get you up and running again if you did get stuck where no users have the grant priv.

mysql> UPDATE mysql.user SET Grant_priv = 'Y' where User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;

mysql> SELECT Host,User,Grant_priv FROM mysql.user;
+---------------------------+------------------+------------+
| Host                      | User             | Grant_priv |
+---------------------------+------------------+------------+
| localhost                 | mysql.infoschema | N          |
| localhost                 | mysql.session    | N          |
| localhost                 | mysql.sys        | N          |
| localhost                 | root             | Y          |
+---------------------------+------------------+------------+
4 rows in set (0.00 sec)

After running my playbook again, the grant permission was removed from the root user.

mysql> SELECT Host,User,Grant_priv FROM mysql.user;
+---------------------------+------------------+------------+
| Host                      | User             | Grant_priv |
+---------------------------+------------------+------------+
| localhost                 | mysql.infoschema | N          |
| localhost                 | mysql.session    | N          |
| localhost                 | mysql.sys        | N          |
| localhost                 | root             | N          |
+---------------------------+------------------+------------+
4 rows in set (0.00 sec)

This is happening with all users where ALL,GRANT is defined. The only workaround I can use for now will be the good old fall back to the shell module and executing commands outside the mysql_user module until resolved.

Regards,
Matt.

rsicart · 2022-08-29T12:58:57Z

Just clarify that the Grant Priv revocation problem seems to be present in collection 3.4.1, but not in 2.3.8.

Problem is not present when creating new users (user_add only grants privs), but present on user modification, where grants and revokes are done.

betanummeric · 2022-08-30T15:37:48Z

@tchernomax
FYI, in case you lock yourself out of a mysql/mariadb you can recover by restarting it with --skip-grant-tables --skip-networking, connect from localhost and update the mysql.user or mysql.global_priv table, something like

update mysql.global_priv set Priv='{"access": 18446744073709551615, "authentication_string": "censored","version_id":100609,"plugin":"mysql_native_password"}' where User='root' and Host='localhost';

Then stop and start normally.
Of course, it should not come to that.

Andersson007 · 2022-09-05T09:25:20Z

@rsicart should the issue get closed? (i've released the collection version that contains your fix)

rsicart · 2022-09-05T10:35:34Z

No, at the moment we always have a change.

As @tchernomax commented, we should try to make a diff of privileges before and after configuring privileges. Perhaps something similar to a deep diff of dicts.

Andersson007 · 2022-09-05T11:14:06Z

No, at the moment we always have a change.

As @tchernomax commented, we should try to make a diff of privileges before and after configuring privileges. Perhaps something similar to a deep diff of dicts.

@rsicart ah, ok, thanks for clarifying!

Andersson007 · 2022-09-09T05:58:33Z

Thanks everyone involved!
~~The fix for this issue is available in the latest collection version, feel free to install it manually from Galaxy and use, thanks!~~

UPDATE: it's not in the release, i mixed it up with another fix, i apologize

pgrenaud · 2022-12-05T22:19:01Z

Hum, I'm a little bit late for that, but the issue doesn't seem entirely fixed. If I run ansible with --check, a change is still reported. Whereas without the --check, no change is reported.

I'm running mysql_user from community.mysql version 3.5.1 with priv: '*.*:ALL,GRANT'.

Should we reopen this issue or should I open a new one?

Andersson007 · 2022-12-06T07:26:53Z

@pgrenaud hello, thanks for reporting the case!

Andersson007 · 2022-12-06T07:30:53Z

@rsicart do you have time to take a look? ^
Looks like we forgot to cover check mode.
I think we should add the same task that you added in the PR with check_mode: yes to reproduce the case.
If you have no time, please let me know (now rush with it though)

rsicart · 2022-12-06T21:12:00Z

@rsicart do you have time to take a look? ^ Looks like we forgot to cover check mode. I think we should add the same task that you added in the PR with check_mode: yes to reproduce the case. If you have no time, please let me know (now rush with it though)

Hey @Andersson007, thanks for letting me know. I'm a little busy lately, I prefer that someone takes a look (if possible).

Andersson007 · 2022-12-07T08:50:23Z

@rsicart do you have time to take a look? ^ Looks like we forgot to cover check mode. I think we should add the same task that you added in the PR with check_mode: yes to reproduce the case. If you have no time, please let me know (now rush with it though)

Hey @Andersson007, thanks for letting me know. I'm a little busy lately, I prefer that someone takes a look (if possible).

@rsicart hey, thanks for letting us know!
let's keep it open then, if someone picks it up, it'd be cool.
If it's still open when you have easier times @rsicart, feel free to pick it up!
It doesn't look like a blocker, so it can wait imo.
I'll put help_wanted

Andersson007 mentioned this issue Feb 12, 2021

mysql_user idempotency issue with REQUIRESSL and ALL privileges #89

Closed

This was referenced Feb 26, 2021

mysql_user always reports changes when privs: *.*:ALL #104

Closed

login tracking [Temporarily frozen] #62

Draft

Create ROLES, assign them and SET default role #50

Closed

bigo8525 mentioned this issue Mar 15, 2021

mysql_user: Grant all privileges result in missing dynamic privileges in every second run in MySQL 8 #120

Closed

rsicart mentioned this issue Sep 22, 2021

mysql_user: replace VALID_PRIVS by get_valid_privs() function #217

Merged

rsicart mentioned this issue Aug 29, 2022

Fix: grant revoked priv #434

Merged

rsicart mentioned this issue Sep 5, 2022

Fix privilege changing everytime #438

Merged

renovate bot mentioned this issue Sep 5, 2022

Update Ansible collection community.mysql to v3.5.0 eyulf/homelab#161

Merged

1 task

This was referenced Sep 5, 2022

chore(deps): update dependency community.mysql to v3.5.1 linxside/ansible-ee#51

Merged

chore(deps): update dependency community.mysql to v3.5.0 shibanovp/dotfiles#78

Merged

rsicart closed this as completed in #438 Sep 8, 2022

This was referenced Sep 9, 2022

Update Ansible collection community.mysql to v3.5.1 eyulf/homelab#168

Merged

chore(deps): update dependency community.mysql to v3.5.1 shibanovp/dotfiles#84

Merged

Andersson007 reopened this Dec 6, 2022

Andersson007 added the help wanted Extra attention is needed label Dec 7, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

mysql_user: Granting all privileges at a global level always results in a change in MySQL 8.0 #77

mysql_user: Granting all privileges at a global level always results in a change in MySQL 8.0 #77

steveteahan commented Dec 28, 2020 •

edited

Loading

Andersson007 commented Dec 28, 2020 •

edited

Loading

tgadiev commented Feb 10, 2021

Jorge-Rodriguez commented Feb 26, 2021

rsicart commented Sep 21, 2021

Andersson007 commented Sep 21, 2021 •

edited

Loading

Andersson007 commented Sep 21, 2021

Andersson007 commented Sep 23, 2021

Jorge-Rodriguez commented Sep 23, 2021

rsicart commented Sep 23, 2021 •

edited

Loading

Andersson007 commented Sep 23, 2021

tchernomax commented Aug 9, 2022 •

edited

Loading

Jorge-Rodriguez commented Aug 9, 2022

Andersson007 commented Aug 10, 2022

Jorge-Rodriguez commented Aug 10, 2022

Andersson007 commented Aug 10, 2022

ocfmatt commented Aug 15, 2022 •

edited

Loading

rsicart commented Aug 29, 2022 •

edited

Loading

betanummeric commented Aug 30, 2022

Andersson007 commented Sep 5, 2022 •

edited

Loading

rsicart commented Sep 5, 2022

Andersson007 commented Sep 5, 2022

Andersson007 commented Sep 9, 2022 •

edited

Loading

pgrenaud commented Dec 5, 2022

Andersson007 commented Dec 6, 2022

Andersson007 commented Dec 6, 2022

rsicart commented Dec 6, 2022

Andersson007 commented Dec 7, 2022

mysql_user: Granting all privileges at a global level always results in a change in MySQL 8.0 #77

mysql_user: Granting all privileges at a global level always results in a change in MySQL 8.0 #77

Comments

steveteahan commented Dec 28, 2020 • edited Loading

SUMMARY

ISSUE TYPE

COMPONENT NAME

ANSIBLE VERSION

CONFIGURATION

OS / ENVIRONMENT

STEPS TO REPRODUCE

EXPECTED RESULTS

ACTUAL RESULTS

Andersson007 commented Dec 28, 2020 • edited Loading

tgadiev commented Feb 10, 2021

Jorge-Rodriguez commented Feb 26, 2021

rsicart commented Sep 21, 2021

Andersson007 commented Sep 21, 2021 • edited Loading

Andersson007 commented Sep 21, 2021

Andersson007 commented Sep 23, 2021

Jorge-Rodriguez commented Sep 23, 2021

rsicart commented Sep 23, 2021 • edited Loading

Andersson007 commented Sep 23, 2021

tchernomax commented Aug 9, 2022 • edited Loading

Jorge-Rodriguez commented Aug 9, 2022

Andersson007 commented Aug 10, 2022

Jorge-Rodriguez commented Aug 10, 2022

Andersson007 commented Aug 10, 2022

ocfmatt commented Aug 15, 2022 • edited Loading

rsicart commented Aug 29, 2022 • edited Loading

betanummeric commented Aug 30, 2022

Andersson007 commented Sep 5, 2022 • edited Loading

rsicart commented Sep 5, 2022

Andersson007 commented Sep 5, 2022

Andersson007 commented Sep 9, 2022 • edited Loading

pgrenaud commented Dec 5, 2022

Andersson007 commented Dec 6, 2022

Andersson007 commented Dec 6, 2022

rsicart commented Dec 6, 2022

Andersson007 commented Dec 7, 2022

steveteahan commented Dec 28, 2020 •

edited

Loading

Andersson007 commented Dec 28, 2020 •

edited

Loading

Andersson007 commented Sep 21, 2021 •

edited

Loading

rsicart commented Sep 23, 2021 •

edited

Loading

tchernomax commented Aug 9, 2022 •

edited

Loading

ocfmatt commented Aug 15, 2022 •

edited

Loading

rsicart commented Aug 29, 2022 •

edited

Loading

Andersson007 commented Sep 5, 2022 •

edited

Loading

Andersson007 commented Sep 9, 2022 •

edited

Loading