Search code examples
mysqlsqlsql-grantmysql-8.0

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

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 GRANTs' ???

(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).

Solution

  • 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

    1. 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

    1. Make sure you finish the GRANTs commands with a FLUSH PRIVILEGES;

    2. 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/)