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 ...
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.