pt-table-checksum says authorization isn't there when it is

Bug #1003939 reported by Sheeri K. Cabral on 2012-05-24
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Triaged
Undecided
Unassigned

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';

Baron Schwartz (baron-xaprb) 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.

Download full text (5.0 KiB)

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 trademark...

Read more...

tags: added: privs pt-table-checksum
Changed in percona-toolkit:
status: New → Triaged
Baron Schwartz (baron-xaprb) wrote :

I don't think that option not to check authorization would actually be a win. I think we just need to rip it all out and let failures be the test of success. The tool does very little upfront work these days anyway, so it should fail much sooner.

Baron Schwartz (baron-xaprb) wrote :

This is a duplicate of bug 916168.

Daniel Nichter (daniel-nichter) wrote :

MySQL 5.5 currently has a bug that affects this: http://bugs.mysql.com/bug.php?id=61846

Daniel Nichter (daniel-nichter) wrote :

For the record:

mysql> SHOW GRANTS FOR CURRENT_USER\G

5.1
===

*************************** 1. row ***************************
Grants for test_user@%: GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF'
*************************** 2. row ***************************
Grants for test_user@%: GRANT ALL PRIVILEGES ON `percona`.* TO 'test_user'@'%'

5.5.21
======

*************************** 1. row ***************************
Grants for test_user@%: GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF'
*************************** 2. row ***************************
Grants for test_user@%: GRANT ALL PRIVILEGES ON `percona`.* TO 'test_user'@'%'

Identical grants, but:

mysql> USE percona; SHOW FULL COLUMNS FROM checksums\G

5.1
===

*************************** 1. row ***************************
     Field: db
      Type: char(64)
 Collation: latin1_swedish_ci
      Null: NO
       Key: PRI
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:

5.5.21
======

*************************** 1. row ***************************
     Field: db
      Type: char(64)
 Collation: latin1_swedish_ci
      Null: NO
       Key: PRI
   Default: NULL
     Extra:
Privileges: select
   Comment:

So as Baron said in bug 916168, I'm just going to remove the privs check and let the tool either work or not, and if it doesn't, then the user will know without a doubt that they need to fix the user's privs.

Daniel Nichter (daniel-nichter) wrote :

Correction: "So as Baron said in _this bug_ ..."

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.