Execution Mode Options for Stored Programs

Stored program code differs from any other kind of code that might execute against the database in that it can have database privileges that are different from those of the account that executes the stored program. Normally, when we execute some SQLwhether it is inside the MySQL client, a PHP program, or whateverthe activities that the SQL will perform (read table X, update table Y, etc.) will be checked against the privileges that are associated with the database account to which we are connected. If our account lacks privilege to perform the activity, the SQL statement will fail with the appropriate error.

Stored programs can be defined to act in the same way, if the SQL SECURITY INVOKER clause is included in the CREATE PROCEDURE or CREATE FUNCTION statement used to create the program. However, if SQL SECURITY DEFINER (the default) is specified instead, then the stored program executes with the privilege of the account that created the stored program, rather than the account that is executing the stored program. Known as definer rights, this execution mode can be a very powerful way of restricting ad hoc table modifications and avoiding security breaches. Definer rights can also be a problem, however, if you are relying on traditional security privileges to secure your database.

Let's go through a quick example before we dig in more deeply. A user creates a procedure to execute a simple transaction, as shown in Example 18-1.

Example 18-1. Simple transaction using definer rights security

CREATE PROCEDURE tfer_funds
 (from_account INT, to_account INT,tfer_amount NUMERIC(10,2))
 SQL SECURITY DEFINER
BEGIN
 START TRANSACTION;

 UPDATE account_balance
 SET balance=balance-tfer_amount
 WHERE account_id=from_account;

 UPDATE account_balance
 SET balance=balance+tfer_amount
 WHERE account_id=to_account;

 INSERT into transaction_log
 (user_id, description)
 values(user( ), concat('Transfer of ',tfer_amount,' from ',
 from_account,' to ',to_account));

 COMMIT;
END;

We grant the EXECUTE privilege on this procedure to Fred, who has no other privileges to the account_balance table:

 GRANT EXECUTE ON PROCEDURE prod.tfer_funds TO 'FRED'@'%';

Now, Fred would like to make some illicit changes to the account_balance table, but he is unable to do so directly:

 C:in32>mysql -uFRED -pFRED -Dprod
 Welcome to the MySQL monitor. Commands end with ; or g.
 Your MySQL connection id is 7 to server version: 5.0.18-nightly-20051211-log

 Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

 mysql> SELECT * FROM account_balance;
 ERROR 1142 (42000): SELECT command denied to user 'FRED'@'localhost' for table
'account_balance'
 mysql> INSERT INTO account_balance (account_id,balance) values(324,4000);
 ERROR 1142 (42000): INSERT command denied to user 'FRED'@'localhost' for table
'account_balance'
 mysql> ARGH!
 -> ;
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
 that corresponds to your MySQL server version for the right syntax to use
 near 'ARGH'!' at line 1

Fred can use the stored procedure to adjust balances (as shown in Figure 18-1), but by doing so he is required to take the money "from" somewhere and to create an incriminating row in the transaction_log table:

 mysql> CALL tfer_funds(324,916,200);
 Query OK, 0 rows affected (0.44 sec)

 mysql> SELECT * FROM transaction_log WHERE user_id LIKE 'FRED%';
 +---------------------+----------------+---------------------------------+
 | txn_timestamp | user_id | description |
 +---------------------+----------------+---------------------------------+
 | 2005-04-14 11:23:45 | FRED@localhost | Transfer of 200 from 324 to 916 |
 +---------------------+----------------+---------------------------------+
 2 rows in set (0.00 sec)

 mysql> ARGH!
 -> ;
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ARGH!' at line 1

Figure 18-1. A definer rights stored program can execute SQL that the user does not have direct permission to execute

In short, using "definer rights" lets us grant permission to use the database only in ways that we clearly define through stored programs. If you like, you can think of such stored programs as an API to the database that we provide to users.

The down side of using stored programs in this way is that it makes it much harder to be certain how you have restricted access to certain objects. For instance, we can issue the following statement to try and make sure that Fred cannot look at account balances:

 REVOKE SELECT ON prod.account_balance FROM 'FRED'@'%';

However, we would need to review all of the stored programs that Fred has access to before we could be 100% sure that he cannot perform any such activity.

If we want stored programs to succeed only if the user has sufficient privileges to execute the SQL statements that they contain, then we need to create an invoker rights program instead. Example 18-2 shows the tfer_funds stored procedure created with the SQL SECURITY INVOKER option specified.

Example 18-2. Invoker rights stored procedure

CREATE PROCEDURE tfer_funds
 (from_account INT, to_account INT,tfer_amount NUMERIC(10,2))
 SQL SECURITY INVOKER
