[MDEV-9134] ALL PRIVILEGES insufficient to kill query Created: 2015-11-16  Updated: 2015-11-19  Resolved: 2015-11-19

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.0.21-galera
Fix Version/s: 10.0.21-galera

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: need_feedback
Environment:

ubuntu 14.04


 Description    

MariaDB [(none)]> show grants;
 
+-----------------------------------------------------------------------------------------------------+
 
| Grants for openquery@localhost                                                                      |
 
+-----------------------------------------------------------------------------------------------------+
 
| GRANT ALL PRIVILEGES ON *.* TO 'openquery'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
 
+-----------------------------------------------------------------------------------------------------+
 
 
 
 
 
MariaDB [(none)]> pager grep RENAME
 
PAGER set to 'grep RENAME'
 
MariaDB [(none)]> show processlist;
 
| 1132689 | web         | 10.0.1.226:49476 | web_core       | Query   |    5497 | exit mysqld_lock_tables()    | RENAME TABLE rec_db.abilities TO rec_db1447648955.abilities                  |    0.000 |
 
| 1135244 | web         | 10.0.1.226:50465 | web_core       | Query   |    4783 | Waiting for table level lock | RENAME TABLE rec_db.abilityactions TO rec_db1447649669.abilityactions        |    0.000 |
 
| 1137379 | web         | 10.0.1.226:52356 | web_core       | Query   |    3830 | Waiting for table level lock | RENAME TABLE rec_db.abilityreminders TO rec_db1447650622.abilityreminders    |    0.000 |
 
100 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> kill 1132689;
 
ERROR 1095 (HY000): You are not owner of thread 1132689
 
MariaDB [(none)]> kill 1135244;
 
ERROR 1095 (HY000): You are not owner of thread 1135244
 
MariaDB [(none)]> kill connection 1135244;
 
ERROR 1095 (HY000): You are not owner of thread 1135244



 Comments   
Comment by Elena Stepanova [ 2015-11-18 ]

Works for me:

MariaDB [(none)]> show grants;
 
+--------------------------------------------------------------------------------------------------+
 
| Grants for elenst@localhost                                                                      |
 
+--------------------------------------------------------------------------------------------------+
 
| GRANT ALL PRIVILEGES ON *.* TO 'elenst'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
 
+--------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [(none)]> show processlist;
 
+----+--------+-----------------+------+---------+------+-------+------------------+----------+
 
| Id | User   | Host            | db   | Command | Time | State | Info             | Progress |
 
+----+--------+-----------------+------+---------+------+-------+------------------+----------+
 
| 16 | elenst | localhost       | NULL | Query   |    0 | init  | show processlist |    0.000 |
 
| 18 | foo    | localhost:44378 | test | Sleep   |    3 |       | NULL             |    0.000 |
 
+----+--------+-----------------+------+---------+------+-------+------------------+----------+
 
2 rows in set (0.01 sec)
 
 
 
MariaDB [(none)]> kill 18;
 
Query OK, 0 rows affected (0.00 sec)

The usual reason is that at the moment of connection the user only had the PROCESS privileges, and you granted ALL after the connection was already established – in this case, indeed, the privileges would be visible but not active.

If you've ruled it out, then possibly something is wrong with the WSREP-ed version, AFAIR it had some logic around account management.

Comment by Daniel Black [ 2015-11-18 ]

the user was created 1-2 weeks ago with all privs and it was a new connection when the kill as attempted. The queries being killed where the ones stuck due to MDEV-7370.

Maybe while manipulating the engine independent starts the query elevated privs somewhere.

Certainly not reproducible on benign SLEEP queries.

MariaDB [(none)]> pager grep sleep; show processlist;
 
PAGER set to 'grep sleep'
 
| 3367283 | root        | localhost | NULL | Query   |       5 | User sleep         | select sleep(400)                                                                                    |    0.000 |
 
67 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> pager; kill 3367283;
 
Default pager wasn't set, using stdout.
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> pager grep sleep; show processlist;
 
PAGER set to 'grep sleep'
 
| 3368223 | root        | localhost | NULL | Query   |       7 | User sleep         | select sleep(400)                                                                                    |    0.000 |
 
67 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> kill connection 3368223;
 
Query OK, 0 rows affected (0.00 sec)

Comment by Elena Stepanova [ 2015-11-19 ]

Still works:

MariaDB [test]> show processlist;
 
+----+--------+-----------------+------+---------+------+---------------------------+---------------------------+----------+
 
| Id | User   | Host            | db   | Command | Time | State                     | Info                      | Progress |
 
+----+--------+-----------------+------+---------+------+---------------------------+---------------------------+----------+
 
|  3 | elenst | localhost       | test | Query   |    0 | init                      | show processlist          |    0.000 |
 
|  4 | root   | localhost:50721 | db1  | Query   |    4 | exit mysqld_lock_tables() | rename table t1 to db2.t1 |    0.000 |
 
+----+--------+-----------------+------+---------+------+---------------------------+---------------------------+----------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> kill 4;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> show grants;
 
+--------------------------------------------------------------------------------------------------+
 
| Grants for elenst@localhost                                                                      |
 
+--------------------------------------------------------------------------------------------------+
 
| GRANT ALL PRIVILEGES ON *.* TO 'elenst'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
 
+--------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select @@version;
 
+-----------------------+
 
| @@version             |
 
+-----------------------+
 
| 10.0.21-MariaDB-wsrep |
 
+-----------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select user, host, plugin from mysql.user where user = 'elenst';
 
+--------+-----------+-------------+
 
| user   | host      | plugin      |
 
+--------+-----------+-------------+
 
| elenst | localhost | unix_socket |
 
+--------+-----------+-------------+
 
1 row in set (0.00 sec)

Please paste the cnf file.

Comment by Daniel Black [ 2015-11-19 ]

Sorry, don't know where to go to from here. I guess its can't reproduce then.

Generated at Thu Feb 08 07:32:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.