Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16774

SET PASSWORD and ALTER USER with slightly different results

Details

    Description

      Just create two users:

      MariaDB [(none)]> create user foo identified by 'bar';
       
      MariaDB [(none)]> create user foo2 identified with mysql_native_password as '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';
       
      MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
      +------+------+-------------------------------------------+--------+-----------------------+
      | user | host | password                                  | plugin | authentication_string |
      +------+------+-------------------------------------------+--------+-----------------------+
      | foo  | %    | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB |        |                       |
      | foo2 | %    | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB |        |                       |
      +------+------+-------------------------------------------+--------+-----------------------+
      

      Edit password with ALTER USER:

      MariaDB [(none)]> alter user foo IDENTIFIED with mysql_native_password as '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';
      

      Edit password with SET PASSWORD:

      MariaDB [(none)]> SET PASSWORD FOR 'foo2'@'%' = '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';
      

      Result:

      MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
      +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
      | user | host | password                                  | plugin                | authentication_string                     |
      +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
      | foo  | %    | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |                       |                                           |
      | foo2 | %    |                                           | mysql_native_password | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |
      +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
      

      Is this intended behavior?

      Attachments

        Issue Links

          Activity

            dakr Daniel Krämer created issue -
            dakr Daniel Krämer made changes -
            Field Original Value New Value
            Description Just create two users:
            {code:sql}
            MariaDB [(none)]> create user foo identified by 'bar';

            MariaDB [(none)]> create user foo2 identified with mysql_native_password as '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

            MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
            +------+------+-------------------------------------------+--------+-----------------------+
            | user | host | password | plugin | authentication_string |
            +------+------+-------------------------------------------+--------+-----------------------+
            | foo | % | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB | | |
            | foo2 | % | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB | | |
            +------+------+-------------------------------------------+--------+-----------------------+
            {code}
            Edit password with SET PASSWORD:
            {code:sql}
            MariaDB [(none)]> SET PASSWORD FOR 'foo2'@'%' = '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';
            {code}

            Edit password with ALTER USER:
            {code:sql}
            MariaDB [(none)]> alter user foo IDENTIFIED with mysql_native_password as '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';
            {code}

            Result:
            {code:sql}
            MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            | user | host | password | plugin | authentication_string |
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            | foo | % | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA | | |
            | foo2 | % | | mysql_native_password | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            {code}
            Just create two users:
            {code:sql}
            MariaDB [(none)]> create user foo identified by 'bar';

            MariaDB [(none)]> create user foo2 identified with mysql_native_password as '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

            MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
            +------+------+-------------------------------------------+--------+-----------------------+
            | user | host | password | plugin | authentication_string |
            +------+------+-------------------------------------------+--------+-----------------------+
            | foo | % | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB | | |
            | foo2 | % | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB | | |
            +------+------+-------------------------------------------+--------+-----------------------+
            {code}
            Edit password with SET PASSWORD:
            {code:sql}
            MariaDB [(none)]> SET PASSWORD FOR 'foo2'@'%' = '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';
            {code}

            Edit password with ALTER USER:
            {code:sql}
            MariaDB [(none)]> alter user foo IDENTIFIED with mysql_native_password as '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';
            {code}

            Result:
            {code:sql}
            MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            | user | host | password | plugin | authentication_string |
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            | foo | % | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA | | |
            | foo2 | % | | mysql_native_password | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            {code}

            Is this intended behavior?
            elenst Elena Stepanova made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]

            Thanks for the report.
            I think it's even more questionable why CREATE USER .. IDENTIFIED WITH and ALTER USER .. IDENTIFIED WITH have different results.
            Reproducible on 10.2 as well.

            I'm setting it to confirmed, although given the task MDEV-12715 for 10.4, it might be not worth fixing (if everything works okay with both variations of the user row).

            elenst Elena Stepanova added a comment - Thanks for the report. I think it's even more questionable why CREATE USER .. IDENTIFIED WITH and ALTER USER .. IDENTIFIED WITH have different results. Reproducible on 10.2 as well. I'm setting it to confirmed, although given the task MDEV-12715 for 10.4, it might be not worth fixing (if everything works okay with both variations of the user row).
            elenst Elena Stepanova made changes -
            Component/s Authentication and Privilege System [ 13101 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            Assignee Vicentiu Ciorbaru [ cvicentiu ]
            elenst Elena Stepanova made changes -
            dakr Daniel Krämer made changes -
            Description Just create two users:
            {code:sql}
            MariaDB [(none)]> create user foo identified by 'bar';

            MariaDB [(none)]> create user foo2 identified with mysql_native_password as '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

            MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
            +------+------+-------------------------------------------+--------+-----------------------+
            | user | host | password | plugin | authentication_string |
            +------+------+-------------------------------------------+--------+-----------------------+
            | foo | % | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB | | |
            | foo2 | % | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB | | |
            +------+------+-------------------------------------------+--------+-----------------------+
            {code}
            Edit password with SET PASSWORD:
            {code:sql}
            MariaDB [(none)]> SET PASSWORD FOR 'foo2'@'%' = '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';
            {code}

            Edit password with ALTER USER:
            {code:sql}
            MariaDB [(none)]> alter user foo IDENTIFIED with mysql_native_password as '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';
            {code}

            Result:
            {code:sql}
            MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            | user | host | password | plugin | authentication_string |
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            | foo | % | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA | | |
            | foo2 | % | | mysql_native_password | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            {code}

            Is this intended behavior?
            Just create two users:
            {code:sql}
            MariaDB [(none)]> create user foo identified by 'bar';

            MariaDB [(none)]> create user foo2 identified with mysql_native_password as '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

            MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
            +------+------+-------------------------------------------+--------+-----------------------+
            | user | host | password | plugin | authentication_string |
            +------+------+-------------------------------------------+--------+-----------------------+
            | foo | % | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB | | |
            | foo2 | % | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB | | |
            +------+------+-------------------------------------------+--------+-----------------------+
            {code}

            Edit password with ALTER USER:
            {code:sql}
            MariaDB [(none)]> alter user foo IDENTIFIED with mysql_native_password as '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';
            {code}

            Edit password with SET PASSWORD:
            {code:sql}
            MariaDB [(none)]> SET PASSWORD FOR 'foo2'@'%' = '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';
            {code}

            Result:
            {code:sql}
            MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            | user | host | password | plugin | authentication_string |
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            | foo | % | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA | | |
            | foo2 | % | | mysql_native_password | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            {code}

            Is this intended behavior?

            Right, this is an oversight. Privileges still work properly, it's a "cosmetic" thing.

            When checking if a user has a password or not we will use password column with mysql_native_password (or the old auth if password is in short format) if plugin and authentication_string are not set. SET PASSWORD will play with plugin and authentication string values and clear the password column while alter user will use the password column if it can.

            Inconsistent, but it won't affect server behaviour with regards to passwords and authentication. If you don't look at mysql.user table, you won't notice a change

            serg what do you think? Should we update the code in 10.2 or just scrap the password column altogether in 10.4 and leave this behaviour as is?

            cvicentiu Vicențiu Ciorbaru added a comment - Right, this is an oversight. Privileges still work properly, it's a "cosmetic" thing. When checking if a user has a password or not we will use password column with mysql_native_password (or the old auth if password is in short format) if plugin and authentication_string are not set. SET PASSWORD will play with plugin and authentication string values and clear the password column while alter user will use the password column if it can. Inconsistent, but it won't affect server behaviour with regards to passwords and authentication. If you don't look at mysql.user table, you won't notice a change serg what do you think? Should we update the code in 10.2 or just scrap the password column altogether in 10.4 and leave this behaviour as is?

            Just for your information:
            I stumbled upon this while using https://forge.puppet.com/puppetlabs/mysql and opened a issue there too: https://tickets.puppetlabs.com/browse/MODULES-7487

            dakr Daniel Krämer added a comment - Just for your information: I stumbled upon this while using https://forge.puppet.com/puppetlabs/mysql and opened a issue there too: https://tickets.puppetlabs.com/browse/MODULES-7487
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -

            This is, probably, caused by my fix for MDEV-16238.

            In that fix I made SET PASSWORD to use the authentication_string and clear the password column. It was to avoid the cases when both are set making the behavior ambiguous.

            Apparently, CREATE USER does the opposite, prefers password column. I don't think it matters much, as long as authentication_string and password are never set both at the same time.

            serg Sergei Golubchik added a comment - This is, probably, caused by my fix for MDEV-16238 . In that fix I made SET PASSWORD to use the authentication_string and clear the password column. It was to avoid the cases when both are set making the behavior ambiguous. Apparently, CREATE USER does the opposite, prefers password column. I don't think it matters much, as long as authentication_string and password are never set both at the same time.
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            Priority Minor [ 4 ] Blocker [ 1 ]
            elenst Elena Stepanova made changes -
            Priority Blocker [ 1 ] Critical [ 2 ]
            markus makela markus makela made changes -
            elenst Elena Stepanova made changes -
            Priority Critical [ 2 ] Blocker [ 1 ]
            elenst Elena Stepanova made changes -
            Assignee Vicentiu Ciorbaru [ cvicentiu ] Sergei Golubchik [ serg ]
            markus makela markus makela added a comment -

            This affects MaxScale and also needs to be fixed there (MXS-2111).

            markus makela markus makela added a comment - This affects MaxScale and also needs to be fixed there ( MXS-2111 ).
            serg Sergei Golubchik made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3.11 [ 23141 ]
            Fix Version/s 10.2.19 [ 23207 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]

            According to the info in this issue, this is fixed in 10.3.11. However I'm testing it and i still have doubts about the behavior.

            When creating a user with:

            create user foo2 identified with mysql_native_password as '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

            Shouldn't this populate authentication_string with the password and mysql_native_password in plugin. what I actually find is:

            MariaDB [(none)]> create user foo identified by 'bar';
            Query OK, 0 rows affected (0.000 sec)
             
            MariaDB [(none)]> create user foo2 identified with mysql_native_password as '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';
            Query OK, 0 rows affected (0.001 sec)
             
            MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
            +------+------+-------------------------------------------+--------+-----------------------+
            | user | host | password                                  | plugin | authentication_string |
            +------+------+-------------------------------------------+--------+-----------------------+
            | foo  | %    | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB |        |                       |
            | foo2 | %    | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB |        |                       |
            +------+------+-------------------------------------------+--------+-----------------------+
            
            

            If i change the password using alter user:

             
            MariaDB [(none)]> alter user foo2 IDENTIFIED with mysql_native_password as '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';
            Query OK, 0 rows affected (0.000 sec)
             
            MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
            +------+------+-------------------------------------------+--------+-----------------------+
            | user | host | password                                  | plugin | authentication_string |
            +------+------+-------------------------------------------+--------+-----------------------+
            | foo  | %    | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |        |                       |
            | foo2 | %    | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |        |                       |
            +------+------+-------------------------------------------+--------+-----------------------+
            2 rows in set (0.000 sec)
            
            

            Only using set password updates authentication_string:

            MariaDB [(none)]> SET PASSWORD FOR 'foo2'@'%' = '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';
            Query OK, 0 rows affected (0.001 sec)
             
            MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            | user | host | password                                  | plugin                | authentication_string                     |
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            | foo  | %    | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |                       |                                           |
            | foo2 | %    | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA | mysql_native_password | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |
            +------+------+-------------------------------------------+-----------------------+-------------------------------------------+
            2 rows in set (0.000 sec)
            
            

            My understanding is that this behavior is not the expected one. I'm using 10.3.12:

            1. mysqld --version
              mysqld Ver 10.3.12-MariaDB-1:10.3.12+maria~bionic for debian-linux-gnu on x86_64 (mariadb.org binary distribution)
            amoralej Alfredo Moralejo added a comment - According to the info in this issue, this is fixed in 10.3.11. However I'm testing it and i still have doubts about the behavior. When creating a user with: create user foo2 identified with mysql_native_password as '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB'; Shouldn't this populate authentication_string with the password and mysql_native_password in plugin. what I actually find is: MariaDB [(none)]> create user foo identified by 'bar'; Query OK, 0 rows affected (0.000 sec)   MariaDB [(none)]> create user foo2 identified with mysql_native_password as '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB'; Query OK, 0 rows affected (0.001 sec)   MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%'; +------+------+-------------------------------------------+--------+-----------------------+ | user | host | password | plugin | authentication_string | +------+------+-------------------------------------------+--------+-----------------------+ | foo | % | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB | | | | foo2 | % | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB | | | +------+------+-------------------------------------------+--------+-----------------------+ If i change the password using alter user:   MariaDB [(none)]> alter user foo2 IDENTIFIED with mysql_native_password as '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA'; Query OK, 0 rows affected (0.000 sec)   MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%'; +------+------+-------------------------------------------+--------+-----------------------+ | user | host | password | plugin | authentication_string | +------+------+-------------------------------------------+--------+-----------------------+ | foo | % | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA | | | | foo2 | % | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA | | | +------+------+-------------------------------------------+--------+-----------------------+ 2 rows in set (0.000 sec) Only using set password updates authentication_string: MariaDB [(none)]> SET PASSWORD FOR 'foo2' @ '%' = '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA' ; Query OK, 0 rows affected ( 0.001 sec)   MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%' ; +------+------+-------------------------------------------+-----------------------+-------------------------------------------+ | user | host | password | plugin | authentication_string | +------+------+-------------------------------------------+-----------------------+-------------------------------------------+ | foo | % | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA | | | | foo2 | % | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA | mysql_native_password | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA | +------+------+-------------------------------------------+-----------------------+-------------------------------------------+ 2 rows in set ( 0.000 sec) My understanding is that this behavior is not the expected one. I'm using 10.3.12: mysqld --version mysqld Ver 10.3.12-MariaDB-1:10.3.12+maria~bionic for debian-linux-gnu on x86_64 (mariadb.org binary distribution)

            I agree with @Alfredo Moralejo , the issue is still present on 10.3.12 from debian buster. please consider making create user, alter user and set password strongly consistent, otherwise it would break many configuration management engines.

            bodik@cesnet.cz Radoslav Bodó added a comment - I agree with @Alfredo Moralejo , the issue is still present on 10.3.12 from debian buster. please consider making create user, alter user and set password strongly consistent, otherwise it would break many configuration management engines.
            danblack Daniel Black added a comment -

            Not consistent as above test.

            danblack Daniel Black added a comment - Not consistent as above test.
            danblack Daniel Black made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            danblack Daniel Black made changes -
            Fix Version/s 10.3.11 [ 23141 ]
            Fix Version/s 10.2.19 [ 23207 ]
            danblack Daniel Black made changes -
            Affects Version/s 10.3.11 [ 23141 ]
            serg Sergei Golubchik made changes -
            Priority Blocker [ 1 ] Major [ 3 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            serg Sergei Golubchik added a comment - - edited

            The fix in the commit dd6e74c62a2 made sure that the password field is always set, because that was the original issue that broke third-party tools.

            Other differences between password and authentication_string were removed in 10.4.

            serg Sergei Golubchik added a comment - - edited The fix in the commit dd6e74c62a2 made sure that the password field is always set, because that was the original issue that broke third-party tools. Other differences between password and authentication_string were removed in 10.4.
            serg Sergei Golubchik made changes -
            Fix Version/s 10.2.19 [ 23207 ]
            Fix Version/s 10.3.11 [ 23141 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 88453 ] MariaDB v4 [ 154678 ]
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 191437

            People

              serg Sergei Golubchik
              dakr Daniel Krämer
              Votes:
              2 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.