Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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';
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.