Bug #17598 privilege checks when trigger is executed
Submitted: 20 Feb 2006 20:26 Modified: 27 Oct 2009 8:54
Reporter: Sergei Golubchik Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[20 Feb 2006 20:26] Sergei Golubchik
Description:
According to the standard a trigger does not need UPDATE privilege to modify NEW pseudo-row.
But in MySQL it currently  does.

(it's the second part of bug#17033)

How to repeat:
 

Suggested fix:
Allow the trigger to modify NEW if either invoker or definer have UPDATE (or INSERT, depending on the operation) privilege. If at least one of them has the privilege, the modification should be allowed.
[20 Feb 2006 20:26] Sergei Golubchik
as discussed with Kostja - "To be fixed later"
[27 Sep 2008 12:22] Konstantin Osipov
Later is now?
[23 Sep 2009 8:57] Lars-Erik Bjørk
Test file

Attachment: bug17598.test (application/octet-stream, text), 745 bytes.

[23 Sep 2009 8:58] Lars-Erik Bjørk
Result file

Attachment: bug17598.result (application/octet-stream, text), 546 bytes.

[23 Sep 2009 9:00] Lars-Erik Bjørk
Sergei, could you please verify that this test is satisfactory?
[5 Oct 2009 18:46] Sergei Golubchik
thinking more about it... there's no need to check that either invoker or definer have the necessary privilege. Because invoker always does - otherwise she wouldn't be able to run the INSERT/UPDATE statement in the first place!

So, we don't need to check privileges on NEW at all.
[8 Oct 2009 9:44] Konstantin Osipov
Lars-Erik, per the above comment from Sergey, please add the test case and close the bug.
[23 Oct 2009 8:50] Lars-Erik Bjørk
New test and result files are given below.

Scenarios 1, 2 and 4 in the test still fails, so I will leave this bug open as for now.
[23 Oct 2009 8:51] Lars-Erik Bjørk
Test file

Attachment: bug17598.test (application/octet-stream, text), 3.18 KiB.

[23 Oct 2009 8:51] Lars-Erik Bjørk
Result file

Attachment: bug17598.result (application/octet-stream, text), 2.61 KiB.

[23 Oct 2009 8:53] Lars-Erik Bjørk
Scenario 1:
Definer has INSERT privileges, invoker has INSERT privileges.
This fails when doing an insert query:
query 'INSERT INTO bug17598.t1 VALUES (1), (2), (3)' failed: 1143: UPDATE command denied to user 'u1'@'localhost' for column 'c1' in table 't1'

Scenario 2:
Definer does not have INSERT privileges, invoker has INSERT privileges.
This fails when doing an insert query:
query 'INSERT INTO bug17598.t1 VALUES (1), (2), (3)' failed: 1143: UPDATE command denied to user 'u1'@'localhost' for column 'c1' in table 't1'

Scenario 3:
Definer has UPDATE privileges, invoker has UPDATE privileges:
This succeeds when doing an update query

Scenario 4:
Definer does not have UPDATE privileges, invoker has UPDATE privileges:
This fails when doing an update query:
query 'UPDATE bug17598.t1 SET c1 = 17' failed: 1143: UPDATE command denied to user 'u1'@'localhost' for column 'c1' in table 't1'

According to Sergei, all of these scenarios should succeed
[27 Oct 2009 8:53] Bugs System
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/commits/88258

3675 lars-erik.bjork@sun.com	2009-10-27
      Test for bug#17598 - privilege checks when trigger is executed.
      Based on the description given by Sergei.
      
      The test has four scenarios.
      
      Scenario 1:
      Definer has INSERT privileges, invoker has INSERT privileges.
      This fails when doing an insert query:
      query 'INSERT INTO bug17598.t1 VALUES (1), (2), (3)' failed: 1143: UPDATE command denied
      to user 'u1'@'localhost' for column 'c1' in table 't1'
      
      Scenario 2:
      Definer does not have INSERT privileges, invoker has INSERT privileges.
      This fails when doing an insert query:
      query 'INSERT INTO bug17598.t1 VALUES (1), (2), (3)' failed: 1143: UPDATE command denied
      to user 'u1'@'localhost' for column 'c1' in table 't1'
      
      Scenario 3:
      Definer has UPDATE privileges, invoker has UPDATE privileges:
      This succeeds when doing an update query
      
      Scenario 4:
      Definer does not have UPDATE privileges, invoker has UPDATE privileges:
      This fails when doing an update query:
      query 'UPDATE bug17598.t1 SET c1 = 17' failed: 1143: UPDATE command denied to user
      'u1'@'localhost' for column 'c1' in table 't1'
      
      According to Sergei, all of these scenarios should succeed
     @ mysql-test/r/trigger.result
        The expected result.
     @ mysql-test/t/trigger.test
        The test.