Bug #34553 SHOW CREATE VIEW output depends on DEFINER grants

• Submitted: 14 Feb 2008 16:03
• Modified: 18 Jan 2013 1:38
• Reporter: Andrii Nikitin
• Status: Closed
• Category: MySQL Server: Views
• Severity: S3 (Non-critical)
• Version: 5.0.50/5.1
• OS: Any
• CPU Architecture: Any

[14 Feb 2008 16:03] Andrii Nikitin

Description:
If definer for view doesn't exist or have no SELECT grant for referenced table, then SCHEMA name is included into output. (applies to mysqldump also).

Can output for root depend on others' permissions?

when no permission:
% show create view va;
.. `test23145`.`tbl`.`gcampaignid` ...

whith SELECT permission on `tbl`:
% show create view va;
... `tbl`.`gcampaignid` ...

How to repeat:
run script and compare output for SHOW CREATE VIEW after grant

drop database if exists test23145;

create database test23145;

use test23145;

CREATE TABLE `tbl` (
`xdate` date NOT NULL,
`gcampaignid` bigint(20) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

create definer=a23145 view va as select `tbl`.`gcampaignid` AS `gcampaignid`,max(`tbl`.`xdate`) AS `lastreportdate` from `tbl` group by `tbl`.`gcampaignid`;

grant select on *.* to a23145;
revoke all privileges, grant option from a23145;
-- drop user a23145; -- (optionally)

show create view va;
grant select on test23145.tbl to a23145;
show create view va;

Suggested fix:
provide output that doesn't depends on definer privileges

[14 Feb 2008 20:09] MySQL Verification Team

Thank you for the bug report. Verified as described.

[18 Jan 2013 1:38] Paul DuBois

Noted in 5.7.1 changelog.

The output for SHOW CREATE VIEW could vary depending on the DEFINER
account privileges.