Is it possible to encapsulate a table in MySQL?

Question

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?

Answer 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';