Let’s look at each of the advantages of stored programs in turn.
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 ...