Bug #2777 Stored procedure doesn't observe definer's rights
Submitted: 13 Feb 2004 10:49 Modified: 2 Mar 2004 2:55
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.0-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: Bugs System CPU Architecture:Any

[13 Feb 2004 10:49] Peter Gulutzan
Description:
In general, MySQL observes the difference between invoker's rights and definer's rights 
(with definer's rights = default). However, if definer's rights are more restrictive than 
invoker's rights, certain operations are not blocked. 
 

How to repeat:
1. Start client with --user=root 
 
mysql> use db5; 
Database changed 
 
mysql> create table t (s1 int); 
Query OK, 0 rows affected (0.28 sec) 
 
mysql> insert into t values (1); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> grant select on db5.* to user1; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> grant select, insert, update, delete on db5.* to user2; 
Query OK, 0 rows affected (0.00 sec) 
 
2. Start client with --user=user1 
 
mysql> use db5; 
Database changed 
 
mysql> create procedure p () insert into t values (1); 
Query OK, 0 rows affected (0.53 sec) 
 
mysql> call p(); 
ERROR 1142 (42000): insert command denied to user: 'user1'@'localhost' for table 't' 
 
So far, okay. Although user1 shouldn't have been allowed to create the procedure, 
that's a separate bug. The main thing is: INSERT was disallowed, because user1 
has only SELECT privileges, and the procedure by default has sql security definer. 
 
3. Start client with --user=user2 
 
mysql> use db5; 
Database changed 
 
mysql> call p(); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select * from t; 
+------+ 
| s1   | 
+------+ 
|    1 | 
+------+ 
1 row in set (0.00 sec) 
 
Certainly user2 has INSERT privilege, but user2 should be acting with the privileges of 
user1, who has no INSERT privilege. So I don't understand why the INSERT succeeded.
[18 Feb 2004 15:32] Dean Ellis
Verified against 5.0.1-alpha-log, although I receive a different error message when user1 attempts to call the procedure:

ERROR 1044 (42000): Access denied for user: 'user1'@'%' to database 'db5'

+----------------------------------------+
| Grants for user1@%                     |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'%'      |
| GRANT SELECT ON `db5`.* TO 'user1'@'%' |
+----------------------------------------+
[2 Mar 2004 2:55] Per-Erik Martin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html