MySQL Bugs: #43168: Not possible to backup a view if definer does not exist.

Bug #43168	Not possible to backup a view if definer does not exist.
Submitted:	25 Feb 2009 7:57	Modified:	5 Oct 2010 7:36
Reporter:	Rafal Somla	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Rafal Somla	CPU Architecture:	Any
Tags:	disabled

Description:
It is not possible to backup a view if its definer does not exist. This is not OK, because statement like

CREATE DEFINER=tom VIEW v1 AS SELECT * FROM t1;

succeeds even if user tom does not exist. Thus it should be possible to save the definition of such a view and re-create it at restore time.

See also BUG#26817 and BUG#13331.

How to repeat:
Execute the following test:

CREATE USER tom;
CREATE DEFINER=tom VIEW test.v1 AS SELECT 1;
DROP USER tom;

BACKUP DATABASE test TO 'test.bkp';

The BACKUP statement fails with error:

1692: Failed to add view `test`.`v1` to the catalog

Suggested fix:
This is probably a problem when the view is opened within si_objects to determine its base tables or views (get_view_base_tables/views() functions).

REFINED PROBLEM DESCRIPTION
===========================

1. When a view is added to backup catalogue, its base tables are determined 
(Backup_info::add_view_deps()) using si_objects obs::get_view_base_views(). 

2. The latter function opens the view with open_tables() (si_objects.cc:1520). 
Function open_tables() signals error if view's definer does not exist. Thus 
obs::get_view_base_views() fails and BACKUP operation fails.

3. Even if obs::get_view_base_views() would succeed, later a create statement 
for the view would be obtained by executing SHOW CREATE VIEW statement.

4. For a view with missing definer, SHOW CREATE VIEW reports warning. This is 
interpreted as an error by obs::get_view_create_statement() and again leads to 
BACKUP failure.

5. The problem in point 4 could be solved by ignoring warnings from SHOW CREATE 
VIEW in obs::get_view_create_statement(). 

6. Problem in point 2 can be solved by setting table->prelocking_placeholder 
flag to TRUE before calling open_tables(). This relaxes some checks made by 
open_tables() and at the same time still enables for listing the base tables.

7. With prelocking_placeholder set to TRUE, open_tables() does not check if 
underlying tables exist and BACKUP will ignore this fact as well. This is not 
good because when fixing bug#34902 it was decided that a view with missing base 
tables should stop BACKUP process.

8. Possible fix for 7 would be to have explicit checks for existence of view's 
base tables. I.e., open_tables() will succeed, obs::get_view_base_tables/views() 
would return valid iterator and then we should iterate over base tables/views 
and signal error if one of them is missing.

Additional problem is that it should not be possible to backup a view which is 
invalid because an underlying view/table was altered (BUG#34867). Disabling checks 
as suggested in points 5 and 8 above would allow for backup of such broken views 
and then RESTORE would fail.

I don't see an easy fix for this bug. Here are problems I run into when trying to find a solution.

1. BACKUP fails for a view with non-existing definer because si_objects code fails to open such a view, because open_table() function performs access right checks which fail if user does not exist.

2. It is possible to disable checks in open_table() but then other important problems are not detected. For example the fact that base objects of a view are missing (BUG#34902) or that view is invalid because base view has been altered (BUG#34867). But these conditions must be detected by BACKUP command.

3. If open_table() detects an invalid view, it does not inform about the exact problem detected. Instead, a general ER_VIEW_INVALID error is reported. This seems to be by design - even if more detailed cause of the problem is known, it is explicitly hidden (see TABLE_LIST::hide_view_error()) probably for security reasons. Thus it is not possible to distinguish situation when definer is missing form other errors which should not be ignored.

4. Even if open_table() would not hide exact reason of a failed open attempt, there are no explicit checks in the code for the existence of the definer. In this case an error ER_TABLEACCESS_DENIED_ERROR is detected when access rights are checked (in mysql_derived_prepare). The same error could happen in the situation when the definer exists, but does not have enough privileges. Distinguishing these two situations would require adding extra logic to open_table() with difficult to predict consequences for the functioning of the server.

Triage,
I propose to remove SR60BACKUP tag from this bug and schedule it for later release.

Rationale
---------
Since I don't see an easy/immediate fix for this bug, I think it should be fixed as a part of a bigger task to implement "bast-effort" backup restore, which is scheduled for 6.1 or later release. Without fixing this bug in beta, the behaviour of BACKUP will be consistent with other similar situations, when an attempt to backup an invalid view is made.

Detailed rationale
------------------
This issue is a part of the bigger problem of handling broken dependencies during BACKUP/RESTORE. That is, what should happen if user tries to BACKUP an object with broken dependencies such as:

- a view with some base table/view missing (BUG#34902)
- an view which is invalid because its base view has been altered (BUG#34867)

Also, what should happen if user tries to restore backup image in an environment where some dependencies are not satisfied.

The desired solution of this problem is to have "best-effort" backup/restore operations (e.g. WL#4638). That is, even in presence of problems, the operations continue and try to complete as much work as possible.

However, "best-effort" is not implemented now and is not planned for beta release of backup (see http://forge.mysql.com/wiki/OnlineBackup). The beta release code stops on the first encountered error. At the same time we try to follow these principles:

- BACKUP/RESTORE statements should never crash the server (obviously), but report error instead (this had to be fixed e.g. in BUG#34902 and BUG#34867)
- BACKUP should not create image which can not be successfully restored.

With respect to the issue reported in this bug, the above principles are satisfied. If view with missing definer exists, BACKUP will report an error and refuse to create a backup image. This is the same as the situation when a view with missing base table or a view with altered base view is backed-up.

A piece of the backup_objects_dependency.test is disabled due to this bug.

triage: re-triaging and reducing to I4 as it is expected that ot many will have 'broken objects in production systems.

The feature will not be implemented.