Re: access problem for a particular table

[Divesh Kamra]

Hi 
Run mysql_upgrade command at OS shell 

DK Sent from Phone

 On 28-May-2014, at 1:40 pm, Lentes, Bernd 
 bernd.len...@helmholtz-muenchen.de wrote:
 
 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.len...@helmholtz-muenchen.de
 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
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: access problem for a particular table

[Johan De Meersman]

- Original Message -
 From: Bernd Lentes bernd.len...@helmholtz-muenchen.de
 To: mysql@lists.mysql.com
 Sent: Wednesday, 28 May, 2014 10:10:33 AM
 Subject: access problem for a particular table
 
 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.

You don't need to backup performance_schema or information_schema. Fix the 
backup tool.


 root has these rights:
 
 [...]

Why does the root user have such specific rights? It suggests that you use it 
for application purposes. Typically you'd set up root or another user as admin 
with all privileges on *.*; and NEVER use that for anything but administrative 
purposes.


 backup has these rights:
 
 [...]

As said above, no need to back up performance_schema or information_schema - 
they're dynamically generated by the MySQL server. You've already granted the 
necessary rights (well, there could be more, but you've probably got what you 
need) on *.*, so no more need for all the specifics. Get rid of them, they only 
confuse people looking at them.


 What I understood is that the usage right for root on
 performance_schema.cond_instances means no rights.
[...]
 But the usage right remains, it does not disappear.
 How can I grant these rights to user backup ?

Well, yes and no. It does mean a user has no rights, but it is really something 
implicit that comes with the very existence of a user. Thus, it's only visible 
when a user has no other rights; and you can't revoke it short of dropping the 
user entirely.



-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql