33.1. Using Stored Routines and Triggers for Security Purposes

Stored routines provide security benefits for database administrators. You can define routines that safely access protected data on behalf of ordinary (non-administrative) users, but do not return information that these users should not see. To implement this kind of security precaution, use the combination of the DEFINER security characteristic in the routine definition and the EXECUTE access privilege.

Suppose that you have a table containing sales transactions that should not be visible to ordinary users, but for which summary values of total sales volume need no protection. Create a routine that calculates the summary, using an account that has direct access to the table, and use the DEFINER security characteristic in the routine definition so that it executes with that account's privileges and also has access to the table. Then grant the EXECUTE privilege for the routine to those users who should be able to invoke it. In this way, users gain access to the information provided by the routine, but the protected data used to produce that information remains inaccessible to them.

Stored routines also are useful for data protection in operations that modify data. A stored routine can make changes to tables in a safe way, without giving users direct access to the tables. This prevents them from making possibly unsafe changes themselves. To take this approach, use the following strategy:

• For the tables in question, disallow direct access by ordinary users for INSERT, UPDATE, and DELETE statements. (You can do this by granting the appropriate privileges to administrative users only.)

• Implement a procedural interface for modifying each table. That is, using an administrative account that has access to the table, write stored procedures that have DEFINER security and that perform the required modifications to the table, given appropriate data values as parameters. Grant the EXECUTE privilege for these routines to the appropriate users.

• Require users to perform table modifications by calling the stored routines and passing column values for the rows to be modified as parameters. Each procedure examines its arguments and verifies that they satisfy whatever constraints are deemed necessary. If the arguments are suitable, the procedure performs the requested modification. If they are not, the procedure aborts the operation.

The stored procedures thus act as gateways that check incoming data for legality, perform the requested operation only if it is safe to do so, and reject the attempt otherwise.

This procedural approach also can be used if you want to allow or disallow a modification based on factors other than the legality of column value parameters, such as the identity of the client user or the current date and time. For example, you might have a poll that has an expiration date, after which any attempt to insert new records into a vote table should be rejected. A time-based procedure can implement this constraint.

Triggers provide another way to increase database security for data modification operations, by changing the operation of statements with which triggers are associated (INSERT, UPDATE, and DELETE). For this purpose, AFTER triggers are less useful than BEFORE TRiggers. By the time an AFTER trigger sees an incoming data value, it has already been stored in the database and it is too late to take any corrective action if the value is invalid. A BEFORE TRigger, in contrast, can examine an incoming data value and check it to see whether it is reasonable. If not, the trigger can modify the value to be more suitable before it is stored in the database. For example, a value that is out of bounds can be changed to be within bounds. A value that is not in any of a set of allowable categories can be assigned to a default category. In both cases, the trigger prevents an invalid value from entering the database by mapping it to a valid value.

The preceding trigger strategy is useful if you want only to filter incoming data. If the objective is to cancel an operation entirely when data values are unsuitable, you cannot do this in MySQL using a trigger. However, you can do so using the procedural approach outlined earlier in this section: Disallow direct table access to ordinary users and require them to call stored procedures that perform or reject requested modifications.