Comment 2 for bug 1003939

Could we compromise and have a --no-check-auth option, so that in
situations like these we can overcome it? or is the workaround of
"just grant duplicate meaningless permissions" workaround enough?

-Sheeri

On Thu, May 24, 2012 at 1:00 PM, Baron Schwartz <email address hidden> wrote:
> The problem is that there's no way to win with the way we are currently
> doing this.  Here is the history: we used to not try to check
> privileges. But then the tool would do a lot of work and "fail late." We
> were trying to "fail early" for a better user experience. So we tried
> SHOW FULL COLUMNS, and a bunch of other things like a LIMIT 0
> update/delete against the table.
>
> But SHOW FULL COLUMNS actually lies about the privileges. I don't have
> any bugs handy to reference, but I know I've seen this before. (Maybe
> someone has even reported it before against this tool, or pt-table-
> sync.)  And a LIMIT 0 change to the table will throw an error about
> statement-based logging blah blah. This might be fixable by starting a
> transaction, issuing the change, and then rolling it back... but that
> won't work for MyISAM blah blah. Oh, so we could do SHOW GRANTS and ...
> ugh, that's going to be even worse and less reliable.
>
> So we are stuck in a hell of special cases and no-win and so on. We just
> have to bite the bullet and go back to the following approach, in my
> opinion: don't check. Just do it. If it fails it fails, and the user
> will have to fix it.
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1003939
>
> Title:
>  pt-table-checksum says authorization isn't there when it is
>
> Status in Percona Toolkit:
>  New
>
> Bug description:
>  I started with these grants:
>
>  mysql> show grants;
>  +------------------------------------------------------------------------------------------------------------------------------------------------------+
>  | Grants for checksum@localhost                                                                                                                        |
>  +------------------------------------------------------------------------------------------------------------------------------------------------------+
>  | GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'checksum'@'localhost' IDENTIFIED BY PASSWORD 'HASH ELIDED' |
>  | GRANT ALL PRIVILEGES ON `percona`.* TO 'checksum'@'localhost'                                                                                        |
>  +------------------------------------------------------------------------------------------------------------------------------------------------------+
>  2 rows in set (0.00 sec)
>
>  mysql> exit
>  Bye
>  [root@db1 ~]# mysql -u checksum -p -h db2
>  Enter password:
>  Welcome to the MySQL monitor.  Commands end with ; or \g.
>  Your MySQL connection id is 940282
>  Server version: 5.5.20-55-log Percona Server (GPL), Release rel24.1, Revision 217
>
>  Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights
>  reserved.
>
>  Oracle is a registered trademark of Oracle Corporation and/or its
>  affiliates. Other names may be trademarks of their respective
>  owners.
>
>  Type 'help;' or '\h' for help. Type '\c' to clear the current input
>  statement.
>
>  mysql> show grants;
>  +---------------------------------------------------------------------------------------------------------------------------------------------------------+
>  | Grants for checksum@db2                                                                                                                        |
>  +---------------------------------------------------------------------------------------------------------------------------------------------------------+
>  | GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'checksum'@'db1' IDENTIFIED BY PASSWORD 'HASH ELIDED' |
>  | GRANT ALL PRIVILEGES ON `percona`.* TO 'checksum'@'db1'                                                                                        |
>  +---------------------------------------------------------------------------------------------------------------------------------------------------------+
>  2 rows in set (0.00 sec)
>
>  mysql> exit
>
>  But, when I try to run pt-table-checksum, I get:
>
>  [root@db1 ~]# pt-table-checksum   --user=checksum --password=ELIDED
>  05-24T06:07:29 User does not have all privileges on --replicate table `percona`.`checksums`.
>
>
>  But I have granted all on percona.*, which surely includes percona.checksums.
>
>
>  I was able to get around it by granting permissions to the table specifically:
>
>  mysql> grant all on percona.checksums to checksum@localhost identified
>  by 'checksum'; grant all on percona.checksums to checksum@db1
>  identified by 'checksum';
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-toolkit/+bug/1003939/+subscriptions

--
- Sheeri K. Cabral

http://tinyurl.com/mysqlbook will take you to the Amazon.com page for
my book, "MySQL Administrator's Bible".