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.

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions

Triggers

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



MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net