MySQL Stored Procedure Programming

Advantages of Stored Programs

Let’s look at each of the advantages of stored programs in turn.

They Enhance Database Security

We’ll see in Chapter 18 how the default security mode of stored programs (SQL SECURITY DEFINER) permits a stored program to execute SQL statements even if the calling database account lacks the security privileges to execute these statements as native SQL. By granting a database account access to stored programs only—without granting direct permissions on underlying tables—we can ensure that access to the database occurs only in the manner defined by our stored programs. We can also ensure that these SQL statements are surrounded by whatever business rule validation or logging is required. This concept is explained in more detail in Chapter 18.

In the event that an application account is compromised (for instance, the password is “cracked”), the attacker will still only be able to execute our stored programs, as opposed to being able to run any ad hoc SQL. While such a situation constitutes a severe security breach, at least we are assured that the hacker will be subject to the same checks and logging as a normal application user. The hacker will also be denied the opportunity to retrieve information about the underlying database schema, which will hinder attempts to perform further malicious activities.

The security advantages of stored programs are a powerful motivation to include stored programs in our applications, especially with today’s increasing focus ...