MySQL 8 vs MySQL 5: Why wildcards misbehave in GRANTs command?

Question

I came across with one of my old projects (using MySQL 5 and PHP 5.5). I've just wanted to test that project using MySQL 8 and PHP7.0. To install the DB i normally do:

...
mysql -u root -p < DBinstall/webAppDBinstall.sql
...

in a "install" script for my web app. The sql commands contain:

...
-- # ######################## SET PHP_SCRIPT & GRANTS FOR ############################
CREATE USER 'php_script'@'localhost' IDENTIFIED BY 'php_script';
GRANT INSERT,SELECT ON webApp.* TO 'php_script'@'localhost';
GRANT UPDATE ON webApp.Orders TO 'php_script'@'localhost';
GRANT UPDATE ON webApp.OrderItems TO 'php_script'@'localhost';
GRANT UPDATE ON webApp.UserStatus TO 'php_script'@'localhost';
GRANT INSERT ON webApp.History TO 'php_script'@'localhost';
...

Then i get the following error from PHP when running the webApp:

$ cat /var/log/apache2/error.log

...
[Thu May 28 06:01:26.317867 2020] [:error] [pid 8906] [client 127.0.0.1:32960] PHP Warning:  mysqli::query(): Couldn't fetch mysqli in /var/www/webApp/src_php/mylib.php on line 68, referer: http://webApp/index.php?error=0 
...

Note: that this project was running flawlessly using MySQL 5 and PHP 5.

Then i thought that "it smells" like being a privilege matter. So i did the following:

mysql> select User,Table_name,Table_priv from mysql.tables_priv where User='php_script';
+------------+------------+------------+
| User       | Table_name | Table_priv |
+------------+------------+------------+
| php_script | History    | Insert     |
| php_script | OrderItems | Update     |
| php_script | Orders     | Update     |
| php_script | UserStatus | Update     |
+------------+------------+------------+
4 rows in set (0.00 sec)

mysql>

Yeap, that's what it is.

Here is the proof:

I changed the my DBinstall/webAppDBinstall.sql script FROM:

...
-- # ######################## SET PHP_SCRIPT & GRANTS FOR ############################
CREATE USER 'php_script'@'localhost' IDENTIFIED BY 'php_script';
GRANT INSERT,SELECT ON webApp.* TO 'php_script'@'localhost';
GRANT UPDATE ON webApp.Orders TO 'php_script'@'localhost';
GRANT UPDATE ON webApp.OrderItems TO 'php_script'@'localhost';
GRANT UPDATE ON webApp.UserStatus TO 'php_script'@'localhost';
GRANT INSERT ON webApp.History TO 'php_script'@'localhost';
...

TO:

-- # ######################## SET PHP_SCRIPT & GRANTS FOR ############################
CREATE USER 'php_script'@'localhost' IDENTIFIED BY 'php_script';
GRANT INSERT,SELECT ON  webApp.Users TO 'php_script'@'localhost';
GRANT INSERT,SELECT ON  webApp.UserStatus TO 'php_script'@'localhost';
GRANT INSERT,SELECT ON  webApp.Categories TO 'php_script'@'localhost';
GRANT INSERT,SELECT ON  webApp.ProductGroups TO 'php_script'@'localhost';
GRANT INSERT,SELECT ON  webApp.CategoriesGroups TO 'php_script'@'localhost';
GRANT INSERT,SELECT ON  webApp.Products TO 'php_script'@'localhost';
GRANT INSERT,SELECT ON  webApp.Orders TO 'php_script'@'localhost';
GRANT INSERT,SELECT ON  webApp.OrderItems TO 'php_script'@'localhost';
GRANT INSERT,SELECT ON  webApp.Messages TO 'php_script'@'localhost';
GRANT INSERT,SELECT ON  webApp.History TO 'php_script'@'localhost';
GRANT UPDATE ON webApp.Orders TO 'php_script'@'localhost';
GRANT UPDATE ON webApp.OrderItems TO 'php_script'@'localhost';
GRANT UPDATE ON webApp.UserStatus TO 'php_script'@'localhost';
GRANT INSERT ON webApp.History TO 'php_script'@'localhost';

And THE RESULT IS:

mysql> select User,Table_name,Table_priv from mysql.tables_priv where User='php_script';
+------------+------------------+----------------------+
| User       | Table_name       | Table_priv           |
+------------+------------------+----------------------+
| php_script | History          | Select,Insert        |
| php_script | Categories       | Select,Insert        |
| php_script | CategoriesGroups | Select,Insert        |
| php_script | Messages         | Select,Insert        |
| php_script | OrderItems       | Select,Insert,Update |
| php_script | Orders           | Select,Insert,Update |
| php_script | ProductGroups    | Select,Insert        |
| php_script | Products         | Select,Insert        |
| php_script | UserStatus       | Select,Insert,Update |
| php_script | Users            | Select,Insert        |
+------------+------------------+----------------------+
10 rows in set (0.00 sec)

