Re: trying to solve a mysql grant issue

--00000000000051f5da061bcca635
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFAM 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`;
>  =3D=3Dseems 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
>

--00000000000051f5da061bcca635
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Try this:</div><div><br></div><div>GRANT SUPER, CREAT=
E USER, SYSTEM_USER, CREATE ROUTINE, ALTER ROUTINE ON *.* TO &#39;foo_user&=
#39;@&#39;localhost&#39;;<br>GRANT SUPER, CREATE USER, SYSTEM_USER, CREATE =
ROUTINE, ALTER ROUTINE ON *.* TO &#39;foo_user&#39;@&#39;%&#39;;<br>FLUSH P=
RIVILEGES;<br></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" c=
lass=3D"gmail_attr">On Wed, Jun 26, 2024 at 11:23=E2=80=AFAM bruce &lt;<a h=
ref=3D"mailto:[email protected]">[email protected]</a>&gt; wrote:<br></=
div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex">Hi.<br>
<br>
Testing a php app, and running into an error....<br>
<br>
The app throws an error..<br>
<br>
SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied;<br>
you need (at least one of) the SYSTEM_USER privilege(s) for this<br>
operation ,the sql is: &#39;DROP FUNCTION IF EXISTS `get_monday`&#39;<br>
<br>
As far as I can tell, this relates to mysql - grant/privilege issues..<br>
<br>
So.. as root for the test user &quot;foo&quot;, created the mysql cmds to<b=
r>
test/add the privileges to the user.<br>
<br>
=C2=A0GRANT SUPER, CREATE USER, SYSTEM_USER ON *.* TO foo_user;<br>
=C2=A0GRANT SUPER, CREATE USER, SYSTEM_USER ON *.* TO `foo_user`@`localhost=
`;<br>
=C2=A0=3D=3Dseems to work..=C2=A0 the *.* is needed..<br>
<br>
To check, tried to see if the data is in the mysql.user tbl..<br>
<br>
show grants for &#39;foo_user&#39;@&#39;localhost&#39;;<br>
+--------------------------------------------------------------------------=
-------------------------------------------------------+<br>
| Grants for foo_user@localhost<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|<br>
+--------------------------------------------------------------------------=
-------------------------------------------------------+<br>
| GRANT CREATE, ALTER, SUPER, CREATE ROUTINE, ALTER ROUTINE, CREATE<br>
USER ON *.* TO `foo_user`@`localhost` WITH GRANT OPTION |<br>
| GRANT SYSTEM_USER ON *.* TO `foo_user`@`localhost`<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|<br>
| GRANT ALL PRIVILEGES ON `zentaopms`.* TO `foo_user`@`localhost` WITH<br>
GRANT OPTION=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0|<br>
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `mysql`.`user` TO<br>
`foo_user`@`localhost`=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |<br>
+--------------------------------------------------------------------------=
-------------------------------------------------------+<br>
4 rows in set (0.00 sec)<br>
<br>
<br>
So, I suspect I&#39;m doing something wrong..<br>
<br>
I posted this to mysql, and decided to post to php as well, just in case.<b=
r>
<br>
Any thoughts/things to try would be appreciated. Also, what tbls need<br>
to be checked to make sure the privileges are modified.<br>
<br>
thanks<br>
</blockquote></div>

--00000000000051f5da061bcca635--