SHOW GRANTS (General)
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`
GRANT ALL PRIVILEGESmy 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:
EXECUTE. It would be nice to see the anonymised output from other database servers. So please execute the query
GRANT ALL PRIVILEGESon your server and post the anonymised output here in this thread.
ok, my results.
Command (For me, I was required to include @'127.0.0.1'):
SHOW GRANTS FOR 'myTestUser'@'127.0.0.1';
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.