MySQL Bugs: #17598: privilege checks when trigger is executed

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

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.

as discussed with Kostja - "To be fixed later"

Later is now?

Test file

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

Result file

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

Sergei, could you please verify that this test is satisfactory?

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.

Lars-Erik, per the above comment from Sergey, please add the test case and close the bug.

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.

Test file

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

Result file

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

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

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.