Details
Minor
Description
When trying to use a user with a global SELECT privilege on . , and more privileges (lock/insert/delete/etc) on two other DBs, adminer refuses to update rows in the db.
We opened a bug at adminer:
http://sourceforge.net/p/adminer/bugs-and-features/433/
They told us it was a bug in mariadb...
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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)
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.
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...
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
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;
Please, show the output of SHOW GRANTS for the user in question.