MySQL Bugs: #84319: Privilege problems in delete with alias

Bug #84319	Privilege problems in delete with alias
Submitted:	22 Dec 2016 12:32	Modified:	26 Jan 2017 17:13
Reporter:	Gert-Jan Kreeft	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	privilege alias delete denied

Description:
A user has the privileges to delete from a certain table (lets say test.data) and it's possible to delete records from this table like so:

DELETE FROM test.data WHERE id = 43

This works regardless of the current schema for the user. But if I rewrite this statement to the following:

DELETE td FROM test.data td WHERE td.id = 43

it only works from the test schema. From any other schema I get an error "DELETE command denied to user ...". So it looks like the privilege system gets confused when I try to use table aliases in the delete statement.

How to repeat:
Create table in a test schema. Give a user delete privileges for tables in this schema.
Change to another schema and issue a delete statement on the table in the test schema that uses a table alias.

Looks related to https://bugs.mysql.com/bug.php?id=77509.?

Very older issue: https://bugs.mysql.com/bug.php?id=12811.

@Miguel, both issues address this problem so I suppose it won't be fixed anywhere soon. It would be helpful if the MySQL documentation warned about this limitation. It took me a lot time to figure out what was going on.

Thank you the bug report. Please correct me and print here how you got the issue. I needed to give to same user privilege to another schema so I can use another schema than the test one. Thanks.

c:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36 Source distribution PULL: 2016-DEC-25

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > create database test;
Query OK, 1 row affected (0.03 sec)

mysql 5.6 > create database test2;
Query OK, 1 row affected (0.00 sec)

mysql 5.6 > create table test.data (id int);
Query OK, 0 rows affected (0.42 sec)

mysql 5.6 > insert into test.data values (43);
Query OK, 1 row affected (0.09 sec)

mysql 5.6 > grant delete on test.data to 'user1'@'localhost' identified by 'user1';
Query OK, 0 rows affected (0.14 sec)

mysql 5.6 > exit
Bye

c:\dbs>5.6\bin\mysql -uuser1 -p --port=3560
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.36 Source distribution PULL: 2016-DEC-25

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test2
ERROR 1044 (42000): Access denied for user 'user1'@'localhost' to database 'test2'
mysql> exit
Bye

c:\dbs>56c

c:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.36 Source distribution PULL: 2016-DEC-25

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > grant delete on test2.* to 'user1'@'localhost' identified by 'user1';
Query OK, 0 rows affected (0.05 sec)

mysql 5.6 > exit
Bye

c:\dbs>5.6\bin\mysql -uuser1 -p --port=3560
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.36 Source distribution PULL: 2016-DEC-25

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test2
Database changed
mysql> DELETE td FROM test.data td WHERE td.id = 43;
Query OK, 1 row affected (0.25 sec)

Please check if you are able to perform the delete command when the user has the select privilege too beside of the delete one. Anyway I think this bug report is duplicate of the one I pointed before. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".