Problem with mysqldump and View in MySQL

backupMySQLmysqldumpview

When using mysqldump to backup MySQL, I got following error.

mysqldump --all-databases --routines >> all.sql
mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied to user 'tungbt'@'192.168.12.197' for column 'id' in table 'hdcn_hd' (1143)

hdkien is a view

CREATE ALGORITHM=UNDEFINED DEFINER=`tungbt`@`192.168.12.197` SQL SECURITY DEFINER VIEW `hdcn`.`hdkien` AS (...striped...)

User tungbt@192.168.12.197 has already had the privilege to select on table hdcn_hd and I can select from view hdkien without problem.

mysql> select * from hdkien limit 1;
+------+-----------+
| id   | shd       |
+------+-----------+
|  876 | ADFADFA1  |
+------+-----------+

More info:

MySQL version: mysql-community-server-5.5.37-4.el6.x86_64
OS: CentOS 6.5

Why I got the error when running mysqldump and how can I resolve it?

Update 1 (2014/04/17)

I run mysqldump with user 'root'@'localhost'.

mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '...striped...' WITH GRANT OPTION                             |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+

The user 'tungbt'@'192.168.12.197' is the definer of view hdcn.hdkien (and the SQL SECURITY is DEFINER.

+------------------------------------------------------------------------------------------------------------------+
| Grants for tungbt@192.168.12.197                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tungbt'@'192.168.12.197' IDENTIFIED BY PASSWORD '...striped...'                             |
| GRANT ALL PRIVILEGES ON `hdcn`.* TO 'tungbt'@'192.168.12.197'                                                      |
+------------------------------------------------------------------------------------------------------------------+

Update 2

$ mysql -ANe"SELECT USER(),CURRENT_USER()"
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+

Update 3

mysql> SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';
+------------------+--------------+
| MySQLGrantsCount | MySQLVersion |
+------------------+--------------+
|               42 | 5.5.37-log   |
+------------------+--------------+

Best Answer

You need to have the SHOW VIEW privilege. I wrote about this Dec 2013 : Which are the minimum privileges required to get a backup of a MySQL database schema?

In that post I show these minimum privileges for a mysqldump

SELECT
SHOW VIEW (If any database has Views)
TRIGGER (If any table has one or more triggers)
LOCK TABLES (If you use an explicit --lock-tables)

You should run this command:

SHOW GRANTS FOR tungbt@192.168.12.197;

If SHOW VIEW is not there, that's the reason why.

UPDATE 2014-04-16 23:06 EDT

When you did this

mysqldump --all-databases --routines >> all.sql

I see you did not specify the user and password. That being the case, you were not logged in as root@localhost. You will have to be explicit in specifying the root user

mysqldump -uroot -p --all-databases --routines >> all.sql

You will see the password prompt. Enter the root@localhost password and you are off and running.

You could also specify the password too

mysqldump -uroot -ppassword --all-databases --routines >> all.sql

Give it a Try !!!

WILD SUGGESTIONS

If you are using .~/my.cnf and still getting an error, you might be hitting this situation in Bug #70907 mysqldump: Couldn't execute 'show table status': SELECT command denied to user '

If the config file is .~/my.cnf is really /root/.my.cnf, perhaps you are not logged in as Linux root. You may have to run sudo.

Please run this command

mysql -ANe"SELECT USER(),CURRENT_USER()"

If you do not see root@localhost twice, then you are not authenticating correctly.

In .my.cnf you need to make sure that user and password are under the [client] section

[client]
user=root
password=rootpassword

not under the [mysql] section.

UPDATE 2014-04-17 13:53 EDT

I cannot help look at that bug report and wonder the following: Since you have DEFINER=tungbt@192.168.12.197, it is possible that root@localhost is behaving like tungbt@192.168.12.197 ? I say this because according to the MySQL Documentation on CREATE VIEW: At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to table columns, the creator must have some privilege for each column in the select list of the definition, and the SELECT privilege for each column used elsewhere in the definition.

You could change the definer of the view to root@localhost and try the mysqldump again

CORRECTIVE ACTION

You may have to recreate the mysqldump after you fix the VIEW in the source database.

If you cannot recreate the mysqldump due to the size, you may have edit the mysqldump to ignore the VIEW definition altogether.

MySQL Permissions – How to Grant Additional Permissions

The fundamental problem you have is this:

You cannot grant another user a privilege which you yourself do not have; the GRANT OPTION privilege enables you to assign only those privileges which you yourself possess.

^{— http://dev.mysql.com/doc/refman/5.6/en/grant.html}

This makes sense, since if it were otherwise, then you could just grant privileges to yourself.

There is one way around this, depending on how much flexibility you have with the calling code -- stored procedures can run with the credentials of the defining user (as opposed to the invoking user). If the calling system could call a procedure instead of using GRANT ..., then you can create a procedure that is executable by the restricted user, granting other privileges as appropriate.