Case:
My dev super super had all global privilege boxes checked except for 'File', (using mysql 5.0 and 5.1)
When I tried to remove a database specific privilege for some user this silently failed.
The reason for this is that we try "REVOKE ALL PRIVILEGES ON `test` . * FROM 'piet'@'%';" first and then GRANT all the boxes the were checked.
Since my user did not have the 'File' privilege this was not allowed by mysql. In a comment it states "this query may fail, but this does not matter :o)" but I tend to disagree.
I'm afraid there has to be some code to revoke all privileges that were not selected in the form.
Herman,
here is what I tried on 3.4.0-rc1 and MySQL 5.1.55 (all done as root)
1. create user herman with all global privileges except FILE
2. give him SELECT and INSERT privilege on database X
3. remove the INSERT privilege on database X: it worked
4. revoke all his privileges on database X: it worked
I don't understand "my dev super super".
These steps worked for you because you were logged in as root, which has all privileges.
Te replicate this you should login as your herman user, and then try steps 2-4 on some random unprivileged user.
For me it works also while logged on as the herman user who has all global privileges except FILE.
I've replicated the problem om the demo server, please login using user helmo with password "LFus4X968q4jABBw"
Then just try to remove the global UPDATE privilege for user piet.
When you have AJAX enabled it at first looks like the UPDATE privilege is dropped since the user table again shows 'USAGE' for piet. However when you refresh it's back.
Ok but all this time (in your initial bug report and my subsequent interventions), we were not talking about changing global privileges, just database-specific privileges.
Do you still have problems changing db-specific privileges?
Hmm, I was convinced that I experienced this for db-specific and global privileges.
But as it stands I'm only able to reproduce this for global privs
Herman,
are you asking for phpMyAdmin to not silently fail on this, or for a workaround so that your "almost privileged" user can remove a global privilege that he does not possess himself?
I have been looking for a way to solve this. (But, I got distracted by other changes in the server_privileges file)
I think that the cleanest way would be to store the previous value of a checkbox in a hidden form field.
Comparing teh checked checkboxes with the previous values should allow us to selectively drop the privileges that the user has de-selected.
Ok. For 3.4.0 I think we should at least avoid a silent fail?
Introducing a new error message would violate the string freeze.
The easiest fix I can currently think of would be to replace PMA_DBI_try_query with PMA_DBI_query for $sql_query0.
But we should be careful not to break any other usecase.
I agree with this fix (+ removing the comment that it "may fail"), are you doing it?
Temporary fix committed.
To recap:
The better fix would be to not use 'REVOKE ALL PRIVILEGES' at all. We could revoke individual privileges based on the actually un-checked checkboxes.
For this we should store the previous value of a checkbox in a hidden form field.
Or, instead of using hidden fields - read privileges before applying changes, so that code dealing with it won't be scattered around.
My thought was that using hidden fields would lead to less scattering of code...
As the privs are already read when the form is generated.
That was just a suggestion, I haven't looked into the code. I just thought that making privs retrieval into a function (to avoid code duplication) and comparing POST data with function's output would be simpler.
The temporary fix causes this bug:
https://sourceforge.net/tracker/?func=detail&atid=377408&aid=3301996&group_id=23067
I've reverted the temporary fix in Git branch QA_3_4 for inclusion in 3.4.1 and started working on the fix I had in mind.
See attached patch (work in progress...)
Work in progress patch
Related https://sourceforge.net/p/phpmyadmin/bugs/4052/