MySQL Bugs: #22763: Disrepancy between SHOW CREATE VIEW and I

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:	Sergei Glukhov	CPU Architecture:	Any
Tags:	Q1, regression

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`

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.

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

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.

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

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.

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.

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.

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.

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.

Bug #39733 was marked as duplicate of this one.

Pushed into 5.0.72  (revid:sergey.glukhov@sun.com-20081002093707-gzrhy7v09omfszqh) (version source revid:sergey.glukhov@sun.com-20081002093707-gzrhy7v09omfszqh) (pib:4)

Pushed into 5.1.30  (revid:sergey.glukhov@sun.com-20081002093707-gzrhy7v09omfszqh) (version source revid:mats@sun.com-20081008113713-2vxny72m5w1tywoi) (pib:4)

This is actually pushed to 5.1.29, not 5.1.30.

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.

Pushed into 6.0.8-alpha  (revid:sergey.glukhov@sun.com-20081002093707-gzrhy7v09omfszqh) (version source revid:sergey.glukhov@sun.com-20081002111648-n45momm93739j51y) (pib:5)

Noted in 6.0.8 changelog.

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)

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)

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)

This bug is not yet fixed. Tried it on Community edition V 8.0.15. 
When selecting from the information_schema.VIEWS table, the VIEW_DEFINITION column output is empty for views which are not created by the user who is running the select query.