MySQL Bugs: #68527: "show grants for ..." output is not stable

Bug #68527	"show grants for ..." output is not stable
Submitted:	28 Feb 2013 17:40	Modified:	7 Mar 2013 8:37
Reporter:	Jörg Brühe	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.5.20, 5.5.31, 5.6.11, 5.7.1, 5.1.69, 5.0.97	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I'm the DBA for some 60+ MySQL servers.
To check the privileges granted on them, I run a script that does all necessary "show grants for ..." statements and saves the output in timestamped files.

Comparing that output of consecutive runs, I see differences in the order of privileges reported - even for users which did not have any privilege changed.

Just one example:

 | Grants for jbossesbmmm@%                                            |                                      
 +---------------------------------------------------------------+
 | GRANT USAGE ON *.* TO 'esbmmm'@'%' IDENTIFIED BY PASSWORD ... |
-| GRANT ALL PRIVILEGES ON `jbpm`.* TO 'esbmmm'@'%'              |
 | GRANT ALL PRIVILEGES ON `mmm`.* TO 'esbmmm'@'%'               |
-| GRANT ALL PRIVILEGES ON `esb`.* TO 'esbmmm'@'%'               |
-| GRANT ALL PRIVILEGES ON `mmmreporting`.* TO 'esbmmm'@'%'      |
+| GRANT ALL PRIVILEGES ON `jbpm`.* TO 'esbmmm'@'%'              |
 | GRANT ALL PRIVILEGES ON `juddi3`.* TO 'esbmmm'@'%'            |
+| GRANT ALL PRIVILEGES ON `esb`.* TO 'esbmmm'@'%'               |
 | GRANT ALL PRIVILEGES ON `juddi2`.* TO 'esbmmm'@'%'            |
 | GRANT ALL PRIVILEGES ON `jbm`.* TO 'esbmmm'@'%'               |
 | GRANT ALL PRIVILEGES ON `juddi1`.* TO 'esbmmm'@'%'            |
+| GRANT ALL PRIVILEGES ON `mmmreporting`.* TO 'esbmmm'@'%'      |
 | GRANT ALL PRIVILEGES ON `mysql`.`juddi2` TO 'esbmmm'@'%'      |
 +---------------------------------------------------------------+

While here it is pretty easy to see there is no privilege change, I have other examples which are much worse.

How to repeat:
Run "show grants for ..." and save the output,
add some privilege for a different user on a different database,
run "show grants for ..." again and compare the output.

Suggested fix:
Make sure the order of "show grants for ..." output lines is stable for any account at least in all those cases where no privilege was changed for this specific account.

Better yet, make sure the output order of all unchanged lines is stable, so that only the lines for added/changed/removed objects (dis)appear/differ.

Thank you for the report.

Please add to your tests query SELECT USER(), CURRENT_USER() and log its output together with output of SHOW GRANTS. Then send us results for both runs which give inconsistent results.

Hi Sveta!
This is the output (network name part changed):

+----------------------------------+----------------+
| user()                           | current_user() |
+----------------------------------+----------------+
| appadm@isdeblnnl021.FQDN         | appadm@%       |
+----------------------------------+----------------+

The script always uses that user, in all runs.

Hi  Jörg ,

this can not be true for servers you complain about:

-| GRANT ALL PRIVILEGES ON `jbpm`.* TO 'esbmmm'@'%'              
-| GRANT ALL PRIVILEGES ON `esb`.* TO 'esbmmm'@'%'               |
-| GRANT ALL PRIVILEGES ON `mmmreporting`.* TO 'esbmmm'@'%'      |
+| GRANT ALL PRIVILEGES ON `jbpm`.* TO 'esbmmm'@'%'              |
+| GRANT ALL PRIVILEGES ON `esb`.* TO 'esbmmm'@'%'               |
+| GRANT ALL PRIVILEGES ON `mmmreporting`.* TO 'esbmmm'@'%'      |

appadm@% can not be 'esbmmm'@'%' if you don't use proxy users.

Also this reduced quote of the problem shows most likely you don't see any inconsistency in SHOW GRANTS output, but simply connect to same server in different order.

I tend to think this is not a bug.

There was a misunderstanding:

The script is not reporting the privileges of the current user,
it is run by an admin user to report the privileges of all accounts.

Here is the essential part:

for MACH in `cat FILE-WITH-ALL-HOST-NAMES`
do
    echo "===== $MACH ====="
    # get a list of all accounts, write it to output
    echo 'select user, host, password from mysql.user order by user, host' | \
        mysql -h $MACH -u appadm -pPASSWD --table
    echo
    # get that list again, but feed it into a loop
    # that generates "show grants for ..." statements
    # and pipe them to another client call for execution
    echo 'select user, host, password from mysql.user order by user, host' | \
        mysql -h $MACH -u appadm -pPASSWD | tail -n +2 | \
        while read USR HST PASS
        do
            echo "show grants for '$USR'@'$HST';"
        done | mysql -h $MACH -u appadm '-p!app@dmin!' --table
    echo
    echo
done 2>&1 | tee PRIVS-$TIMESTAMP

User "appadm" has sufficient privileges to run administrative commands from remote.

The "order by" makes sure that all accounts are sorted (order is stable),
now what is missing is the stable output order of "show grants for ..."

Thank you for the feedback.

Verified as described.

Test case for MTR:

create database db1;
create database db2;
create database db3;
create database db4;
create database db5;

grant ALL PRIVILEGES ON db1.* to foo@'%';
grant ALL PRIVILEGES ON db2.* to foo@'%';
grant ALL PRIVILEGES ON db3.* to foo@'%';
grant ALL PRIVILEGES ON db4.* to foo@'%';
grant ALL PRIVILEGES ON db5.* to foo@'%';

show grants for foo@'%';

revoke ALL PRIVILEGES ON db2.* from foo@'%';
grant ALL PRIVILEGES ON db2.* to foo@'%';

show grants for foo@'%';

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Joerg,

Welcome back ! 
This is one of the many reasons the SHOW commands are not really usable : the
can't sort.
So you either need to use a client side sorting (kind of like what mysqltest
does) or go to the INFORMATION_SCHEMA tables.

That's 2 easy ways out : create a proper mysqltest test using the
"sorted_result" option
(http://dev.mysql.com/doc/mysqltest/2.0/en/mysqltest-commands.html) or issue
SELECT .. FROM INFORMATION_SCHEMA.... ORDER BY ... statements instead.