Bug #87829 DDL of Views replicated to other nodes even with "ERROR 1142"!!
Submitted: 21 Sep 2017 16:40 Modified: 20 Oct 2017 13:49
Reporter: Wole Badde Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:5.6 and 5.7 OS:CentOS
Assigned to: CPU Architecture:Any

[21 Sep 2017 16:40] Wole Badde
User without Create View privileges get "ERROR 1142" but VIEW definition still gets replicated to other attached nodes!

So even though the user could not get to create or use the ViEW, the other attached nodes replication from the active node have the View/table definition of the Created VIEW!

How to repeat:
1. Grant SELECT privilege to a user on a schema.
Example : GRANT SELECT ON `dummy_db`.* TO 'metest'@'%' identified by <secret>

2. Then user runs the SQL query to create a view Example "create view dummy_view as select * from  dummy_db.table1 group by amount having count(*)>1"

3. user would get "ERROR 1142 (42000) at line 1: CREATE VIEW command denied to user 'metest'@'%' for table 'table1' but the other attached nodes would have the DDL for the VIEW.

4.Even the datadir would have the table1.frm

Observed  on Versions :
"Server version:5.6.34-79.1-56-log Percona XtraDB Cluster (GPL), Release rel79.1, Revision 7c38350, WSREP version 26.19, wsrep_26.19"

"Server version:5.6.34-79.1-log Percona Server (GPL), Release 79.1, Revision 1c589f9"

and Even "Server version:5.7.18-15-57-log Percona XtraDB Cluster (GPL), Release rel15, Revision 7693d6e, WSREP version 29.20, wsrep_29.20"

Suggested fix:
Most probably related to the replication pluggins
[20 Oct 2017 11:22] MySQL Verification Team
Hello Wole Badde,

Thank you for the report.
I'm not seeing this issue on 5.6.38(or even with 5.6.34) with default settings. Could you please provide exact configuration of master/slave from your environment to reproduce this issue at our end? Thank you!

[20 Oct 2017 13:42] Wole Badde

We are replication out of the PXC cluster from one of the nodes that make up the Cluster.

It has also been confirmed to be a PXC related problem according to the bug report filed with Percona here https://jira.percona.com/browse/PXC-870

This what They said they found

In most of the code (for DDL), PXC does the access check then does the TOI transaction.
  if (check_table_access(thd, DROP_ACL, all_tables, FALSE, UINT_MAX, FALSE))
    goto error;
   /* Conditionally writes to binlog. */
   res= mysql_drop_view(thd, first_table, thd->lex->drop_mode);
However, for CREATE VIEW, we do
   res= mysql_create_view(thd, first_table, thd->lex->create_view_mode);
The access check happens within the mysql_create_view().  So the WSREP_TO_ISOLATION_BEGIN() will replicate the DDL to the other nodes, before the access check.
I am checking the other calls that use WSREP_TO_ISOLATION_BEGIN() to see what other DDL operations will show the same behavior as CREATE VIEW. "

[20 Oct 2017 13:49] MySQL Verification Team
Thank you for confirming that this is something PXC related problem(sorry, could not check provided link as I don't have access to that resource).
If you are able to isolate this problem, and have reproducible test case for our builds then please feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.