Bug #27878 Use of view overrides column update privileges on underlying table
Submitted: 17 Apr 2007 8:45 Modified: 17 May 2007 14:20
Reporter: Phil Anderton Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.38, 5.1, falcon tree OS:Linux
Assigned to: Evgeny Potemkin CPU Architecture:Any

[17 Apr 2007 8:45] Phil Anderton
Description:
A user only has privileges to update a given column of a table t. By using a view, he is able to update any column of t, although the view is defined with SQL SECURITY INVOKER.

How to repeat:
As root:

GRANT UPDATE (col1) ON t TO 'readonlyuser'@'localhost';
CREATE SQL SECURITY INVOKER VIEW v AS SELECT * FROM t;
FLUSH PRIVILEGES;

As 'readonlyuser':

UPDATE t SET col2='xxx' WHERE (some condition)
ERROR 1143 (42000): UPDATE command denied to user 'readonlyuser'@'localhost' for column 'col2' in table 't'

UPDATE v SET col2='xxx' WHERE (some condition)
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0
[17 Apr 2007 10:53] Valeriy Kravchuk
Thank you for a problem report. Please, connect as readonlyuser and send the results of:

SHOW GRANTS;

And, as root:

SELECT * from mysql.user where user='readonlyuser'\G
[17 Apr 2007 22:34] Sveta Smirnova
test case

Attachment: bug27878.test (application/octet-stream, text), 600 bytes.

[17 Apr 2007 22:36] Sveta Smirnova
Thank you for the report.

Verified on Linux using attached test case. All versions are affected.
[17 Apr 2007 22:43] Sveta Smirnova
better test case

Attachment: bug27878_2.test (application/octet-stream, text), 634 bytes.

[11 May 2007 17:55] 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/26525

ChangeSet@1.2479, 2007-05-11 21:49:07+04:00, evgen@moonbone.local +4 -0
  Bug#27878: Unchecked privileges on a view referring to a table from another 
  database.
  
  If a user has a right to update anything in the current database then the 
  access was granted and further checks of access rights for underlying tables
  wasn't done correctly. The check is done before a view is opened and thus no
  check of access rights for underlying tables can be carried out.
  This allows a user to update through a view a table from another database for
  which he hasn't enough rights.
  
  Now the mysql_update() and the mysql_test_update() functions are forces
  re-checking of the access rights after a view is opened.
[11 May 2007 19:21] 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/26530

ChangeSet@1.2479, 2007-05-11 23:19:11+04:00, evgen@moonbone.local +4 -0
  Bug#27878: Unchecked privileges on a view referring to a table from another 
  database.
  
  If a user has a right to update anything in the current database then the 
  access was granted and further checks of access rights for underlying tables
  wasn't done correctly. The check is done before a view is opened and thus no
  check of access rights for underlying tables can be carried out.
  This allows a user to update through a view a table from another database for
  which he hasn't enough rights.
  
  Now the mysql_update() and the mysql_test_update() functions are forces
  re-checking of access rights after a view is opened.
[11 May 2007 20:48] 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/26540

ChangeSet@1.2484, 2007-05-12 00:46:07+04:00, evgen@moonbone.local +2 -0
  grant.result, grant.test:
    Corrected test case for the bug#27878.
[13 May 2007 6:17] Bugs System
Pushed into 5.1.19-beta
[13 May 2007 6:19] Bugs System
Pushed into 5.0.42
[17 May 2007 14:20] Paul DuBois
Noted in 5.0.42, 5.1.19 changelogs.

Security fix: Use of a view could allow a user to gain update
privileges for tables in other databases.
[20 Jul 2007 16:13] Paul DuBois
CVE number has been assigned:
http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2007-3782
[14 Feb 2011 0:06] OthepeTuh OthepeTuh
Sorry for the stupid question. What is the best search engine http://google.com or http://yahoo.com?
[11 Dec 2019 18:35] Landing page
Good post. Thanks for sharing it. <a href="https://www.fiverr.com/extensiveseo/design-high-converting-responsive-landing-page" title="landing page">Landing Page</a>
[11 Mar 2020 9:02] kajabi website
Very informative post about <b><a href="https://www.fiverr.com/extensiveseo/create-fully-responsive-kajabi-website" title="kajabi website">membership site</a></b> .Thanks for sharing it.
[13 Jun 2020 2:31] Can Zara
I'm still having issues with these two sites.
https://andwebtraffic.org
https://substarinc.com
[26 Aug 2020 17:22] jim bramall
Finally found a patch, great work. 

https://traffsio.com
[27 Sep 2020 5:07] Web designer
Very useful and helpful information. Good work.You can checkout our https://webdesignerxx.online/ service.
[12 Nov 2020 14:25] Will Smith
very nice article 

You can checkout our website to <a href="https://allonlineservices.com"> Buy google reviews</a>
[19 Apr 2022 18:29] dav wibli
Thank you for this documentation, I have a somewhat similar problem on my database (on the site http://laviedenosancetres.fr) and it is back to normal
[29 Jun 2023 15:05] Can Zara
I have the same issue with my site. https://pinsia.com 
Can anyone help me?
[26 Oct 2023 21:30] Jake Cresswell
Excellent fix for Incall here in UK https://www.escortslocal.co.uk