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 programsin particular, stored proceduresare 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 objectsand from other programming languagesis 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 facilitysometimes called definer rights securityallows us to tighten our database security: we can ensure that a user gains access to tables only via stored program code that restricts the types of operations that can be performed on those tables and that can implement various business and data integrity rules. For instance, by establishing a stored program as the only mechanism available for certain table inserts or updates, we can ensure that all of these operations are logged, and we can prevent any invalid data entry from making its way into the table.

We can also create stored programs that execute with the privileges of the calling user, rather than those of the user who created the program. This mode of security is sometimes called invoker rights security, and it offers other advantages beyond those of definer rights, which we will explore in this chapter.

Before delving into the two execution modes available in MySQL, we will first examine the basic permissions needed to create, manage, and execute stored programs . Then we'll go into a detailed discussion of definer rights and invoker rights, and consider how these capabilities might be used in our applications. Finally, we will consider the use of stored programs to increase the general security of our MySQL server and, conversely, identify ways in which the use of stored programs can reduce overall security if developers are not careful.