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 ]
            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?
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            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 ]
            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 ]
            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 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.