MySQL Bugs: #30018: UPDATE Fails

Bug #30018	UPDATE Fails
Submitted:	24 Jul 2007 21:49	Modified:	30 Aug 2007 13:12
Reporter:	Mr Wakazula	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Windows
Assigned to:	Sveta Smirnova	CPU Architecture:	Any

Description:
SYNOPSIS
If you execute an update that references a view, you will receive the following error "UPDATE command denied to user".

I am *not* trying to update the view.  

DESCRIPTION
1) create two tables (TableA and TableB)
2) create a view of the second table (TableC)
3) create a user with SELECT privileges to all tables
4) grant the user UPDATE privileges to TableA
5) execute and UPDATE against TableA referencing TableC

CONTEXT
Server: Windows 2003 Enterprise SP2
MySql Server: 5.0.45
MySql Query Browser: 1.2.12

How to repeat:
/**** CREATE TABLES ***/

CREATE TABLE  `test`.`tablea` (
  `IdA` int(10) unsigned NOT NULL auto_increment,
  `ValueA` varchar(45) NOT NULL,
  PRIMARY KEY  (`IdA`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE  `test`.`tableb` (
  `IdB` int(10) unsigned NOT NULL auto_increment,
  `ValueB` varchar(45) NOT NULL,
  PRIMARY KEY  (`IdB`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*** CREATE USER ***/
CREATE USER `test`@`%` IDENTIFIED BY 'password';
GRANT select, update, insert, delete ON test.TableA TO 'test'@`%`;
GRANT select ON test.TableB TO 'test'@`%`;
GRANT select ON test.TableC TO 'test'@`%`;
FLUSH PRIVILEGES; /* just to be safe */

/*** EXECUTE QUERY ***/
UPDATE TableA
JOIN TableC ON TableA.IdA = TableC.IdB
SET TableA.ValueA = TableC.ValueB;

/* ERROR: UPDATE command denied to user 'test'@'localhost' for table 'tablec' */

Suggested fix:
The following query should execute without fail:

UPDATE TableA
JOIN TableC ON TableA.IdA = TableC.IdB
SET TableA.ValueA = TableC.ValueB;

Thank you for the bug report. To complete the test case please provide
the create view statement. Thanks in advance.

Hi Miguel.

Here is the information you requested:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW  `test`.`tablec` AS select `tableb`.`IdB` AS `IdB`,`tableb`.`ValueB` AS `ValueB` from `tableb`;

Thank you for the feedback.

You got error "UPDATE command denied to user 'test'@'localhost' for table 'tablec'", but provided GRANT statements for user 'test'@'%'. Please provide output of SHOW GRANTS FOR test@localhost

Also please provide output of SHOW GRANTS FOR `root`@`%`

If I've provided privileges to %... wouldn't 127.0.0.1 be included in that?

Here is the information you requested:

GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
GRANT SELECT ON `test`.`tablec` TO 'test'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.`tablea` TO 'test'@'%'
GRANT SELECT ON `test`.`tableb` TO 'test'@'%'

Thank you for the feedback.

test@localhost and test@% are different users. Please provide output of statements SHOW GRANTS FOR test@localhost and SHOW GRANTS FOR `root`@`%`

MySql 5.7.5. Access Control documentation says:
"a Host value of '%' matches any hostname"

Therefore, if I login as 127.0.0.1, the % rule/grant should be applicable, should it not?

SHOW GRANTS FOR `root`@`%` returns:
GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD
'*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
GRANT SELECT ON `test`.`tablec` TO 'test'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.`tablea` TO 'test'@'%'
GRANT SELECT ON `test`.`tableb` TO 'test'@'%'

SHOW GRANTS FOR test@localhost returns:
There is no such grant defined for user 'test' on host 'localhost'

Thank you for the feedback.

I can not repeat described behaviour in myself environment.

Please provide output of "SELECT user(), current_user()", "SELECT user, host FROM mysql.user" and command you use to connect to mysqld.

Hi Sveta.

I have just completed the test on two separate systems.  Both of which fail.  I don't think you are able to reproduce the error as I have left out one important step.  My appologies.  

To avoid confusion, PLEASE USE THE FOLLOWING FOR TESTING:

/** Test Begin **/
/** Log into MySql Query Browser as the root user **/

CREATE TABLE  `test`.`tablea` (
  `IdA` int(10) unsigned NOT NULL auto_increment,
  `ValueA` varchar(45) NOT NULL,
  PRIMARY KEY  (`IdA`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE  `test`.`tableb` (
  `IdB` int(10) unsigned NOT NULL auto_increment,
  `ValueB` varchar(45) NOT NULL,
  PRIMARY KEY  (`IdB`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `test`.`tablec`
AS select `tableb`.`IdB` AS `IdB`,`tableb`.`ValueB` AS `ValueB` from `tableb`;

DROP USER `test`@`%`;
CREATE USER `test`@`%` IDENTIFIED BY 'password';
GRANT select, update, insert, delete ON test.TableA TO 'test'@`%`;
GRANT select ON test.TableB TO 'test'@`%`;
GRANT select ON test.TableC TO 'test'@`%`;
FLUSH PRIVILEGES; /* just to be safe */

/** Log into MySql Query Browser as test <-- this is the part I forgot **/
/** The following should fail with: UPDATE command denied to user 'test'@'localhost' for table 'tablec' **/

UPDATE TableA
JOIN TableC ON TableA.IdA = TableC.IdB
SET TableA.ValueA = TableC.ValueB;

/** Test End **/

-------------------------------------------------------------
SELECT user(), current_user();
RETURNS: 'test@localhost', 'test@%'

SELECT user, host FROM mysql.user;
RETURNS: SELECT command denied to user 'test'@'localhost' for table 'user'

Thank you for the feedback.

I still can not repeat described behaviour. So I'll close the report as "Can't repeat".

Good morning,

I have just finshed running the test case on two different servers:

Server: MySQL 5.0.45-community-nt via TCP/IP
Client: MySql Client Version 5.1.11
Error: UPDATE command denied to user 'test'@'borris.icebergmedia.com' for table 'tablec'
My Notes: YellowMonkey

Server: MySQL 5.0.41-community-nt via TCP/IP
Client: MySQL Client Version 5.1.11
Error: UPDATE command denied to user 'test'@'localhost' for table 'tablec'
My Notes: Borris

On both occasions, the test failed.  If more information is required to isolate the error, I am more than willing to work with you. It seems unlikely that the error is an abnormality as I can reproduce it on separate systems.

/** TEST CASE: Begin **/
/** Log into MySql Query Browser as the root user **/

CREATE TABLE  `test`.`tablea` (
  `IdA` int(10) unsigned NOT NULL auto_increment,
  `ValueA` varchar(45) NOT NULL,
  PRIMARY KEY  (`IdA`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE  `test`.`tableb` (
  `IdB` int(10) unsigned NOT NULL auto_increment,
  `ValueB` varchar(45) NOT NULL,
  PRIMARY KEY  (`IdB`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW 
`test`.`tablec`
AS select `tableb`.`IdB` AS `IdB`,`tableb`.`ValueB` AS `ValueB` from `tableb`;

DROP USER `test`@`%`;
CREATE USER `test`@`%` IDENTIFIED BY 'password';
GRANT select, update, insert, delete ON test.TableA TO 'test'@`%`;
GRANT select ON test.TableB TO 'test'@`%`;
GRANT select ON test.TableC TO 'test'@`%`;
FLUSH PRIVILEGES; /* just to be safe */

/** Log into MySql Query Browser as test <-- this is the part I forgot **/
/** The following should fail with: UPDATE command denied to user 'test'@'localhost' for
table 'tablec' **/

UPDATE TableA
JOIN TableC ON TableA.IdA = TableC.IdB
SET TableA.ValueA = TableC.ValueB;

/** TEST CASE: End **/