[#MDEV-7800] Improper "show grants" presentation with adminer

Comment by Sergei Golubchik [ 2015-03-18 ]

Please, show the output of SHOW GRANTS for the user in question.

Comment by Arnaud Launay [ 2015-03-19 ]

MariaDB [(none)]> show grants for foobar@W.X.Y.Z;

+-----------------------------------------------------------------------------------------------------------------+

| Grants for foobar@W.X.Y.Z                                                                                       |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT SELECT ON *.* TO 'foobar'@'W.X.Y.Z' IDENTIFIED BY PASSWORD 'scrambled'    |

| GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `db1`.* TO 'foobar'@'W.X.Y.Z'                     |

| GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `db2`.* TO 'foobar'@'W.X.Y.Z'                     |

| GRANT ALL PRIVILEGES ON `db3`.* TO 'foobar'@'W.X.Y.Z'                                                           |

+-----------------------------------------------------------------------------------------------------------------+

4 rows in set (0.00 sec)

Comment by Sergei Golubchik [ 2015-03-19 ]

What table does this foobar@W.X.Y.Z user tries to edit? Please, show the output from SHOW FULL COLUMNS FROM for this table for this user.

Comment by Arnaud Launay [ 2015-03-19 ]

He was trying to edit in db1.

Using is login pass from W.X.Y.Z server:

mysql -ufoobar  -p  -h dbserver db1

mysql> SHOW FULL COLUMNS FROM client;

+----------------+--------------+-----------------+------+-----+--------------+----------------+------------+--------------------------------+

| Field          | Type         | Collation       | Null | Key | Default      | Extra          | Privileges | Comment                        |

+----------------+--------------+-----------------+------+-----+--------------+----------------+------------+--------------------------------+

| clientId       | int(11)      | NULL            | NO   | PRI | NULL         | auto_increment | select     |                                |

| name           | varchar(250) | utf8_general_ci | NO   |     | NULL         |                | select     |                                |

| quota          | int(11)      | NULL            | NO   |     | NULL         |                | select     |                                |

| PublicId       | int(11)      | NULL            | NO   |     | NULL         |                | select     |                                |

| Server         | varchar(250) | utf8_general_ci | NO   |     | something    |                | select     |                                |

| isActive       | tinyint(1)   | NULL            | NO   |     | 1            |                | select     |                                |

+----------------+--------------+-----------------+------+-----+--------------+----------------+------------+--------------------------------+

6 rows in set (0.00 sec)

Seems there might be something wrong in maria after all...

Comment by Arnaud Launay [ 2015-03-19 ]

Just tested for completeness, if on the DB server I revoke the select priv:

REVOKE SELECT ON *.* FROM 'foobar'@'W.X.Y.Z' ;

I get the permission I was hoping of getting:

| Field          | Type         | Collation       | Null | Key | Default      | Extra          | Privileges           | Comment                        |

+----------------+--------------+-----------------+------+-----+--------------+----------------+----------------------+--------------------------------+

| clientId       | int(11)      | NULL            | NO   | PRI | NULL         | auto_increment | select,insert,update |                                |

| name           | varchar(250) | utf8_general_ci | NO   |     | NULL         |                | select,insert,update |                                |

| quota          | int(11)      | NULL            | NO   |     | NULL         |                | select,insert,update |                                |

etc

Comment by Sergei Golubchik [ 2015-03-19 ]

it's an upstream bug. MySQL-5.5 shows the same buggy behavior.
The complete test case:

create database mysqltest1;

create table mysqltest1.t1 (a int, b year, c varchar(100));

grant select on *.* to u1@localhost;

grant select, insert, update, delete, lock tables, execute on mysqltest1.* to u1@localhost;

connect(u1, localhost, u1,,);

use mysqltest1;

show grants;

show full columns from t1;

insert t1 value (1,2,3);

connection default;

drop user u1@localhost;

drop database mysqltest1;

[MDEV-7800] Improper "show grants" presentation with adminer Created: 2015-03-18 Updated: 2022-09-08
Status:	Open
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	5.5.42, 10.0
Fix Version/s:	5.5

[MDEV-7800] Improper "show grants" presentation with adminer Created: 2015-03-18 Updated: 2022-09-08