#2200 (ok 2.9.0) Privileges are not being set properly.
Well I searched your database for any comments on this
and have seen a few indirectly related to this but
they all seem to skip around the issue we are see.
So the basics here are we are running MySQL 4.1.14
with phpMyAdmin 2.8.0.3 versions. This same problem
is also clearly duplicated in phpMyAdmin 2.6.3-pl1
The basic problem is when changing privileges for a
user, the system does not revoke the exising privilges
before doing the new "GRANT" command.
You can see this very easily by creating a user in for
this test we created the user "ChangeMe".
The user was created and phpMyAdmin ran the command:
GRANT SELECT , INSERT , UPDATE , DELETE , FILE ON * .
* TO 'ChangeMe'@'localhost'
WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0 ;
Which are the "Global Privileges" we asked for.
Now we want to remove some of those privileges in this
case we want to remove all the privileges leaving only
the "SELECT" privilege.
Here is the command sequence that phpMyAdmin ran:
REVOKE GRANT OPTION ON * . *
FROM 'ChangeMe'@'localhost';
GRANT SELECT ON * . *
TO 'ChangeMe'@ 'localhost'
WITH MAX_QUERIES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0 ;
If you look at the privilegs you will see that they
are all still in force! There is no way within
phpMyAdim to revoke privileges you can only add more
privileges!
We can make things work properly by manually revoking
privilegs and then using phpMyAdmin to put in the ones
we want. But this sort of defeats the purpose of your
tool.
Also we think this issue is implicated in a few posts
that claim deleting users is not working. Since there
are privileges that never get revoke you fail on
trying to delete the user.
I am not sure why there are no other posts on this but
this is really making life complex for us.
By the way earlier versions worked just fine. We are
not sure at what version number this problem was
introduced but as noted above it goes back to at least
2.6.3-pl1.
Please help...we do like your product it is quite cool.
Thanks in advance for your help.
Bernie
Discussion
Logged In: YES
user_id=210714
Cannot reproduce this bug under MySQL 4.1.14. This is
executed after unchecking some global privileges:
REVOKE ALL PRIVILEGES ON *.* FROM 'ChangeMe'@'localhost';
REVOKE GRANT OPTION ON *.* FROM 'ChangeMe'@ 'localhost';
GRANT SELECT ON *.*
TO 'ChangeMe'@ 'localhost'
WITH MAX_QUERIES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0 ;
-----------
In server_privileges.php maybe you can have a look around
line 965 where $sql_query0 is built and echo it. Then around
line 1011 where it is executed, verify if it failed (it will
enter the IF).
Logged In: YES
user_id=1268587
Lem9, thanks for the quick response to this. We will take
a look at SQL commands as they are being executed. It
will take us a bit to run those tests.
However, your prelimary analysis might be right. We have
from time to time had the "REVOKE ALL PRIVILEGES" fail.
It has never been clear to us under what conditions that
happens. We can always REVOKE the privileges by listing
them out but the "ALL" for some reason fails.
So given your analysis and that we have seen this fail I
bet that is what is going on here.
With that our action plan is the following:
1. Determine if phpMyAdmin is getting a failure on
the "REVOKE ALL PRIVILEGES" command.
2. Determine why phpMyAdmin is not reporting that error.
3. Figure out why on Earth the "REVOKE ALL PRIVILEGES"
command is failing.
So we will keep you posted.
Again, thanks for the insight and looking into this so
quickly for us.
Bernie
Logged In: YES
user_id=210714
Bernie,
For point 2, there is a comment in the code "this query may
fail but this does not matter". I am not sure why this
comment is there, but we could easily catch an error at this
point with
if (PMA_DBI_getError())
Logged In: YES
user_id=1268587
Again, Lem9, thanks for the quick response. We looked at
the code and your comments and determined the failure was
indeed due to the failure of the SQL command "REVOKE ALL
PRIVILEGES". Once that command failed which as you noted
is not checked in the file "server_privileges.php" then the
following "GRANT" is just adding new privileges to the
existing ones. And then you have the problem as reported.
By the way this also explains the failure to "DROP" a user
some have reported. It also has a "REVOKE" that might fail
and then the user can not be dropped since they might have
current privileges.
So it looks to me that you can close this case. We just
did not have the correct privileges for the "REVOKE ALL" to
work and that led to not being able to effectively change
specific privileges since that process requires the "REVOKE
ALL" to work first and then come back and add the
privileges that are left.
As a suggestion you might want:
a) Include some warning when the "REVOKE ALL" has failed.
(or maybe even better)
b) Drop back and "REVOKE" the specific privileges when it
becomes clear the "REVOKE ALL" failed. Perhaps you could
do a "SHOW GRANTS" and then modify the result to become
a "REVOKE" statement for the privileges being modify. In
part of fact we were doing just that manually with SQL
commands to remove all the privileges before modifying so
we know that logic would work.
Anyhow, thanks again for the help, keep up the great work.
Bernie
Logged In: YES
user_id=210714
Bernie,
I would like to reproduce this situation to be able to
detect a failure of REVOKE ALL. Could you tell me which
privileges you were lacking to see this fail?
Logged In: YES
user_id=1268587
Lem9, thanks again for the help. When we "fixed" our
problems I did it by giving all privileges to the
particular user and did not look to see what specific
privilege corrected the problem. I thought I saw some
reference to the "REVOKE ALL" in the mySQL on-line
documentation saying something about needing a certain
level of privileges to the "mysql" table otherwise the
command will fail.
In any case here is the status of the "global privileges"
for the user BEFORE I put a "yes" response everywhere....
Select_priv enum -- Y
Insert_priv enum -- Y
Update_priv enum -- Y
Delete_priv enum -- Y
Create_priv enum -- Y
Drop_priv enum -- Y
Reload_priv enum -- Y
Shutdown_priv enum -- Y
Process_priv enum -- Y
File_priv enum -- Y
Grant_priv enum -- Y
References_priv enum -- Y
Index_priv enum -- Y
Alter_priv enum -- Y
Show_db_priv enum -- N
Super_priv enum -- N
Create_tmp_table_priv enum -- N
Lock_tables_priv enum -- N
Execute_priv enum -- N
Repl_slave_priv enum -- N
Repl_client_priv enum -- N
Once I changed them all to "Y" and effect put in place "ALL
PRIVILEGS" everything works as you described.
I hope that helps. Let me know.
Bernie
Logged In: YES
user_id=210714
Ok, with your indications I was able to reproduce. It's
weird, an admin needs all privileges to be able to REVOKE
ALL PRIVILEGES. If for example he does not have
repl_slave_priv (which is irrelevant) he can't do the command.
I added an error message when this happens.
Fixed for 2.9.0.
Logged In: YES
user_id=1268587
Lem9, a final thanks for your help with this.
We agree with your about the weird behavior of needing to
have even irrelevant permissions for the REVOKE ALL
PRIVILEGES work. We looked in the mySQL manual and this
seems a bit ambiguous to us..
This is from the 5.1 version of the manual:
"The REVOKE statement enables system administrators to
revoke privileges from MySQL accounts. To use REVOKE, you
must have the GRANT OPTION privilege, and you must have the
privileges that you are revoking.....
...To revoke all privileges, use the following syntax,
which drops all global, database-, table-, and column-level
privileges for the named user or users:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
To use this REVOKE syntax, you must have the global CREATE
USER privilege or the UPDATE privilege for the mysql
database."
So to us we read this to mean if you have the "CREATE USER"
or the "UPDATE" privilege on the the database then
the "REVOKE ALL" should work.
However, after what we just went through perhaps the
meaning in the first paragraph that you must have "the
privileges you are revoking". So if you don't have "ALL
PRIVILEGES" then you can not do a "REVOKE ALL" and to
have "ALL" that means all even those that seem irrelvant to
the running of that command.
Anyhow, thought this backup information would be helpful
for later.
Again, thanks for taking care of this. Keep up the great
work.
Bernie