Conclusion

In this chapter we looked at the basic security permissions required for creating and executing stored programs and at how the SQL SECURITY clause affects the security context of an executing stored program.

By defaultor if the SQL SECURITY DEFINER clause is specifiedstored programs execute with the permissions of the account that created the stored program. This means that a database user can execute a stored program that can perform database operations not available to that user through normal SQL. You can use this feature to implement a scheme in which a user can manipulate the database through stored programs but has no privilege to manipulate the database through normal SQL. Restricting database access in this way through stored programs can improve database security, since you can ensure that table accesses are restricted to known routines that perform appropriate validation or logging. You can reduce your exposure should the database account involved be compromised.

If the SQL SECURITY INVOKER clause is specified, then the stored program will execute with the permissions of the account that is executing the stored program. In this case, an exception will be raised if the stored program attempts to execute a SQL statement that the invoker does not have permission to execute as native SQL.

Stored programs in MySQL 5.0 are implicitly resistant to SQL injectionunless they include dynamic SQL via prepared statements. We recommend that you exercise caution when using dynamic SQL in stored programstake every precaution to ensure that the stored procedure or function is not vulnerable to malicious SQL injection. If prepared statements and dynamic SQL are necessary, then make sure to validate input parameters, and consider using the SQL SECURITY INVOKER mode to limit your exposure.