MySQL Bugs: #54371: Privileges required to use --events option for mysqldump

Bug #54371	Privileges required to use --events option for mysqldump
Submitted:	9 Jun 2010 15:32	Modified:	10 Jun 2010 12:51
Reporter:	Manuel Rüter	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.1.44, 5.1.49-bzr	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	events, mysqldump, privileges, show

Description:
The --events option for mysqldump seems to require the events privilege even if there are no events to be dumped. Otherwise an error is thrown.

How to repeat:
I have a MySQL 5.1.44 Server with a simple database on it and wanted to create a SQL-Dump from it. I used the mysqldump tool which comes with MySQL 5.1 to do this.
mysqldump --version
mysqldump Ver 10.13 Distrib 5.1.47, for pc-linux-gnu (i686)

If I use just the default options everything works fine and the dump is created:
mysqldump -hsomehost -usomeuser -p some_db_name > dump.sql

I then tried to add the --events option to the command but left everything else as it was befor:
mysqldump --events -hsomehost -usomeuser -p some_db_name > dump.sql

In this case I got the error message:
mysqldump: Couldn't execute 'show events': User 'someuser@'somehost' does not have privileges for database 'some_db_name' (1044)

My user does not have any global or events privileges set. If I set the events privilege for the given database and user the error message disappears.

This behavior seems to be incorrect to me. Should not the events work in the same way as triggers do? If I use the --triggers option no such error is thrown if the user does not have trigger privileges. I checked the documentation but could not find any information regarding privileges required to execute the show events statement.

The error would make some sense if the database has in fact some events to be dumped but my database has none.

My problem in particular is that I want to build a script which dumps many different databases. In order to avoid this error I can not use --events always as I do with --triggers to get the events if any exist but have to check for each DB if the events privilege is set.

Suggested fix:
Can this errormessage be removed as it is for triggers or at the very least be downgraded to a warning please.

Can you, please, provide the results of SHOW GRANTS statement for your user? Look:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uuser1 -puser1 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.1.48-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*34D3B87A652E7F0D1D371C3DBF28E291705468C4' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'user1'@'localhost'                                                      |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysqldump -uuser1 -puser1 test > /tmp/dump1.sql
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysqldump -uuser1 -puser1 --events test > /tmp/dump1.sql

I do not see any problems like yours in this case.

Hello Valeriy,

here the information you requested:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 419531
Server version: 5.1.44 Gentoo Linux mysql-5.1.44-r1

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants
+-----------------------------------------------------------------------------+
| Grants for someuser@172.16.0.0/255.240.0.0
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'someuser'@'172.16.0.0/255.240.0.0' IDENTIFIED BY PASSWORD 53616e455442c041'

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `some_db_name`.* TO 'someuser'@'172.16.0.0/255.240.0.0' |

As I have written before I receive no error message if the user has the events privilege. This is why it works in your example because you granted ALL PRIVILEGES ON `test`.* TO 'user1'@'localhost'

I just think it does not make sense having to grant the events privilege to all users in order to be able to use them with mysqldump. And again, as you can see my user does not have the triggers privilege but I can use the --triggers option with mysqldump without an error. I think it should work in the same way for --events.

You are right:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uuser1 -puser1 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*34D3B87A652E7F0D1D371C3DBF28E291705468C4'                                                                                              |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `test`.* TO 'user1'@'localhost' |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysqldump -uuser1 -puser1 --triggers test > /tmp/dump1.sql
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysqldump -uuser1 -puser1 --events test > /tmp/dump1.sql
mysqldump: Couldn't execute 'show events': Access denied for user 'user1'@'localhost' to database 'test' (1044)