Hello

Because of the reported performance issues I have been tossing around ideas to solve the problem. One was to rely a few functions, that are follow ups of database operations, on database triggers, another one was to outsource tasks more or less completely to the database, that needs nowadays complex preparations in our PHP-code.

I begun with the tests independent from the corresponding issue #673. I thereby draw on my experience with MS SQL and its implementation of stored procedures (from now on SP). There I impemented a program with only SP, that decomposes the nested levels of a JSON object into its parts and key-value-pairs, analises them and selects the data depending of the curent dataset, to decide what of the dataset should be stored in another database.

With running SHOW GRANTS FOR 'database-user-name'; when logged in in phpMyAdmin I got the following result

Grants for database-user-name@%
GRANT USAGE ON *.* TO `database-user-name`@`database-host`
GRANT ALL PRIVILEGES ON `database-name`.* TO `database-user-name`@`database-host`

With GRANT ALL PRIVILEGES my user is allowed to do more or less anything. This output could look completely different on other servers. The following permissions are explicitly required to handle SP: CREATE ROUTINE, ALTER ROUTINE and EXECUTE. It would be nice to see the anonymised output from other database servers. So please execute the query GRANT ALL PRIVILEGES on your server and post the anonymised output here in this thread.

Tschö, Auge

ok, my results.

Command (For me, I was required to include @'127.0.0.1'):
SHOW GRANTS FOR 'myTestUser'@'127.0.0.1';

Returns:
Grants for myTestUser@127.0.0.1
GRANT USAGE ON *.* TO `myTestUser `@`127.0.0.1`
GRANT ALL PRIVILEGES ON `myTestDB `.* TO `myTestUser `@`127.0.0.1`

This is a good start. I am also able to create the SPs you mention above. I am running on a shared account using a shared MySQL (MariaDB) server from Hostinger. Other hosts may provide different access, so as you mention we will likely need to get more data from other users here just to confirm it's standard practice.