| Bug #17573 | Security Elevation Problem: DROP USER does not drop that users procedures | ||
|---|---|---|---|
| Submitted: | 20 Feb 2006 11:56 | Modified: | 20 Feb 2006 12:50 |
| Reporter: | Kristian Koehntopp | ||
| Status: | Duplicate | ||
| Category: | Server | Severity: | S2 (Serious) |
| Version: | 5.0.18 | OS: | Linux (Linux) |
| Assigned to: | Target Version: | ||
[20 Feb 2006 12:29]
Kristian Koehntopp
The same problem affects triggers.
root@localhost [boom]> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` bigint(20) unsigned NOT NULL,
`cu` varchar(80) NOT NULL,
`su` varchar(80) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [boom]> show triggers\G
*************************** 1. row ***************************
Trigger: insert_t
Event: INSERT
Table: t
Statement: begin set new.cu = current_user(), new.su=session_user(); end
Timing: BEFORE
Created: NULL
sql_mode:
Definer: a@%
1 row in set (0.00 sec)
root@localhost [boom]> insert into t (id) values ( 2);
Query OK, 1 row affected, 2 warnings (0.01 sec)
root@localhost [boom]> select * from t;
+----+-----+----------------+
| id | cu | su |
+----+-----+----------------+
| 1 | a@% | a@localhost |
| 2 | a@% | root@localhost |
+----+-----+----------------+
2 rows in set (0.00 sec)
root@localhost [boom]> drop user a;
Query OK, 0 rows affected (0.00 sec)
root@localhost [boom]> insert into t (id) values ( 3);
Query OK, 1 row affected, 2 warnings (0.00 sec)
root@localhost [boom]> select * from t;
+----+----------------+----------------+
| id | cu | su |
+----+----------------+----------------+
| 1 | a@% | a@localhost |
| 2 | a@% | root@localhost |
| 3 | root@localhost | root@localhost |
+----+----------------+----------------+
3 rows in set (0.00 sec)
Description: DROP USER does not drop that users procedures. The status of these procedures is silently changed to SQL SECURITY INVOKER. A procedure that was previously executed with access permissions "a"@"%" is now executed as "root"@"localhost" as demonstrated. How to repeat: As root: root@localhost [mysql]> select version(); +----------------+ | version() | +----------------+ | 5.0.18-max-log | +----------------+ 1 row in set (0.00 sec) root@localhost [mysql]> drop user "a"$$ Query OK, 0 rows affected (0.00 sec) root@localhost [mysql]> drop user "b"$$ Query OK, 0 rows affected (0.00 sec) root@localhost [mysql]> delimiter ; root@localhost [mysql]> create user "a"; Query OK, 0 rows affected (0.00 sec) root@localhost [mysql]> create user "b"; Query OK, 0 rows affected (0.00 sec) root@localhost [mysql]> grant all on boom.* to "a"; Query OK, 0 rows affected (0.00 sec) root@localhost [mysql]> grant all on boom.* to "b"; Query OK, 0 rows affected (0.00 sec) As a: a@localhost [boom]> delimiter $$ a@localhost [boom]> create procedure blah() sql security definer begin select current_user(), session_user(); end; $$ Query OK, 0 rows affected (0.00 sec) a@localhost [boom]> call blah()$$ +----------------+----------------+ | current_user() | session_user() | +----------------+----------------+ | a@% | a@localhost | +----------------+----------------+ 1 row in set (0.00 sec) As b: b@localhost [boom]> call blah(); +----------------+----------------+ | current_user() | session_user() | +----------------+----------------+ | a@% | b@localhost | +----------------+----------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) As root: root@localhost [(none)]> drop user a; Query OK, 0 rows affected (0.00 sec) root@localhost [(none)]> select user, host, password from mysql.user; +------+-----------+----------+ | user | host | password | +------+-----------+----------+ | root | localhost | | | b | % | | +------+-----------+----------+ 2 rows in set (0.00 sec) root@localhost [(none)]> show procedure status\G *************************** 1. row *************************** Db: boom Name: blah Type: PROCEDURE Definer: a@% Modified: 2006-02-20 11:49:35 Created: 2006-02-20 11:49:35 Security_type: DEFINER Comment: root@localhost [(none)]> call boom.blah(); +----------------+----------------+ | current_user() | session_user() | +----------------+----------------+ | root@localhost | root@localhost | +----------------+----------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec) Suggested fix: a) A DROP USER should drop all procedures associated with that user just as DROP DATABASE drops them. b) Alternatively, the procedure definitions are kept and set to INVALID (this requires the introduction of a status flag for Procedure Objects).