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 Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.50/5.1 OS:Any
Assigned to: CPU Architecture:Any

[14 Feb 2008 16:03] Andrii Nikitin
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;

`xdate` date NOT NULL,
`gcampaignid` bigint(20) NOT NULL default '0'

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.