mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES

Discussion:

Tanmay Pradhan

2010-10-19 05:36:46 UTC

Hi,

I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***

In order to restrict root account login from localhost only, I did the
following:
mysql> DELETE FROM user WHERE user = 'root' AND host = '%';
mysql> FLUSH PRIVILEGES;

After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE > abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES

Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -pxxxx
ABC_DATABASE > abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'<IP
Address>' (using password: YES) when using LOCK TABLES

Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?

Thanks for any help.

Regards,
Tanmay

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-***@m.gmane.org

Krishna Chandra Prajapati

2010-10-19 12:40:40 UTC

Permalink

Hi Pradhan,

Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup

Krishna
CGI.COM

Post by Tanmay Pradhan
Hi,
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
mysql> DELETE FROM user WHERE user = 'root' AND host = '%';
mysql> FLUSH PRIVILEGES;
After this,
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE > abc.dump
mysqldump: Got error: 1449: The user specified as a definer
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -pxxxx
ABC_DATABASE > abc.dump
Address>' (using password: YES) when using LOCK TABLES
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Thanks for any help.
Regards,
Tanmay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

Michael Dykman

2010-10-19 16:35:35 UTC

Permalink

Of course you know you did not delete ***@localhost, just root @ '%'
which generally should not matter to mysqldump.

What I suspect is the issue here is that the database you are trying
to dump contains procedures/methods that were defined by a user while
logged in as root@'%'. If you recreate those procs as your database
user, this should correct.

- michael dykman

On Tue, Oct 19, 2010 at 8:40 AM, Krishna Chandra Prajapati

Post by Krishna Chandra Prajapati
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup
Krishna
CGI.COM

--
- michael dykman
- ***@gmail.com

May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-***@m.gmane.org

Tanmay Pradhan

2010-10-20 02:14:21 UTC

Permalink

Yes, ***@localhost entry is still present in user table. Only
root@'%' is deleted. So it's not obvious to fail.

Hi yu.zou,
The ***@localhost entry already had all privileges, except this entry
had empty password column.

***@localhost entry before GRANT
============================
+----------------------------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host | User | Password
| Select_priv | Insert_priv | Update_priv | Delete_priv |
Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv |
File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv |
Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv |
Show_view_priv | Create_routine_priv | Alter_routine_priv |
Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections | max_user_connections |
+----------------------------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | root |
| Y | Y | Y | Y |
Y | Y | Y | Y | Y |
Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y
| Y | Y | | |
| | 0 | 0 | 0 |
0 |

However, I still gave the following cmd.

mysql> GRANT select, lock tables ON *.* TO 'root'@'localhost'
IDENTIFIED BY 'password';
mysql> flush privileges;

***@localhost entry after GRANT
==========================
+----------------------------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host | User | Password
| Select_priv | Insert_priv | Update_priv | Delete_priv |
Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv |
File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv |
Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv |
Show_view_priv | Create_routine_priv | Alter_routine_priv |
Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections | max_user_connections |
+----------------------------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | root |
*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | Y | Y
| Y | Y | Y | Y | Y |
Y | Y | Y | Y | Y
| Y | Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y |
| | | | 0 |
0 | 0 | 0 |

Still mysqldump fails.....

Hi michael dykman,
I dropped all procedures, still mysqldump failed.

What else could be the issue?

Thanks for the help.

Regards,
Tanmay

Post by Michael Dykman
which generally should not matter to mysqldump.
What I suspect is the issue here is that the database you are trying
to dump contains procedures/methods that were defined by a user while
user, this should correct.
- michael dykman
On Tue, Oct 19, 2010 at 8:40 AM, Krishna Chandra Prajapati

--
- michael dykman
May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-***@m.gmane.org

Tanmay Pradhan

2010-10-21 00:43:09 UTC

Permalink

Appreciate any help on this.
Any alternative of mysqldump is also fine with me to take backup while
restricting root login access from localhost only.

Thanks in advance.

Regards,
Tanmay

Post by Tanmay Pradhan
Hi yu.zou,
had empty password column.
============================
+----------------------------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host | User | Password
| Select_priv | Insert_priv | Update_priv | Delete_priv |
Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv |
File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv |
Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv |
Show_view_priv | Create_routine_priv | Alter_routine_priv |
Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections | max_user_connections |
+----------------------------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | root |
| Y | Y | Y | Y |
Y | Y | Y | Y | Y |
Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y
| Y | Y | | |
| | 0 | 0 | 0 |
0 |
However, I still gave the following cmd.
IDENTIFIED BY 'password';
mysql> flush privileges;
==========================
+----------------------------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host | User | Password
| Select_priv | Insert_priv | Update_priv | Delete_priv |
Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv |
File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv |
Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv |
Show_view_priv | Create_routine_priv | Alter_routine_priv |
Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections | max_user_connections |
+----------------------------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | root |
*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | Y | Y
| Y | Y | Y | Y | Y |
Y | Y | Y | Y | Y
| Y | Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y |
| | | | 0 |
0 | 0 | 0 |
Still mysqldump fails.....
Hi michael dykman,
I dropped all procedures, still mysqldump failed.
What else could be the issue?
Thanks for the help.
Regards,
Tanmay

--
- michael dykman
May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-***@m.gmane.org

Shawn Green (MySQL)

2010-10-20 01:36:33 UTC

Permalink

Hellpo Krishna,

Post by Krishna Chandra Prajapati
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup

Not true. The utility mysqldump is just a client like any other program
and can authenticate with the MySQL instance (the database daemon) as
any valid user.

Post by Krishna Chandra Prajapati
Krishna
CGI.COM

To fix this, you need to reset the DEFINER for a TRIGGER defined within
the database so that it is defined as a valid user account.
http://dev.mysql.com/doc/refman/5.1/en/triggers.html

Post by Krishna Chandra Prajapati

Post by Tanmay Pradhan
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -pxxxx
ABC_DATABASE> abc.dump
Address>' (using password: YES) when using LOCK TABLES

In order to issue the LOCK TABLES command, a user needs certain privileges:
http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html

Post by Krishna Chandra Prajapati

Post by Tanmay Pradhan
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?

Securing MySQL is fairly easy. Check out this guide in the manual for
details:
http://dev.mysql.com/doc/refman/5.1/en/security.html
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-***@m.gmane.org