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.<\/p>\n
By the definition of a Stored Routine,
\n– DEFINER clause specifies the creator of the stored routine.
\n– SQL SECURITY clause specifies the execution context of the stored routine.<\/p>\n
Now let’s start with an example:<\/p>\n
Create a very basic stored routine in test database:<\/p>\n
Our current user is: root@localhost Call the procedure: Here, We created a stored routine using root@localhost user. 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. <\/p>\n Lets see how this works. Create a “execuser” with limited privileges: Login using execuser to mysql prompt and call the procedure created under test database: Now check that below command fired by execuser fails due to lack of privileges: – Thus stored procedure allows us to restrict users to access tables directly but still getting access to certain data. Is this behaviour something that you don’t want to happen? Hmmm we have a way out here. – Did you note “SQL SECURITY INVOKER”!! That’s our saviour. Let’s check through execuser@localhost 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.<\/p>\n Finally, a few points: I hope this cleared the basics; thanks for reading.<\/p>\n","protected":false},"excerpt":{"rendered":"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…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[299,300,301,427,303,298,302],"class_list":{"0":"post-1703","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"tag-acess-control","9":"tag-definer","10":"tag-invoker","11":"tag-mysql","12":"tag-mysql-stored-procedure","13":"tag-mysql-stored-routines","14":"tag-sql-security"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1703","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=1703"}],"version-history":[{"count":11,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1703\/revisions"}],"predecessor-version":[{"id":1712,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1703\/revisions\/1712"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=1703"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=1703"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=1703"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}DELIMITER \/\/
\nCREATE PROCEDURE user_cnt()
\nBEGIN
\n SELECT COUNT(*) as total_user FROM mysql.user;
\nEND;\/\/
\nDELIMITER ;<\/code><\/p>\n
\n[ You can see your current user by issuing select CURRENT_USER(); <\/code> ]<\/p>\n
\nCALL user_cnt()\\G
\ntotal_user: 10<\/code><\/p>\n
\n– DEFINER: The user who creates this procedure. i.e. root@localhost
\n– SQL SECURITY: Defines under whose privileges the routine will be executed; defaults to DEFINER. i.e. root@localhost<\/p>\n
\nGRANT EXECUTE ON test.* TO 'execuser'@'localhost' IDENTIFIED BY 'execuser' ;
\nFLUSH PRIVILEGEES;<\/code><\/p>\n
\nmysql> CALL user_cnt()\\G
\ntotal_user: 10<\/code><\/p>\n
\nmysql> select count(*) from mysql.user;
\nERROR 1142 (42000): SELECT command denied to user 'execuser'@'localhost' for table 'user'<\/code><\/p>\n
\n– User execuser@localhost is not having permission on mysql database and still he could get the data.
\n– 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. <\/p>\n
\nLets rewrite the stored procedure as below:<\/p>\nDELIMITER \/\/
\nDROP PROCEDURE IF EXISTS user_cnt;
\nCREATE DEFINER='root'@'localhost' PROCEDURE user_cnt()
\nSQL SECURITY INVOKER
\nBEGIN
\n SELECT COUNT(*) as total_user FROM mysql.user;
\nEND;\/\/
\nDELIMITER ;<\/code><\/p>\n
\n– We’ve specified the DEFINER attribute explicitly.
\n– SQL SECURITY decides under whose privileges the stored routine gets executed! Here it is INVOKER, the user that CALLs the routine!<\/p>\n
\nmysql> call user_cnt();
\nERROR 1142 (42000): SELECT command denied to user 'execuser'@'localhost' for table 'user'<\/code><\/p>\n
\nTogether DEFINER & SQL SECURITY clauses define the security context to be used during routine execution time.
\nThe DEFINER attribute defaults to the current user & defines the creator of the Stored Routine.
\nSQL SECURITY defines the execution permission of the stored routine and defaults to DEFINER.<\/p>\n