Bug #9838 INFORMATION_SCHEMA.COLUMNS columns of granted views missing
Submitted: 12 Apr 2005 10:42 Modified: 19 May 2005 0:37
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 OS:
Assigned to: Sergei Glukhov CPU Architecture:Any

[12 Apr 2005 10:42] Matthias Leich
Description:
If somebody has access permissions (Example: SELECT ) to a VIEW 
- "owned"/created by another user and 
- created within a database where the user, getting the access right,
  has no further rights
he should be able to retrieve the column definitions via
SELECT ... INFORMATION_SCHEMA.COLUMNS .....
This worked sufficient till around 5. April. Now I miss the records 
about the VIEW columns.
The basic problem seems to be that the view belongs to a database
( <> 'test' !!) where my test user no permissions except the right to 
access this VIEW.
DESCRIBE <view table> is a workaround for this bug.

Please have a look into the attached test cases.

BTW: I know the open bugs within the field of granting permissions 
        of VIEWs. But these bugs were already existing before this new 
        bug started to harm NIST test cases(ist001,ist003) that worked
        correct in history.
        Please also note that
            DESCRIBE <view> and SELECT ... VIEW is successfull.

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1812, 2005-04-11

How to repeat:
Please use the statements above or the attached test case 
ml66.test , copy it to mysql-test/t

  touch r/ml66.result     # Produce a dummy file with expected
                                  # results.
  ./mysql-test-run ml66
  inspect r/ml66.reject    # The protocol of the execution.
[12 Apr 2005 10:44] Matthias Leich
Test case with comments and variations of the problem

Attachment: ml66.test (application/test, text), 1.68 KiB.

[20 Apr 2005 10:58] 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/internals/24166
[21 Apr 2005 16:36] Matthias Leich
Hi, I checked the patch today. The behaviour of the NIST tests changed a bit.
Now I get a bigger row count (that means number of columns).

But the problem within my test case file is not solved.

SELECT * FROM testdb.v1;
f1
<empty result>             # That means I am allowed to select on the view. 
DESCRIBE testdb.v1;
Field Type Null Key Default Extra
f1 bigint(20) YES  NULL   # That means I get informations about the columns.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME= 'v1';
                                   # Here I get only the heading of the table but no rows.
This is totally inconsistent. If I have the right to SELECT on a view +
describe shows me informations, I must be able to get similar
informations via SELECT ... FROM INFORMATION_SCHEMA.COLUMNS.
[26 Apr 2005 12:21] 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/internals/24313
[29 Apr 2005 7:25] Per-Erik Martin
Approved with the addtion of Matthia's second test case.
[7 May 2005 8:58] 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/internals/24655
[7 May 2005 9:00] Sergei Glukhov
Fixed in 5.0.6 tree
[19 May 2005 0:37] Paul Dubois
Noted in 5.0.6 changelog.