MySQL 5 Stored Procedures: Relic or Revolution?
Stored procedures (or stored routines, to use the official MySQL terminology) are programs that are both stored and executed within the database server. Stored procedures have been features in closed-source relational databases, such as Oracle, since the early 1990s. However, MySQL added stored procedure support only in the recent 5.0 release and, consequently, applications built on the LAMP stack don't generally incorporate stored procedures. So, this is an opportune time to consider whether stored procedures should be incorporated into your MySQL applications.
Database stored programs first came to prominence in the late 1980s and early 1990s, during the client-server revolution. In the client-server applications of that time, stored programs had some obvious advantages:
Client-server applications typically had to balance processing load carefully between the client PC and the (relatively) more powerful server machine. Using stored programs was one way to reduce the load on the client, which might otherwise be overloaded.
Network bandwidth was often a serious constraint on client-server applications; execution of multiple server-side operations in a single stored program could reduce network traffic.
Maintaining correct versions of client software in a client-server environment was often problematic. Centralizing at least some of the processing on the server allowed a greater measure of control over core logic.
Stored programs offered clear security advantages because, in those days, application users typically connected directly to the database, rather than through a middle tier. As I discuss later in this article, stored procedures allow you to restrict the database account only to well-defined procedure calls, rather than allowing the account to execute any and all SQL statements.
With the emergence of three-tier architectures and Web applications, some of the incentives to use stored programs from within applications disappeared. Application clients are now often browser-based, security is predominantly handled by a middle tier, and the middle tier possesses the ability to encapsulate business logic. Most of the functions for which stored programs were used in client-server applications now can be implemented in middle-tier code (PHP, Java, C# and so on).
Nevertheless, many of the traditional advantages of stored procedures remain, so let's consider these advantages, and some disadvantages, in more depth.
Stored procedures are subject to most of the security restrictions that apply to other database objects: tables, indexes, views and so forth. 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 procedure, rather than those of the user who is executing the stored procedure. This model allows users to perform actions via a stored procedure that they would not be authorized to perform using normal SQL.
This facility, sometimes called definer rights security, allows us to tighten our database security, because 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.
In the event that this application account is compromised (for instance, if the password is cracked), attackers still will be able to execute only our stored programs, as opposed to being able to run any ad hoc SQL. Although such a situation constitutes a severe security breach, at least we are assured that attackers will be subject to the same checks and logging as normal application users. They also will be denied the opportunity to retrieve information about the underlying database schema (because the ability to run standard SQL will be granted to the procedure, not the user), which will hinder attempts to perform further malicious activities.
Another security advantage inherent in stored programs is their resistance to SQL injection attacks. An SQL injection attack can occur when a malicious user manages to “inject” SQL code into the SQL code being constructed by the application. Stored programs do not offer the only protection against SQL injection attacks, but applications that rely exclusively on stored programs to interact with the database are largely resistant to this type of attack (provided that those stored programs do not themselves build dynamic SQL strings without fully validating their inputs).
Practical Task Scheduling Deployment
July 20, 2016 12:00 pm CDT
One of the best things about the UNIX environment (aside from being stable and efficient) is the vast array of software tools available to help you do your job. Traditionally, a UNIX tool does only one thing, but does that one thing very well. For example, grep is very easy to use and can search vast amounts of data quickly. The find tool can find a particular file or files based on all kinds of criteria. It's pretty easy to string these tools together to build even more powerful tools, such as a tool that finds all of the .log files in the /home directory and searches each one for a particular entry. This erector-set mentality allows UNIX system administrators to seem to always have the right tool for the job.
Cron traditionally has been considered another such a tool for job scheduling, but is it enough? This webinar considers that very question. The first part builds on a previous Geek Guide, Beyond Cron, and briefly describes how to know when it might be time to consider upgrading your job scheduling infrastructure. The second part presents an actual planning and implementation framework.
Join Linux Journal's Mike Diehl and Pat Cameron of Help Systems.
Free to Linux Journal readers.Register Now!
- SUSE LLC's SUSE Manager
- My +1 Sword of Productivity
- Tech Tip: Really Simple HTTP Server with Python
- Managing Linux Using Puppet
- Murat Yener and Onur Dundar's Expert Android Studio (Wrox)
- Non-Linux FOSS: Caffeine!
- Returning Values from Bash Functions
- Rogue Wave Software's Zend Server
- Doing for User Space What We Did for Kernel Space
- Parsing an RSS News Feed with a Bash Script