| Bug #17247 | No explicit DEFINER allowed in CREATE routine/DEFINER of routine not replicated | ||
|---|---|---|---|
| Submitted: | 8 Feb 2006 22:51 | Modified: | 27 Feb 2006 22:28 |
| Reporter: | Beat Vontobel (Silver Quality Contributor) (OCA) | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.18 | OS: | Linux (Linux 2.4.21 (SuSE Professional)) |
| Assigned to: | CPU Architecture: | Any | |
[16 Feb 2006 13:28]
Valeriy Kravchuk
Thank you for a problem report. Yes, you are right - the exact definer is not replicated, but looks like it is "by design". There is no way to explicitely set definer in CREATE PROCEDURE statement (http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html), and procedure is created by SQL thread on slave that is internal, and not authenticated as any user (on slave). Moreover, there are may be no same user on slave that defined procedure on master. It is a problem. So, this report can be called a feature request, a documentation request (this surely should be explicitely mentioned at http://dev.mysql.com/doc/refman/5.0/en/replication-features.html) or a bug. What is the most appropriate status, from your point of view?
[16 Feb 2006 14:39]
Beat Vontobel
Thanks for your analysis and asking me for my opinion on "feature request", "documentation request" or "bug". Even if it was maybe "by design", I consider this issue as very serious and would suggest to keep level S2. I'll explain why, give a little test case and a suggested solution.
*** EXPLANATION ***
The current situation actually results in an impossibility to use replication and stored procedures together. Just think of what the two features are used for:
Typical use of replication: redundancy (backup), scalability (load balancing for read requests)
Typical use of stored procedures: abstraction, security
To use stored procedures for abstraction and security we need to define them with SQL SECURITY DEFINER (we usually don't want the user who's up to use the procedures to have direct access to the base tables as well). But on the slave these procedures will now be executed in the security context of the invoker, as no definer is available, and thus stop to work.
This makes load balancing with procedurs of SQL SECURITY DEFINER completely impossible (which is bad enough), but much worse in a redundancy/backup scenario: Whenever one of your replication customers will have to switch to a slave due to failure of the master in an emergeny situation, it will certainly be a very bad surprise that suddenly all his applications stop to work...
*** TEST CASE ***
Test case that shows how procedures with SQL SECURITY DEFINER fail in a replication setup:
ON THE MASTER AS root:
mysql1:localhost-test [root]> GRANT EXECUTE ON test.* TO testuser@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)
mysql1:localhost-test [root]> CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.03 sec)
mysql1:localhost-test [root]> INSERT INTO t (i) VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql1:localhost-test [root]> CREATE PROCEDURE tp ()
-> READS SQL DATA
-> DETERMINISTIC
-> SQL SECURITY DEFINER
-> SELECT * FROM t;
Query OK, 0 rows affected (0.02 sec)
ON THE MASTER AS testuser (everything okay):
mysql1:localhost-test [testuser]> CALL tp();
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ON THE SLAVE AS testuser (execution fails):
mysql2:localhost-test [testuser]> CALL tp();
ERROR 1142 (42000): SELECT command denied to user 'testuser'@'localhost' for table 't'
*** SUGGESTED SOLUTION ***
a) CREATE PROCEDURE and CREATE FUNCTION should allow for an explicit "DEFINER = " clause, in exactly the same way as CREATE TRIGGER and CREATE VIEW already do (I don't see any reason why routines should be treated differently). Security considerations for the definer clause (take from the manual on TRIGGERs and VIEWs):
* If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account.
* If you have the SUPER privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated.
b) The binary log should always contain an explicit "DEFINER =" clause, even if no definer was explicitly set in the original statement.
c) If an according user for a definer of a stored routine doesn't exist and the routine is set to SQL SECURITY DEFINER, the execution of the stored routine should be denied with an error message.
[16 Feb 2006 20:45]
Dmitry Lenev
Hi, Beat! Your concerns are valid indeed. Actually this problem was already reported as bug #15963 and we are working on this issue already. So I will mark this bug as duplicate. Thank you for your interest and persistence!!! (BTW our solution is very similar to the one you have described).
[27 Feb 2006 22:28]
Beat Vontobel
Thanks, Dmitri, I was happy to learn that this issue is already dealt with in bug #15963 and is in fact in good progress. At least I could reuse the work I put into this bug report for a blog post at http://www.futhark.ch/mysql/133.html ;-)
Description: The definer of a stored routine is not written to the binary log and thus not replicated to slaves. How to repeat: On the master do: mysql1:localhost-meteonews [root]> CREATE PROCEDURE test() SQL SECURITY DEFINER DETERMINISTIC NO SQL SELECT 'test'; Query OK, 0 rows affected (0.00 sec) mysql1:localhost-meteonews [root]> SELECT * FROM information_schema.routines WHERE ROUTINE_NAME='test'\G *************************** 1. row *************************** SPECIFIC_NAME: test ROUTINE_CATALOG: NULL ROUTINE_SCHEMA: meteonews ROUTINE_NAME: test ROUTINE_TYPE: PROCEDURE DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: SELECT 'test' EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: YES SQL_DATA_ACCESS: NO SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2006-02-08 22:21:24 LAST_ALTERED: 2006-02-08 22:21:24 SQL_MODE: ROUTINE_COMMENT: DEFINER: root@localhost 1 row in set (0.00 sec) It looks like this in the binlog: #060208 22:21:24 server id 101 end_log_pos 775552082 Query thread_id=446199 exec_time=0 error_code=0 SET TIMESTAMP=1139437284; CREATE PROCEDURE test() SQL SECURITY DEFINER DETERMINISTIC NO SQL SELECT 'test'; Check on the slave: mysql3:localhost-(none) [root]> SELECT * FROM information_schema.routines WHERE ROUTINE_NAME = 'test'\G *************************** 1. row *************************** SPECIFIC_NAME: test ROUTINE_CATALOG: NULL ROUTINE_SCHEMA: meteonews ROUTINE_NAME: test ROUTINE_TYPE: PROCEDURE DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: SELECT 'test' EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: YES SQL_DATA_ACCESS: NO SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2006-02-08 22:21:24 LAST_ALTERED: 2006-02-08 22:21:24 SQL_MODE: ROUTINE_COMMENT: DEFINER: @ 1 row in set (0.02 sec) Suggested fix: Add an explicit DEFINER clause to the binary log when a stored procedure is created.