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 10:45	Modified:	17 May 2007 16:20
Reporter:	Phil Anderton
Status:	Closed
Category:	Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.38, 5.1, falcon tree	OS:	Linux
Assigned to:	Evgeny Potemkin	Target Version:

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