MySQL Bugs: #27878: Use of view overrides column update privileges on underlying table

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

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

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

test case

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

Thank you for the report.

Verified on Linux using attached test case. All versions are affected.

better test case

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

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.

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.

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.

Pushed into 5.1.19-beta

Pushed into 5.0.42

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.

CVE number has been assigned:
http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2007-3782

Sorry for the stupid question. What is the best search engine http://google.com or http://yahoo.com?

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>

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.

I'm still having issues with these two sites.
https://andwebtraffic.org
https://substarinc.com

Finally found a patch, great work. 

https://traffsio.com

Very useful and helpful information. Good work.You can checkout our https://webdesignerxx.online/ service.

very nice article 

You can checkout our website to <a href="https://allonlineservices.com"> Buy google reviews</a>

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

I have the same issue with my site. https://pinsia.com 
Can anyone help me?

Excellent fix for Incall here in UK https://www.escortslocal.co.uk