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 | 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
On Wed, Oct 20, 2010 at 12:35 AM, Michael Dykman <mdyk...@gmail.com> wrote:
> 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 -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=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
