SQL error when modifying user privileges on MySQL 8.0.11+

[objecttothis]

Describe the bug

This is similar to #14217. Modifying user permissions causes a SQL error

To Reproduce

Steps to reproduce the behavior:

1. Go to 'User Accounts'
2. Click on 'Edit Privileges'
3. Modify any privilege (in my then click Go
4. See error (in screenshot)

Expected behavior

I expect that the permissions get changed without error.

Screenshots

Server configuration

• Operating system: FreeBSD 11.3-release-r9
• Web server: Apache 2.4.46
• Database version: MySQL 8.0.20
• PHP version: 7.4.6
• phpMyAdmin version: 5.0.2

Client configuration

• Browser: Firefox Developer Edition
• Operating system: Windows 10 Professional

Additional context

Add any other context about the problem here.

[objecttothis]

Modifying Database-specific privileges does seem to work properly, however.

[objecttothis]

@williamdes I think that PR is Windows specific. My server os is FreeBSD so I can test it but rim not sure it will fix the issue.

[williamdes]

Yeah, sorry for this linking mistake.

This patch will be no good for this issue :)

Can you reproduce this issue on demo servers (root, no password)?

• 5.1.0-dev
• 5.0.x-dev

[objecttothis]

Yep, exact same issue on both demo servers

[williamdes]

I tried MySQL 8.0.2,3 without errors and MySQL 8.0.11 has the error.
And filled up my disk a little more with docker images 😆

[objecttothis]

I'm running 8.0.20, so perhaps its > 8.0.11?

[williamdes]

Any > 8.0.11 seems to be affected

The end of query is blocking the execution

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html

It looks like #14217 was not fixed or maybe it is another bug..

The following features related to account management have been removed:

Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is removed.

Using GRANT to modify account properties other than privilege assignments. This includes authentication, SSL, and resource-limit properties. Instead, establish such properties at account-creation time with CREATE USER or modify them afterward with ALTER USER.

IDENTIFIED BY PASSWORD 'hash_string' syntax for CREATE USER and GRANT. Instead, use IDENTIFIED WITH auth_plugin AS 'hash_string' for CREATE USER and ALTER USER, where the 'hash_string' value is in a format compatible with the named plugin.

Additionally, because IDENTIFIED BY PASSWORD syntax has been removed, the log_builtin_as_identified_by_password system variable is superfluous and has been removed.

The PASSWORD() function. Additionally, PASSWORD() removal means that SET PASSWORD ... = PASSWORD('auth_string') syntax is no longer available.

The old_passwords system variable.

[williamdes]

I tried phpMyAdmin 4.8,4.9,5.0,5.1 and all versions are affected.
The question is how is it possible nobody did see this error since 2018 🤔

[objecttothis]

Perhaps it's an issue that is only prevalent on MySQL 8.0.11 and up, so it wouldn't have been caught until now? nevermind, I see that 8.0.11 was released in 2018. I guess I have a knack for finding broken things?

[williamdes]

Perhaps it's an issue that is only prevalent on MySQL 8.0.11 and up, so it wouldn't have been caught until now?

Yes, I agree
But the release is from 2018-04-19 🤔

Anyway, my fix is quite ready and I will post in in ~30 minutes after writing some unit tests

[objecttothis]

Perhaps it's an issue that is only prevalent on MySQL 8.0.11 and up, so it wouldn't have been caught until now?

Yes, I agree
But the release is from 2018-04-19 🤔

Anyway, my fix is quite ready and I will post in in ~10 minutes after writing some unit tests

Yeah, I caught that after posting my reply.

[williamdes]

Could you try the fix 5164bea ?
Only the change to libraries/classes/Server/Privileges.php is required.

[objecttothis]

Confirmed. This PR fixes the problem. I tested it on my 8.0.20 instance.

[objecttothis]

Side note. This may be related, but it isn't clear. If I modify user privileges on a database level, select the databases, then check all and click go, it runs the query but unchecks "GRANT". It still seems to run the "GRANT ALL PRIVILEGES" query though.

[williamdes]

Side note. This may be related, but it isn't clear. If I modify user privileges on a database level, select the databases, then check all and click go, it runs the query but unchecks "GRANT". It still seems to run the "GRANT ALL PRIVILEGES" query though.

Indeed, if you think this is a bug you can open an issue.
That said, on 4.9 this behavior was already there.

Thank you for testing my patch :)