MySQL Bugs: #8884: TRIGGERS: Create trigger succeeds even when user does not have UPDATE privilege

Bug #8884	TRIGGERS: Create trigger succeeds even when user does not have UPDATE privilege
Submitted:	2 Mar 2005 12:29	Modified:	15 Sep 2005 17:52
Reporter:	Disha	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.2	OS:	Windows (Windows Server 2003/Linux)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
Description: 	
When a user tries to create a TRIGGER with contruct 'SET NEW.col_name=' in the Trigger definition SUCCEEDS 	even when the user does not have UPDATE privileges on the referenced column i.e.'col_name'

Test case : 	
3.5.3 - #7 Ensure that use of the construct "SET NEW. <column name> = <value>" fails at CREATE TRIGGER time, if the current user does not have the UPDATE privilege on the column specified.

How to repeat:
Repro Steps : 	

1. Create a database test1;

2. Use test1;	

3. Create table 't1' with fields 'f1' and 'f2' by executing the following SQL statement:

	CREATE TABLE t1 (f1 INT, f2 INT);

4. Insert into t1 values (1,1),(2,2),(3,3);
		
5. Create User 'Test_User1';

6. Grant SELECT on test1.* to 'Test_user1';
		
7. Grant SUPER on *.* to 'Test_user1';

8. Flush Privileges;

9. Reconnect to the MySQL server with the credentials of 'Test_user1'

10. Use Test1;

11. Update t1 Set f2=999 where f1=1;

12.Try to create a trigger with the following definition:

	Create Trigger TRG1 before insert on t1 for each row SET NEW.f1=999;

Expected Results :  The insert statement in Step 11 should fail as the user does not have update permissions and also the TRIGGER creation in Step 12 should subsequently fail.
		

Actual Results :    The insert statement in Step 11 fails as expected but, the TRIGGER creation succeeds without any errors.

Verified using 5.0.3 from bk

Thanks for your bug report

I tested on 5.0.11 and have follow messages:
on Linux SUSE 9.3:
mysql> Use test1;
ERROR 1044 (42000): Access denied for user 'Test_user1'@'%' to database 'test1'

on Windows 2003:
mysql> Update t1 Set f2=999 where f1=1;
ERROR 1142 (42000): UPDATE command denied to user 'Test_user1'@'localhost' for table 't1'

I would like to notice then Linux version permits to change database, it's very strange.

Verified on Windows/Linux. The update command is denied but the
create trigger is allowed:

mysql> Use test2;
Database changed
mysql> Update t1 Set f2=999 where f1=1;
ERROR 1142 (42000): UPDATE command denied to user 'Test_User2'@'192.168.0.119' for table 't1'
mysql> show grants for "Test_User2";
+-----------------------------------------------+
| Grants for Test_User2@%                       |
+-----------------------------------------------+
| GRANT SUPER ON *.* TO 'Test_User2'@'%'        |
| GRANT SELECT ON `test2`.* TO 'Test_User2'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> Create Trigger TRG1 before insert on t1 for each row SET NEW.f1=999;
Query OK, 0 rows affected (0.01 sec)

c:\mysql\bin>mysql -uTest_User2 -hlight test2 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.0.12-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> Update t1 Set f2=999 where f1=1;
ERROR 1142 (42000): UPDATE command denied to user 'Test_User2'@'light' for table 't1'
mysql> Create Trigger TRG1 before insert on t1 for each row SET NEW.f1=999;
Query OK, 0 rows affected (0.05 sec)

mysql> show grants for Test_User2;
+-----------------------------------------------+
| Grants for Test_User2@%                       |
+-----------------------------------------------+
| GRANT SUPER ON *.* TO 'Test_User2'@'%'        |
| GRANT SELECT ON `test2`.* TO 'Test_User2'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29029

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is not a bug. According to SQL2003, part 2 "foundations", section 11.39 <trigger definition>, paragraphs 2 and 3 of "Access Rules", to create a trigger one needs TRIGGER privilege and possibly SELECT privilege. UPDATE privilege is not required.