MySQL Stored Procedure Programming

Chapter 18. Stored Program Security

Security has always been critical in the world of databases and stored programs that work with those databases. Yet database security has taken on heightened importance in the last decade, with the global reach of the Internet and the increasing tendency for the database to be the target of those trying to compromise application security. In this chapter we explore two different aspects of security as it pertains to MySQL stored programming:

• Controlling access to the execution and modification of stored programs themselves

• Using stored programs to secure the underlying data in MySQL databases

Stored programs—in particular, stored procedures—are subject to most of the security restrictions that apply to other database objects, such as tables, indexes, and views. Specific permissions are required before a user can create a stored program, and, similarly, specific permissions are needed in order to execute a program.

What sets the stored program security model apart from that of other database objects—and from other programming languages—is that stored programs may execute with the permissions of the user who created the stored program, rather than those of the user who is executing the stored program. This model allows users to execute operations via a stored program that they would not be privileged to execute using straight SQL.

This facility—sometimes called definer rights security—allows us to tighten our database security: we can ensure that a user ...