Bug #22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
Submitted: 27 Sep 2006 20:37 Modified: 17 Oct 2008 17:11
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0.26-bk OS:Linux (Linux Suse 10.1)
Assigned to: Sergey Glukhov CPU Architecture:Any
Tags: Q1, regression
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[27 Sep 2006 20:37] Dmitry Lenev
Description:
In MySQL 5.0.24 user who had SELECT and SHOW VIEW privileges on database was able to see definition of any view in this database (even created by other user) using both INFORMATION_SCHEMA.VIEWS system view and SHOW CREATE VIEW statement.
In MySQL 5.0.26 such user can still use  SHOW CREATE VIEW for this purprose,
but can't get the definition using I_S.VIEWS.

Therefore in 5.0.26 we have undocumented behavior change and disrepancy in behavior between I_S.VIEWS and SHOW CREATE VIEW.

How to repeat:
# Here is the script for mysql test-suite which demostrates the problem
--disable_warnings
create database mysqltest;
--enable_warnings

create table mysqltest.t1 (a int);
create view mysqltest.v1 as select * from mysqltest.t1;

# create user with enough privileges to observe view definition
grant show view on mysqltest.* to mysqltest_1@localhost;
grant select on mysqltest.* to mysqltest_1@localhost;

connect (user1,localhost,mysqltest_1,,test);
connection user1;
select table_schema, table_name, view_definition from information_schema.views;
# for 5.0.24a above returns
#table_schema  table_name      view_definition
#mysqltest     v1      /* ALGORITHM=UNDEFINED */ select `mysqltest`.`t1`.`a` AS `a` from `mysqltest`.`t1`
# for 5.0.26-bk just
# table_schema  table_name      view_definition
# mysqltest     v1
show create view mysqltest.v1;
# in both cases returns
# View  Create View
# v1    CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest`.`v1` AS select `mysqltest`.`t1`.`a` AS `a` from `mysqltest`.`t1`
[28 Sep 2006 11:17] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.26-BK (ChangeSet@1.2288, 2006-09-27 20:11:11+05:00) on Linux.
[25 Dec 2006 12:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17381

ChangeSet@1.2350, 2006-12-25 16:36:44+04:00, gluh@mysql.com +5 -0
  Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
  if user does not have privileges on underlying tables then we hide view body
  if user has priviliges on underlying tables and user is definer or has 'SHOW_VIEW_ACL' privilege
  then we show body
[5 Mar 2007 14:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21152

ChangeSet@1.2433, 2007-03-05 18:46:59+04:00, gluh@mysql.com +7 -0
  Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
  if ((user has priviliges on underlying tables) or 
     (current_user is definer and security context is definer)) and 
      user has 'SHOW_VIEW_ACL' on view and user has 'SELECT_ACL' on view
  then we show body.
[18 Feb 2008 15:52] Matthias Leich
Problem detected during work on 
WL#4203 Reorganize and fix the data dictionary tests
        of testsuite funcs_1
which seems related to this bug:

CREATE USER 'testuser1'@'localhost';
CREATE VIEW test.v1 AS SELECT '1';

SELECT table_name,view_definition FROM information_schema.views
WHERE table_name = 'v1';
table_name	view_definition
v1	/* ALGORITHM=UNDEFINED */ select _latin1'1' AS `1`

ALTER DEFINER = 'testuser1'@'localhost'
VIEW test.v1 AS SELECT 'abc';

# !! I get here the view definition presented. !!
SHOW CREATE VIEW test.v1;
View	Create View
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`testuser1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'abc' AS `abc`

SELECT table_name,view_definition FROM information_schema.views
WHERE table_name = 'v1';
table_name  view_definition
v1          <empty>   !! Why is here the view definition missing
                         though I have sufficient privileges and
                         SHOW CREATE VIEW works as expected.

My environment:
- MySQL 5.0-build 
  last ChangeSet@1.2572.1.1, 2008-02-07
- compile-pentium-debug-max
- Linux (OpenSuSE 10.3) 64 Bit
- Intel Core2Duo 6400 64 Bit
[28 Aug 2008 8:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52811

2678 Sergey Glukhov	2008-08-28
      Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
      The problem:
      I_S views table does not check the presence of SHOW_VIEW_ACL|SELECT_ACL privileges for a view.
      It leads to disrepancy between SHOW CREATE VIEW and I_S.VIEWS.
      The fix:
      added appropriate check.
[5 Sep 2008 13:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/53348

2678 Sergey Glukhov	2008-09-05
      Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
      The problem:
      I_S views table does not check the presence of SHOW_VIEW_ACL|SELECT_ACL privileges for a view.
      It leads to discrepancy between SHOW CREATE VIEW and I_S.VIEWS.
[29 Sep 2008 9:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54646

2685 Sergey Glukhov	2008-09-29
      Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
      The problem:
      I_S views table does not check the presence of SHOW_VIEW_ACL|SELECT_ACL
      privileges for a view. It leads to discrepancy between SHOW CREATE VIEW
      and I_S.VIEWS.
      The fix:
      added appropriate check.
[2 Oct 2008 9:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55032

2693 Sergey Glukhov	2008-10-02
      Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
      The problem:
      I_S views table does not check the presence of SHOW_VIEW_ACL|SELECT_ACL
      privileges for a view. It leads to discrepancy between SHOW CREATE VIEW
      and I_S.VIEWS.
      The fix:
      added appropriate check.
[2 Oct 2008 11:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55052

2693 Sergey Glukhov	2008-10-02
      Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
      The problem:
      I_S views table does not check the presence of SHOW_VIEW_ACL|SELECT_ACL
      privileges for a view. It leads to discrepancy between SHOW CREATE VIEW
      and I_S.VIEWS.
      The fix:
      added appropriate check.
[3 Oct 2008 6:15] Sveta Smirnova
Bug #39733 was marked as duplicate of this one.
[9 Oct 2008 17:28] Bugs System
Pushed into 5.0.72  (revid:sergey.glukhov@sun.com-20081002093707-gzrhy7v09omfszqh) (version source revid:sergey.glukhov@sun.com-20081002093707-gzrhy7v09omfszqh) (pib:4)
[9 Oct 2008 17:45] Bugs System
Pushed into 5.1.30  (revid:sergey.glukhov@sun.com-20081002093707-gzrhy7v09omfszqh) (version source revid:mats@sun.com-20081008113713-2vxny72m5w1tywoi) (pib:4)
[15 Oct 2008 15:00] Paul Dubois
This is actually pushed to 5.1.29, not 5.1.30.
[15 Oct 2008 16:39] Paul Dubois
Noted in 5.0.72, 5.1.29 changelogs.

For access to the INFORMATION_SCHEMA.VIEWS table, the server did not
check the SHOW VIEW and SELECT provileges, leading to inconsistency
between output from that table and the SHOW CREATE VIEW statement.

Setting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:41] Bugs System
Pushed into 6.0.8-alpha  (revid:sergey.glukhov@sun.com-20081002093707-gzrhy7v09omfszqh) (version source revid:sergey.glukhov@sun.com-20081002111648-n45momm93739j51y) (pib:5)
[17 Oct 2008 17:11] Paul Dubois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:04] Bugs System
Pushed into 5.1.29-ndb-6.2.17  (revid:sergey.glukhov@sun.com-20081002093707-gzrhy7v09omfszqh) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:22] Bugs System
Pushed into 5.1.29-ndb-6.3.19  (revid:sergey.glukhov@sun.com-20081002093707-gzrhy7v09omfszqh) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:48] Bugs System
Pushed into 5.1.29-ndb-6.4.0  (revid:sergey.glukhov@sun.com-20081002093707-gzrhy7v09omfszqh) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)