BEGIN
 START TRANSACTION;

 UPDATE account_balance
 SET balance=balance-tfer_amount
 WHERE account_id=from_account;

 UPDATE account_balance
 SET balance=balance+tfer_amount
 WHERE account_id=to_account;

 INSERT into transaction_log
 (user_id, description)
 values(user( ), concat('Transfer of ',tfer_amount,' from ',
 from_account,' to ',to_account));

 COMMIT;
END;

Now if we want Fred to be able to execute this stored program, we will have to explicitly grant him access to the tables involved. Otherwise, he gets a security error when he executes the procedure:

 mysql> CALL tfer_funds(324,916,200);
 ERROR 1142 (42000): UPDATE command denied to user 'FRED'@'localhost' for table
'account_balance'

Figure 18-2 illustrates these operations.

As well as arguably clarifying the relationship between users and table privileges, the use of the SQL SECURITY INVOKER option allows us to prevent certain security holes that can arise when stored programs execute dynamic SQL. A stored program that can execute dynamic SQL (see Chapter 5) and that runs with definer rights can represent a significant security risk; see the section "SQL Injection in Stored Programs" later in this chapter.

18.2.1. The SQL SECURITY Clause

The SQL SECURITY clause of the CREATE PROCEDURE and CREATE FUNCTION statements determines whether the program will operate with the privileges of the invoker or those of the definer. The syntax is straightforward:

Figure 18-2. An invoker rights procedure can only issue SQL that the user has permission to execute

 CREATE {PROCEDURE|FUNCTION} program_name (parameter_definitions)
 [ SQL SECURITY {INVOKER|DEFINER} ]
 stored_program_statements

If no SQL SECURITY clause appears, then the program is created with the SQL SECURITY DEFINER option.

The SQL SECURITY clause can be changed without having to re-create the stored procedure or function using the ALTER PROCEDURE or ALTER FUNCTION statement as follows:

 ALTER {PROCEDURE|FUNCTION} program_name
 SQL SECURITY {INVOKER|DEFINER};

The SQL SECURITY clause applies only to procedures or functions; a related clause DEFINERcan be applied to triggers if you want to change the execution privileges under which a trigger runs. See Chapter 11 for more details about this clause.

18.2.2. Using Definer Rights to Implement Security Policies

As we have already discussed, stored programs defined with the SQL SECURITY DEFINER clause can execute SQL statements that would normally not be available to the account executing the stored program. We can use this facility to provide extensive control over the way in which the user interacts with the database.

If we write our application without stored programs, then our front-end code (written in, say, PHP) interacts directly with the underlying MySQL tables. As a consequence, each MySQL account that will be used to run the application must be granted all of the permissions required by the application code.

Directly granting privileges to accounts, however, can lead to significant security problems. Users can take advantage of any client tool, including the MySQL command line, to connect to this account, thereby circumventing any security controls that might have been placed within the application logic.

Let's take a look at a scenario that demonstrates the security issues with a MySQL application that does not use stored programs. If an application performs operations on tables within the prod schema, we might create an account for that application and grant it rights to perform queries and DML on all of the tables in that schema:

 GRANT SELECT, UPDATE, DELETE, INSERT ON prod.* TO myapp@'%'

The myapp account is now a highly privileged accounta hacker who got hold of the account password could delete any or all rows in any of the application tables, select any data (salaries, credit cards, etc.), and perform any number of malicious or dishonest activities.

On the other hand, in a scenario in which we use stored programs to control access to the database, we only need to grant EXECUTE permission on the programs that make up the application:

 GRANT EXECUTE ON prod.* TO myapp@'%'

A user connecting to the myapp account can still get her work done, by calling the appropriate elements in the applicationbut that is precisely all that the user can do. If the capability is not implemented within the application, then it is not available to the user. This significantly reduces the exposure of the database to malicious users if the connection information for the myapp account is compromised.

For instance, our application might contain internal logic that prevents a user from accessing the salary information of employees unless the user is a senior-level manager or a member of the Payroll department. However, this application-level restriction can easily be circumvented if the user logs into the database using the MySQL Query Browser and issues SQL against the database.

By using a "definer rights" stored program, we can ensure that the user gains access to database tables only via code that we provide within the stored program. In that way, we can ensure that the security and integrity of our database is maintained, even if a user logs onto the database directly.

Example 18-3 shows a stored procedure that returns employee details. The stored procedure was created with the SQL SECURITY DEFINER clause, so anyone with the EXECUTE privilege on this procedure will be able to view the employee details, even if he or she doesn't have the SELECT privilege on this table.

The stored procedure checks the ID of the user who executes the procedure and compares this ID with information in the employees table. If the user executing the stored procedure is a senior-level manager or a member of the Payroll department, then the employee details are returned without modification. Otherwise, the employee details are returned with the salary details obscured.

Example 18-3. Procedure that restricts access to employee salary data

