mysql 8.0: Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN

[janette]

I am updating an existing project from mysql 5.7 to mysql 8.
The default permissions for mysql 8 are more restrictive so I need to grant all permissions to the db user.
I have this in my .ddev/config.yaml

hooks:
  post-start:
    - exec: mysql -uroot -proot -hdb -e "GRANT ALL ON db.* TO 'db'@'%';"

ddev restart runs without error but when I run code that creates a new db table and includes SET SESSION innodb_strict_mode=OFF, I get this error

SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation 

[rfay]

Hi @janette -

I'm no expert on mysql, but it seems you need to add those privileges, which aren't part of "ALL" on "db.*"

Maybe GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'db'@'%'; ?

or GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'db'@'%'; ?

You don't actually have to do these in a post-start hook, they're one-time actions, but it should do no harm.

You can also change your code to use root:root instead of db:db

[janette]

Using GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'db'@'%'; works! Thank you!