MySQL Bugs: #57445: Cannot kill threads executing routines defined by others

Bug #57445	Cannot kill threads executing routines defined by others
Submitted:	14 Oct 2010 1:39	Modified:	14 Nov 2010 2:09
Reporter:	Mikiya Okuno	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If the stored routines are created with SQL SECURITY DEFINER and invoked by the user who isn't the definer, it's not possible to KILL the thread by the invoker. "ERROR 1095 (HY000): You are not owner of thread 98" is reported. In addition, a thread executing a such stored program cannot be seen via SHOW PROCESSLIST.

This mostly cause a problem when a timeout expired with C/J: Statement#setQueryTimeout()

How to repeat:
Login with user A

mysql> delimiter //
mysql> create procedure p1 (a int)
    -> begin
    -> select sleep(a);
    -> end;//
Query OK, 0 rows affected (0.17 sec)

Login with user B

mysql> call p1(1000);

Login with user B as another session

mysql> kill 98;
ERROR 1095 (HY000): You are not owner of thread 98

Suggested fix:
Allow the invoker to kill the own threads.

.. and why this behavior is a bug? Seems inline with the purpose of the definer -- privilege checks (while the procedure is executing) are done using the account specified in the definer. This is documented thoroughly.

Hi,

Privilege checks as a definer are fine. But ownership isn't. Even though the routine is executed as another user, but the session is owned by the invoker. At least SHOW PROCESSLIST must show all the session owned by invoker. The invoker should have control for its own sessions.

In other words, a user would want to KILL a query invoked by the user [him|her]self, because he/she invoked it. e.g. JDBC's java.sql.Statement#setQueryTimeout is designed to KILL the session by logging in as the invoker and issuing KILL command against the long running query when a timeout occur; this cannot be done if the session is running the routine with SQL SECURITY DEFINE and defined by an other user.

> But ownership isn't.

The authenticated user does not own the session.

> Even though the routine is executed as another user, but the session is owned by the invoker.

Where is this documented?

> The invoker should have control for its own sessions.

Depends on the context. If it invokes something which has a different effective user, it shouldn't.

Ignore stored procedures for a moment. Think about a regular Unix system. If you invoke a process that changes its user privileges to root, do you expect to be able to kill such a process as a regular user?

> JDBC's java.sql.Statement#setQueryTimeout is designed to KILL the session by logging in as ..

.. and this is fine, I don't see any bugs here. The effective user of a session while a stored procedure is being executed is the definer, if one was set. As for the timeout, the problem is that we still haven't implemented WL#2814. We should not change how privileges work because it happens to break a workaround.

A proper way to support this workaround used by C/J is to perhaps add a KILL privilege or something like.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I wonder what is the recommended way to stop running the long query inside a stored procedure then. The problem is NOT related to timeouts only.

It is quite common to limit applications to only use stored procedures for multiple reasons:
1) this makes possible certain changes to business logic without affecting the executable;
2) this makes it possible to use a limited database account thus preventing serious data corruption and/or performance degradation in case the application has a bug that would otherwise cause it to build a query that affects a lot of rows in a destructive manner or runs too long;
3) this simplifies maintenance tasks in case something goes wrong, i.e. some operations normally performed by the application can be “emulated” by running stored procedures;
4) this just lets the database itself be self-documented;
5) name your reasons here.

Now suppose the stored routine contains a long-running query used to build a report over a large amount of data. Then the application user might decide to interrupt the report building, either because it was started by mistake or because it influences the overall performance too badly, or because for a query that turns out to take so long it’s better to re-run it later, maybe the next day, etc.

How should the “Stop” button be implemented then? Should the application connect the DBMS with a privileged account having KILL privilege? Does MySQL team suggest that the application should actually have the privilege to KILL any session of any user just to be able to kill the one initiated by itself?

Note that this way of interrupting queries is not limited to Java. Libraries for other languages do it this way as well.