MySQL Stored Procedure Programming

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 default—or if the SQL SECURITY DEFINER clause is specified—stored 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 injection—unless they include dynamic SQL via prepared statements. We recommend that you exercise caution when using ...