Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.8, 10.3.11, 10.2(EOL), 10.3(EOL)
-
None
-
Debian Stretch
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
- causes
-
MXS-2111 After SET PASSWORD done on the database, MaxScale not able to connect
-
- Closed
-
- duplicates
-
MDEV-17136 Set Password command doesn't update Password Column in mysql.user table
-
- Closed
-
- is caused by
-
MDEV-16238 root/localhost authn prioritizes authentication_string over Password
-
- Closed
-
- relates to
-
MDEV-12715 remove mysql.user.password column
-
- Closed
-
Activity
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? |
Status | Open [ 1 ] | Confirmed [ 10101 ] |
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 ] |
Link |
This issue relates to |
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? |
Link |
This issue relates to |
Link |
This issue relates to |
Link |
This issue is caused by |
Link |
This issue duplicates |
Priority | Minor [ 4 ] | Blocker [ 1 ] |
Priority | Blocker [ 1 ] | Critical [ 2 ] |
Priority | Critical [ 2 ] | Blocker [ 1 ] |
Assignee | Vicentiu Ciorbaru [ cvicentiu ] | Sergei Golubchik [ serg ] |
Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
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 ] |
Resolution | Fixed [ 1 ] | |
Status | Closed [ 6 ] | Stalled [ 10000 ] |
Fix Version/s | 10.3.11 [ 23141 ] | |
Fix Version/s | 10.2.19 [ 23207 ] |
Affects Version/s | 10.3.11 [ 23141 ] |
Priority | Blocker [ 1 ] | Major [ 3 ] |
Fix Version/s | 10.2 [ 14601 ] | |
Fix Version/s | 10.3 [ 22126 ] |
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 ] |
Workflow | MariaDB v3 [ 88453 ] | MariaDB v4 [ 154678 ] |
Zendesk Related Tickets | 191437 |
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-12715for 10.4, it might be not worth fixing (if everything works okay with both variations of the user row).