mysql>

Any other better thoughts about 'why this happens with wildcards in `GRANT`s' ???

(SORRY for duplicating my question BUT stackoverflow has BANNED me for answering my own questions)

My question is: Why the GRANT <PRIVS> ON <DB>.* TO <USER>; wildcards syntax stopped "working"

Note: This problem is different from mysqli::query(): Couldn't fetch mysqli . My webApp application is a system that runs flawlessly with MySQL 5.x deployments. ITS HAS nothing to do with PHP (although the mysqli error seems to, IT DOES NOT).

Does this answer your question? [mysqli::query(): Couldn't fetch mysqli](https://stackoverflow.com/questions/19937880/mysqliquery-couldnt-fetch-mysqli) — danblack, May 28 '20 at 10:34

Ciprian Stoica · Accepted Answer · 2020-05-29T14:29:55.923

0

I just made a test on a MySQL 8 server where I successfully ran the following:

CREATE DATABASE webApp;
CREATE USER 'php_script'@'localhost' IDENTIFIED BY 'php_script';
GRANT INSERT,SELECT ON webApp.* TO 'php_script'@'localhost';

As I expected, the wildcard grant worked without any issues. So, I'm afraid you will need to search the problem somewhere else.

For example, the target database on one of your example is named webApp an on the next example is webAppDB. So, better check the consistency of the names.

Otherwise, your code syntax looks ok for MySQL 8.

[Edit] - additional suggestions

Make sure skip-name-resolve is set to OFF.

Looks like it is OFF by default on MySQL 8, but it might happen that under some circumstances it is set to ON.

In my case, I run MySQL 8 in a docker container and that variable was set to ON.

If the variable is set to ON, mysql will not resolve localhost to the actual IP of localhost and in your case that might be the problem.

More details on skip-name-resolve here: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_skip_name_resolve

Make sure you finish the GRANTs commands with a FLUSH PRIVILEGES;
Try to switch default-authentication-plugin to mysql_native_password

By default on MySQL 8 default-authentication-plugin is set to caching_sha2_password. But looks like mysqli doesn't have support for this authentication method (according to this article from 2018: https://mysqlserverteam.com/upgrading-to-mysql-8-0-default-authentication-plugin-considerations/)

edited May 29 '20 at 14:29

answered May 28 '20 at 10:03

Ciprian Stoica

2,309
5
22
36

I updated my question because the `webAppDB` vs `webApp` was my edit-on-stackoverflow fault when i posted my question. Thank you a lot for your help. Is there any possibility that the MySQL cli misbehaves due to my bash configuration being set to POSIX (because i prefer this LC on my terminal): `$ cat /etc/bash.bashrc | grep "LC_COLLATE="` ---> `export LC_COLLATE=POSIX` ??? – javase May 28 '20 at 10:26
@javase, I've made a test with LC_COLLATE=POSIX set but looks like it has no negative impact. See my additional suggestions in the updated answer, as I made some additional tests on my machine. – Ciprian Stoica May 29 '20 at 09:21
I think that this is the solution that would work. I'll keep your note for whenever i deploy any MySQL 8 dbms. What i finally did (that day) was to purge the MySQL 8 installation and go back to the 5.7 one. I've also read some incompatibilities with PHP's mysqli + MySQL 8. Thus, i had to move back to 5.7. Thank you A LOT for your help. I'll mark your solution as the one that solves my case. I truly believe that also this 'skip-name-resolve' var was part of the problem along with the PHP + MySQL 8 issue. – javase May 29 '20 at 13:50
For the history what i did was: `sudo apt-get purge mysql-* && sudo mysql_secure_installation && sudo aptitude install mysql-server-5.7 mysql-client-5.7 && mysql -u root -p < DBinstall/webAppDBinstall.sql` and i was back to the old times again :-) – javase May 29 '20 at 13:50
@javase No, no.. don't give up to the upgrade. You might be very close to fix the problem. Check also my 3rd suggestion that I've just added to the answer. I've configured MySQL servers in the past, from version 4 to 8 along with other DB systems (MariaDB, Mongo, PostgreSQL) and though rarely things worked perfectly from the beginning, some solution had been available all the time to make things work. – Ciprian Stoica May 29 '20 at 14:27

MySQL 8 vs MySQL 5: Why wildcards misbehave in GRANTs command?

Yeap, that's what it is.

Any other better thoughts about 'why this happens with wildcards in GRANTs' ???

1 Answers1

Any other better thoughts about 'why this happens with wildcards in `GRANT`s' ???