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.
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
MySQL Stored Programming Tutorial
Blocks, Conditional Statements, and Iterative Programming
Using SQL in Stored Programming
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
MySQL Built-in Functions
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
Using MySQL Stored Programs with PHP
Using MySQL Stored Programs with Java
Using MySQL Stored Programs with Perl
Using MySQL Stored Programs with Python
Using MySQL Stored Programs with .NET
Part IV: Optimizing Stored Programs
Stored Program Security
Tuning Stored Programs and Their SQL
Basic SQL Tuning
Advanced SQL Tuning
Optimizing Stored Program Code
Best Practices in MySQL Stored Program Development