Is it possible in MySQL to set permissions for a user in such a way that he could not perform queries on insert, update, delete, but could call stored procedures that perform such queries. If so, how?

  • one
    When creating a procedure, you can specify that it will be executed with the rights of the creator, not the executor. See SQL SECURITY DEFINER . - Akina
  • And How? proved? - Akina

1 answer 1

Request syntax for revoking user privileges:

 REVOKE privilege ON object FROM username

where privilege is a type of privilege (in your case - INSERT, UPDATE, DELETE).

If you suddenly want to set the privilege back:

 GRANT privilege ON object TO username

You can FLUSH PRIVILEGES privileges using FLUSH PRIVILEGES

When creating a procedure, you must specify the rights with which it will be performed:

  • INVOKER - the script is executed with the rights of the user who called it,
  • DEFINER - the script is executed with the rights of the user specified in CREATE DEFINER.