SQL Procedures Security Access Control

Creative Full Stack Web Developer with 3+ years of experience. Love open source and learning new things every day. Quick learner and believes in hard work
Search for a command to run...

Creative Full Stack Web Developer with 3+ years of experience. Love open source and learning new things every day. Quick learner and believes in hard work
No comments yet. Be the first to comment.
Ever used the search bar in Discord to find an old message? Maybe you searched "meeting link" in your work server. Or tried to find that meme someone shared 2 years ago. Or looked for a conversation with a friend from months back. That simple search ...

IT landscape is evolving faster than ever. Whether you're a seasoned professional or just starting your journey, staying ahead of the curve is crucial. Join me, Yatharth, a senior software engineer at a dynamic remote startup, as we explore the top 5...

Scaling is an important part of system design. Without scaling, no application can survive. In this blog, I will explain scaling with a real-world example, making it easy to understand, so everyone can learn the concept quickly. Let's consider an exa...

Team Members Yatharth Verma Description Introducing FormVibe - Create Beautiful Forms with Ease In the digital era, forms are a vital tool for gathering information and engaging with users. However, designing visually appealing and user-friendly fo...

Hi all, in this blog we are going to learn some differences between npm, yarn and pnpm. I guess everyone is aware of all these package managers. Let's first understand what exactly is package manager just in a brief. Package Manager Package manager l...

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
Either get the procedure created from root user who has all the privileges.
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.