Re: trying to solve a mysql grant issue

Try this:

GRANT SUPER, CREATE USER, SYSTEM_USER, CREATE ROUTINE, ALTER ROUTINE ON *.*
TO 'foo_user'@'localhost';
GRANT SUPER, CREATE USER, SYSTEM_USER, CREATE ROUTINE, ALTER ROUTINE ON *.*
TO 'foo_user'@'%';
FLUSH PRIVILEGES;

On Wed, Jun 26, 2024 at 11:23 AM bruce <[email protected]> wrote:

> Hi.
>
> Testing a php app, and running into an error....
>
> The app throws an error..
>
> SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied;
> you need (at least one of) the SYSTEM_USER privilege(s) for this
> operation ,the sql is: 'DROP FUNCTION IF EXISTS `get_monday`'
>
> As far as I can tell, this relates to mysql - grant/privilege issues..
>
> So.. as root for the test user "foo", created the mysql cmds to
> test/add the privileges to the user.
>
>  GRANT SUPER, CREATE USER, SYSTEM_USER ON *.* TO foo_user;
>  GRANT SUPER, CREATE USER, SYSTEM_USER ON *.* TO `foo_user`@`localhost`;
>  ==seems to work..  the *.* is needed..
>
> To check, tried to see if the data is in the mysql.user tbl..
>
> show grants for 'foo_user'@'localhost';
>
> +---------------------------------------------------------------------------------------------------------------------------------+
> | Grants for foo_user@localhost
>                                                      |
>
> +---------------------------------------------------------------------------------------------------------------------------------+
> | GRANT CREATE, ALTER, SUPER, CREATE ROUTINE, ALTER ROUTINE, CREATE
> USER ON *.* TO `foo_user`@`localhost` WITH GRANT OPTION |
> | GRANT SYSTEM_USER ON *.* TO `foo_user`@`localhost`
>                                                      |
> | GRANT ALL PRIVILEGES ON `zentaopms`.* TO `foo_user`@`localhost` WITH
> GRANT OPTION                                         |
> | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `mysql`.`user` TO
> `foo_user`@`localhost`                                  |
>
> +---------------------------------------------------------------------------------------------------------------------------------+
> 4 rows in set (0.00 sec)
>
>
> So, I suspect I'm doing something wrong..
>
> I posted this to mysql, and decided to post to php as well, just in case.
>
> Any thoughts/things to try would be appreciated. Also, what tbls need
> to be checked to make sure the privileges are modified.
>
> thanks
>