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

2010-10-20 Thread Tanmay Pradhan 
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

On Wed, Oct 20, 2010 at 10:14 AM, Tanmay Pradhan tanma...@gmail.com wrote:
 Yes, r...@localhost entry is still present in user table. Only
 root@'%' is deleted. So it's not obvious to fail.

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

 u...@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;

 u...@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 |

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

2010-10-19 Thread 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



On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan tanma...@gmail.com wrote:

 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 -p
 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=prajapat...@gmail.com

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

2010-10-19 Thread Michael Dykman 
Of course you know you did not delete r...@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
prajapat...@gmail.com wrote:
 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



 On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan tanma...@gmail.com wrote:

 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 -p
 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=prajapat...@gmail.com






-- 
 - michael dykman
 - mdyk...@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=arch...@jab.org

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

2010-10-19 Thread Shawn Green (MySQL) 

Hellpo Krishna,

On 10/19/2010 8:40 AM, Krishna Chandra Prajapati wrote:

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.




Krishna
CGI.COM



On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhantanma...@gmail.com  wrote:


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



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



Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE  abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
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



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=arch...@jab.org

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

2010-10-19 Thread Tanmay Pradhan 
Yes, r...@localhost entry is still present in user table. Only
root@'%' is deleted. So it's not obvious to fail.

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

u...@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;

u...@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

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

2010-10-18 Thread Tanmay Pradhan 
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 -p
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=arch...@jab.org