Is it possible to encapsulate a table in MySQL?

All we need is an easy explanation of the problem, so here it is.

I am new to Databases. I want the following:

  • UPDATEs in a database to happen only through a stored procedure
  • The user does not have GRANT UPDATE
  • The user has GRANT EXECUTE ON PROCEDURE

I tried it this way but figured out that the user needed to have a UPDATE permission.

Is there any other way in which this can be achieved?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

In MySQL Stored Procedures, you have the concept of SQL SECURITY.

It can be either DEFINER or INVOKER

  • When you call a Stored Procedure that has DEFINER for SQL SECURITY, the caller is allowed to have the same grants as the DEFINER for the duration of the call. The GRANT EXECUTE for the specified Stored Procedure is necessary.
  • When you call a Stored Procedure that has INVOKER for SQL SECURITY, the caller is expected to have the needed grants. If any of the needed grants are missing, the call will fail at the earliest point where the needed grant was missing.

For more information, please read the MySQL Documentation on Access Control for Stored Programs and Views

To see the SQL SECURITY for the procedure or function named mydb.myproc, run this:

SELECT security_type FROM information_schema.routines
WHERE routine_schema='mydb' AND routine_name='myproc';

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply