Bug #61846 SHOW FULL COLUMNS displays incorrect privileges for table

• Submitted: 12 Jul 2011 23:44
• Modified: 13 Jul 2011 6:33
• Reporter: Valentine Gostev
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.5.14, 5.5.15
• OS: Any (Centos 5.5 x86_64, Mac OS X)
• CPU Architecture: Any

[12 Jul 2011 23:44] Valentine Gostev

Description:
If mysql user has global privileges (say SELECT), when user issues 'SHOW FULL COLUMNS FROM <db>.<table>' - privilege column will contain only global privileges listed (even if user has GRANT ALL on current db).

How to repeat:
For MTR:

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1(a SMALLINT);

CREATE USER 'sometestuser'@'%';
GRANT ALL ON `test`.* TO 'sometestuser'@'%';
FLUSH PRIVILEGES;

change_user sometestuser,,test;

SHOW FULL COLUMNS FROM t1;

change_user root;

GRANT SELECT ON *.* TO 'sometestuser'@'%';
FLUSH PRIVILEGES;

change_user sometestuser,,test;
SHOW FULL COLUMNS FROM t1;

DROP TABLE t1;

[13 Jul 2011 6:33] Valeriy Kravchuk

Thank you for the bug report. Verified with current mysql-5.5 on Mac OS X:

macbook-pro:mysql-test openxs$ ./mtr bug61846
Logging: ./mtr  bug61846
110713  9:30:58 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/AX0SnC0Xri/ is case insensitive
110713  9:30:58 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.5.15
Checking supported features...
 - skipping ndbcluster
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /Users/openxs/dbs/5.5/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/Users/openxs/dbs/5.5/mysql-test/var'...
Installing system database...
Using server port 61772

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.bug61846                            [ fail ]
        Test ended at 2011-07-13 09:31:15

CURRENT_TEST: main.bug61846
--- /Users/openxs/dbs/5.5/mysql-test/r/bug61846.result	2011-07-13 09:30:48.000000000 +0300
+++ /Users/openxs/dbs/5.5/mysql-test/r/bug61846.reject	2011-07-13 09:31:15.000000000 +0300
@@ -0,0 +1,14 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1(a SMALLINT);
+CREATE USER 'sometestuser'@'%';
+GRANT ALL ON `test`.* TO 'sometestuser'@'%';
+FLUSH PRIVILEGES;
+SHOW FULL COLUMNS FROM t1;
+Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
+a	smallint(6)	NULL	YES		NULL		select,insert,update,references	
+GRANT SELECT ON *.* TO 'sometestuser'@'%';
+FLUSH PRIVILEGES;
+SHOW FULL COLUMNS FROM t1;
+Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
+a	smallint(6)	NULL	YES		NULL		select	
+DROP TABLE t1;
...

[21 Jul 2012 15:52] Daniel Nichter

I can confirm that this bug is still present in 5.5.21 (mysql-5.5.21-osx10.6-x86_64 binary).