1 CREATE PROCEDURE sp_employee_list(in_department_id DECIMAL(8,0))
2 SQL SECURITY DEFINER READS SQL DATA
3 BEGIN
4 DECLARE l_user_name VARCHAR(30);
5 DECLARE l_not_found INT DEFAULT 0;
6 DECLARE l_department_name VARCHAR(30);
7 DECLARE l_manager_id INT;
8
9 DECLARE user_csr CURSOR FOR
10 SELECT d.department_name,e.manager_id
11 FROM departments d JOIN employees e USING(department_id)
12 WHERE db_user=l_user_name;
13
14 DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_not_found=1;
15
16 /* Strip out the host from the user name */
17 SELECT SUBSTR(USER(),1,INSTR(USER( ),'@')-1)
18 INTO l_user_name;
19
20 OPEN user_csr;
21 FETCH user_csr INTO l_department_name,l_manager_id;
22 CLOSE user_csr;
23
24 IF l_department_name='PAYROLL' OR l_manager_id IN (0,1) THEN
25 SELECT surname,firstname,salary
26 FROM employees
27 WHERE department_id=in_department_id
28 ORDER BY employee_id;
29 ELSE
30 /* Not authorized to see salary */
31 SELECT surname,firstname,'XXXXXXX' AS salary
32 FROM employees
33 WHERE department_id=in_department_id
34 ORDER BY employee_id;
35 END IF;
36
37 END;

Let's look at the key parts of this code:

Line(s)	Explanation
17	Retrieve the name of the account currently executing the stored procedure.
2022	Retrieve the employee record with the matching ID.
2428	If the corresponding user is in the Payroll department or is a first- or second-level manager, then we return the employee salary unmasked.
3134	Otherwise, return the data with the salary details masked.

Fred is a software developer with our company who should not be able to see employee salary details. When he executes the stored procedure, the salary details are masked out, as shown in Example 18-4.

Example 18-4. Using a stored procedure to restrict access to sensitive information

C:>mysql -ufred -pfred -Dprod
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 21 to server version: 5.0.18-nightly-20051211-log
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> CALL sp_employee_list(3);
+-------------+-----------+---------+
| surname | firstname | salary |
+-------------+-----------+---------+
| RAYMOND | GOLDIE | XXXXXXX |
| RACE | ARLENA | XXXXXXX |
| HAGAN | LYNNA | XXXXXXX |
| MARSTEN | ALOYS | XXXXXXX |
| FILBERT | LEON | XXXXXXX |
| RAM | SANCHO | XXXXXXX |
| SAVAGE | SORAH | XXXXXXX |
| FLOOD | ULRIC | XXXXXXX |
| INGOLD | GUTHREY | XXXXXXX |
| WARNER | WORTH | XXXXXXX |
| LEOPARD | AUSTIN | XXXXXXX |
| ROBBINETTE | BRIAN | XXXXXXX |
| REUTER | LORIS | XXXXXXX |
| MITCHELL | HUGO | XXXXXXX |

Fred is unable to select from the employees table directly, so there is no way for him to retrieve the employee salary data, as shown in Example 18-5.

Example 18-5. Direct access to the underlying tables is denied

mysql> SELECT * FROM employees;
ERROR 1142 (42000): SELECT command denied to user 'fred'@'localhost' for table
'employees'

Jane is a member of the Payroll department, so when she executes the procedure, she can see the salary details, as shown in Example 18-6.

Example 18-6. The stored procedure allows authorized users to view salary details

C:>mysql -uJane -pJane -Dprod
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 21 to server version: 5.0.18-nightly-20051211-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> CALL sp_employee_list(3);
+-------------+-----------+--------+
| surname | firstname | salary |
+-------------+-----------+--------+
| RAYMOND | GOLDIE | 53465 |
| RACE | ARLENA | 45733 |
| HAGAN | LYNNA | 85259 |
| MARSTEN | ALOYS | 49200 |
| FILBERT | LEON | 97467 |
| RAM | SANCHO | 58866 |
| SAVAGE | SORAH | 83897 |
| FLOOD | ULRIC | 84275 |
| INGOLD | GUTHREY | 60306 |
| WARNER | WORTH | 47473 |

Note that, like Fred, Jane may not directly access the employees table. Instead, she must call the sp_employee_list procedure when she wants to see the salaries for a department. If we move her to another department, she will automatically lose the ability to view these salary details.

We can also use definer rights programs to ensure that transactions applied to the database always conform to various business rules and regulatory compliance measures that we might have in place. Using a stored program to control all inserts into the sales table, for example, could be used to automate the maintenance of audit and summary tables. We saw an example of logging DML within a stored procedure in Example 18-2.

18.2.3. Stored Program or View?

It is sometimes possible to use a view rather than a stored program to implement some aspects of database security. For example, a user can select from a view even if he does not have access to the underlying tables, so with a view you can control which columns and rows a user can see.

