List:General Discussion« Previous MessageNext Message »
From:Bernd Lentes Date:May 28 2014 8:10am
Subject:access problem for a particular table
View as plain text  
Hi,

we just migrated from 5.0 to 5.5. Nearly everything went well. But we can't access one
particular table.
Automysqlback 3.0 says: mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to
user 'backup'@'localhost' for table 'cond_instances' when using LOCK TABLES.

root has these rights:

+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                               
                                                                                          
                 |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD
'*948BD740C15428999D549B9632F3C432415E93A4' WITH GRANT OPTION |
| GRANT SELECT, LOCK TABLES ON `performance_schema`.* TO 'root'@'localhost' WITH GRANT
OPTION                    |
| GRANT SELECT ON `vectordb%`.* TO 'root'@'localhost'                                     
                                                                              |
| GRANT USAGE ON `performance_schema`.`cond_instances` TO 'root'@'localhost' WITH GRANT
OPTION                 |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                            
                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+

backup has these rights:

+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for backup@localhost                                                             
                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'backup'@'localhost' IDENTIFIED BY
PASSWORD '*1827DC630AAEB1E997DB2B212CC94EFD9C431555' |
| GRANT SELECT, LOCK TABLES ON `performance_schema`.* TO 'backup'@'localhost'             
                                              |
| GRANT SELECT ON `vectordb%`.* TO 'backup'@'localhost'                                   
                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+

If I try to specify (as root) exactly the select and lock tables right to user backup, I
get the following error:
mysql> grant select, lock tables on performance_schema.cond_instances to
> 'backup'@'localhost';
ERROR 1142 (42000): SELECT,GRANT,LOC command denied to user 'root'@'localhost' for table
'cond_instances'

What I understood is that the usage right for root on performance_schema.cond_instances
means "no rights".
Trying to revoke seems to work:
mysql> revoke usage on performance_schema.cond_instances from 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

But the usage right remains, it does not disappear.
How can I grant these rights to user backup ?

Thanks for any hint.

Bernd


--
Bernd Lentes

Systemadministration
Institut für Entwicklungsgenetik
Gebäude 35.34 - Raum 208
HelmholtzZentrum münchen
bernd.lentes@stripped
phone: +49 89 3187 1241
fax:   +49 89 3187 2294
http://www.helmholtz-muenchen.de/idg

Die Freiheit wird nicht durch weniger Freiheit verteidigt



Helmholtz Zentrum München
Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH)
Ingolstädter Landstr. 1
85764 Neuherberg
www.helmholtz-muenchen.de
Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe
Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons
Enhsen
Registergericht: Amtsgericht München HRB 6466
USt-IdNr: DE 129521671
Thread
access problem for a particular tableBernd Lentes28 May 2014
  • Re: access problem for a particular tableJohan De Meersman28 May 2014
  • Re: access problem for a particular tableDivesh Kamra3 Jun 2014