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;
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 <badouglas@xxxxxxxxx> 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