Using CASE statements and WHERE clause conditions, it is often possible to create views that restrict access to only appropriate rows orusing updatable viewsthose that restrict modifications. For instance, the two views in Example 18-7 were designed to perform some of the security limitations provided by the stored procedure from Example 18-3.

Definer Rights in N tier Applications

In the days when client/server applications ruled the earth, end users were often given individual database accounts, and they authenticated themselves to the application by connecting to the database. In modern web-based or N-tier applications, users typically authenticate with a middle-tier application or web server, and all users share a pool of common "proxy" database connections.

The definer rights stored program security model was first defined during the client/server era, and it largely reflects this idea that the end user might actually know her database username and password. Nevertheless, the definer rights model still has a valid role in a web-based environment, since it helps limit the exposure if the proxy account is compromised.

In a modern application that uses proxy accounts, access to the password for the proxy account will be carefully restricted. The proxy account should generally be used only by the application server. If a malicious user obtains the password to the proxy account, however, he could then have unrestricted access to the underlying database tables.

By using stored programs to mediate between the application server and the database, we can carefully limit the activities that the proxy account can undertake. We can also implement auditing, alarming, and logging to help us identify any malicious use of this account.

Of course, you should very carefully secure an application's proxy database account under any scenario. But if you are careful to limit that proxy account to execution of application stored programs, you will also limit the damage a malicious user can inflict in a compromised scenario.

 

Example 18-7. Using a view to implement security policies

CREATE VIEW current_user_details_view AS
 SELECT departments.department_name,employees.manager_id
 FROM employees join departments using (department_id)
 WHERE db_user=convert(SUBSTR(USER(),1,INSTR(USER( ),'@')-1) using latin1) ;

CREATE VIEW employees_view AS
 SELECT firstname,surname,salary,db_user,
 CASE WHEN u.department_name='PAYROLL' OR u.manager_id IN (0,1) THEN
 salary
 ELSE '0000000000'
 END CASE AS salary
 FROM employees e, current_user_details_view u ;

Using a view to implement these kinds of access restrictions is attractive, since the view implementation would allow the user more flexible query capabilities (aggregate functions, WHERE clause restrictions, etc.). On the other hand, as the security restrictions become more complex, it becomes increasingly difficultand ultimately impossibleto create views to implement those restrictions. Finally, most organizations must ensure the integrity of transactions, and this cannot be encoded in view definitions.

18.2.4. Handling Invoker Rights Errors

When you create a stored program with invoker rights, you can be sure that the stored program will succeed only if the user executing the stored program has the necessary privileges. This means that you don't have to be particularly careful about who gets EXECUTE privileges to the programthe program will never let them do something that they didn't already have the privilege to do in native SQL. What this means, however, is that the program is now more likely to raise an exception at run-time, since we can't know in advance that the user has the required privileges.

The possibility of runtime security exceptions in invoker rights programs means that you will generally want to add handler logic to these programs. Consider the stored procedure shown in Example 18-8.

Example 18-8. Stored procedure using invoker rights

CREATE PROCEDURE sp_cust_list (in_sales_rep_id INT)
 SQL SECURITY INVOKER
BEGIN
 SELECT customer_id, customer_name
 FROM customers
 WHERE sales_rep_id=in_sales_rep_id;
END;

This stored procedure includes the SQL SECURITY INVOKER clause, so any user who invokes the stored procedure must have the SELECT privilege on the customers table. When Fred, who does not have this privilege, runs sp_cust_list, he will see the error message shown in Example 18-9.

Example 18-9. Invoker privileges can lead to unhandled security-violation errors

mysql> CALL sp_cust_list(14);
ERROR 1142 (42000): SELECT command denied to
 user 'fred'@'localhost' for table 'customers'

Under some circumstances, throwing an unhandled exception in this way might be sufficient. For many applications, however, it will be necessary to trap the error and provide better information and guidance to the user. Consider the revised implementation of the sp_cust_list procedure, shown in Example 18-10.

Example 18-10. Handling security violations with invoker rights procedures

CREATE PROCEDURE sp_cust_list2 (in_sales_rep_id INT)
 SQL SECURITY INVOKER
BEGIN
 DECLARE denied INT DEFAULT 0;

 DECLARE command_denied CONDITION FOR 1142;
 DECLARE CONTINUE HANDLER FOR command_denied SET denied=1;

 SELECT customer_id, customer_name
 FROM customers
 WHERE sales_rep_id=14;

 IF denied =1 THEN
 SELECT 'You may not view customer data.'
 AS 'Permission Denied';
 END IF;
END;

Now when Fred runs this program, he is denied the ability to see the customer information, but at least gets a clearer explanation of the problem, as shown in Example 18-11.

Example 18-11. Handling security violations in a stored procedure

mysql> CALL sp_cust_list2(14);
+--------------------------------------------------------------------+
| Permission Denied |
+--------------------------------------------------------------------+
| You may not view customer data. |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)