MySQL Bugs: #48948: URGENT RFE: default-sql-security

Bug #48948	URGENT RFE: default-sql-security
Submitted:	20 Nov 2009 17:26	Modified:	23 Sep 2010 14:13
Reporter:	Kevin Benton	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	All	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	privileges, routines, stored procedures

Description:
MySQL defaults the security type on stored routines to DEFINER.  This is problematic because administrators applying stored procedures to a system automatically promote those stored procedures to the access of the administrator whether or not that is intended.  It also causes problems if the defining user is dropped or somehow changed.  As I see it, this creates a train wreck waiting to happen.  If the default is SQL SECURITY INVOKER, the person creating the stored procedure must take the time to tell the database that he or she wants to give away the definer's privileges specifically.  Some would disagree with me on this so I am asking that an urgent request be made to add default-sql-security as a configuration parameter and global (not session-level) variable.  That way, administrators can determine which is the default and communicate that out to the users.

This is related to bug 40640.

How to repeat:
See description

Suggested fix:
See description

Thank you for the feature request.

Valeriy, I think it's just not right to publicly suggest to manipulate with mysql.proc table, especially when it's possible to do ALTER PROCEDURE ... SQL SECURITY.

Veleriy, please provide a test case.
It's unclear how, after dropping root user, the definer of the stored procedure became set to "@" (as the reporter says in a private comment). The definer of a created object does not change when this user is dropped.

Related to
Bug#20241 Access to procedures/functions is lost if the definer is dropped

Thank you for your interest in MySQL, but we made a decision to not implement this feature request.
Although technically it's possible to do, maintaining the functionality can be an additional hurdle. Besides, there is a partial workaround (always specify SQL SECURITY clause when creating objects), and it's unclear how a new set of options, that are off by default, can help avoid the situation when wrong objects are created by omission.
Long term we plan to introduce roles, and SQL PATH functionality, which should address some of the worries raised in this report.