MySQL Stored Routines (functions and procedures) are not only used for improving performance but also they’re handy when it comes to enhancing security and restricting user access. This post briefs you about security aspects of stored routines by means of example.

By the definition of a Stored Routine,
– DEFINER clause specifies the creator of the stored routine.
– SQL SECURITY clause specifies the execution context of the stored routine.

Now let’s start with an example:

Create a very basic stored routine in test database:

DELIMITER //
CREATE PROCEDURE user_cnt()
BEGIN
SELECT COUNT(*) as total_user FROM mysql.user;
END;//
DELIMITER ;

Our current user is: root@localhost
[ You can see your current user by issuing select CURRENT_USER(); ]

Call the procedure:
CALL user_cnt()\G
total_user: 10

Here, We created a stored routine using root@localhost user.
– DEFINER: The user who creates this procedure. i.e. root@localhost
– SQL SECURITY: Defines under whose privileges the routine will be executed; defaults to DEFINER. i.e. root@localhost

As SQL SECURITY is set to DEFINER, a user even with only EXECUTE permission for routine can call and get the output of the stored routine regardless of whether that user has permission on mysql database or not.

Lets see how this works. Create a “execuser” with limited privileges:
GRANT EXECUTE ON test.* TO 'execuser'@'localhost' IDENTIFIED BY 'execuser' ;
FLUSH PRIVILEGEES;

Login using execuser to mysql prompt and call the procedure created under test database:
mysql> CALL user_cnt()\G
total_user: 10

Now check that below command fired by execuser fails due to lack of privileges:
mysql> select count(*) from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'execuser'@'localhost' for table 'user'

– Thus stored procedure allows us to restrict users to access tables directly but still getting access to certain data.
– User execuser@localhost is not having permission on mysql database and still he could get the data.
– As the SQL SECURITY was set to DEFINER (default), the execution of the routine happened under the security context of root@localhost user and returned the result.

Is this behaviour something that you don’t want to happen? Hmmm we have a way out here.
Lets rewrite the stored procedure as below:

DELIMITER //
DROP PROCEDURE IF EXISTS user_cnt;
CREATE DEFINER='root'@'localhost' PROCEDURE user_cnt()
SQL SECURITY INVOKER
BEGIN
SELECT COUNT(*) as total_user FROM mysql.user;
END;//
DELIMITER ;

– Did you note “SQL SECURITY INVOKER”!! That’s our saviour.
– We’ve specified the DEFINER attribute explicitly.
– SQL SECURITY decides under whose privileges the stored routine gets executed! Here it is INVOKER, the user that CALLs the routine!

Let’s check through execuser@localhost user:
mysql> call user_cnt();
ERROR 1142 (42000): SELECT command denied to user 'execuser'@'localhost' for table 'user'

So, this is now pretty clear that the stored routine tried to execute the SELECT query under INVOKER’s (execuser@localhost) privileges; and the routine failed as the INVOKER has no privileges on user table.

Finally, a few points:
Together DEFINER & SQL SECURITY clauses define the security context to be used during routine execution time.
The DEFINER attribute defaults to the current user & defines the creator of the Stored Routine.
SQL SECURITY defines the execution permission of the stored routine and defaults to DEFINER.

I hope this cleared the basics; thanks for reading.