Hello All,

In this thread we will learn about access control security for procedures. Recently I was working on one project in my company where I needed one procedure in the production database along with permissions to execute it with my custom user.

But I was not able to execute it. It was throwing an error access denied for some other user which I was not even using to connect to database and execute procedure.

Then I researched about this a lot. And found out that all stored programs like SQL Procedures, Views, Triggers and events they execute within Definer context. What does this mean ?

It means whoever creates the procedure, execution always happen in that user context. If that user does not have all the privileges for operations which procedure is performing, other users who are trying to invoke the procedure will also not be able to execute the procedure. It does not matter whether that user has full permission or not.

Two solutions are there to solve this

1. Either get the procedure created from root user who has all the privileges.

2. Second solution is to define SQL Security Invoker keyword while procedure creation

Example:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2()
SQL SECURITY INVOKER
BEGIN
  UPDATE t1 SET counter = counter + 1;
END;

For learning about this in more details, I would recommend everyone to check the article here as well. It has everything in detail

Link: https://dev.mysql.com/doc/refman/5.6/en/stored-objects-security.html

Thank you all for reading this.