Load Data InFile fails

[Bruce A. Julseth]

I have three users with access to my test MySQL databases, root plus two
others. The following PHP code only works when the user is root. Otherwise
it fails.

$File = addslashes(getc wd() . "\Address.txt") ;
$SQL = "Load Data InFile \"" . $File . "\" into table addresses";
$result = mysql_query($SQ L) or die("Failed to load data");

Both users were created by root from a cmd prompt:

grant all on * to user@localhost identified by 'mypw' with grant options;

What is wrong so that the created users can execute the above code?

Thanks.

Bruce

[Dave]

Re: Load Data InFile fails

On Mon, 01 Aug 2005 21:38:41 -0400, Bruce A. Julseth decided we needed to
hear:
[color=blue]
> I have three users with access to my test MySQL databases, root plus two
> others. The following PHP code only works when the user is root. Otherwise
> it fails.
>
> $File = addslashes(getc wd() . "\Address.txt") ;
> $SQL = "Load Data InFile \"" . $File . "\" into table addresses";
> $result = mysql_query($SQ L) or die("Failed to load data");
>
> Both users were created by root from a cmd prompt:
>
> grant all on * to user@localhost identified by 'mypw' with grant options;
>
> What is wrong so that the created users can execute the above code?
>
> Thanks.
>
> Bruce[/color]

Which db was current when you did the grant? If it was the mysql db,
then you probably just granted access to that rather than the ones
you intended to.
AFAIK (hopefully someone corrects if I'm wrong) a * on its own grants
only to the current db. You should change it to *.* if you want to grant
to all dbs, or use dbname.* for a specific db.
As an aside your privs are very broad here - I guess for test it doesn't
matter, but careful when you move to production.

--
Dave <dave@REMOVEbun dook.com>
(Remove REMOVE for email address)

[Bruce A. Julseth]

Re: Load Data InFile fails


"Dave" <dave@REMOVEbun dook.com> wrote in message
news:pan.2005.0 8.01.21.55.18.1 08530@REMOVEbun dook.com...[color=blue]
> On Mon, 01 Aug 2005 21:38:41 -0400, Bruce A. Julseth decided we needed to
> hear:
>[color=green]
>> I have three users with access to my test MySQL databases, root plus two
>> others. The following PHP code only works when the user is root.
>> Otherwise
>> it fails.
>>
>> $File = addslashes(getc wd() . "\Address.txt") ;
>> $SQL = "Load Data InFile \"" . $File . "\" into table addresses";
>> $result = mysql_query($SQ L) or die("Failed to load data");
>>
>> Both users were created by root from a cmd prompt:
>>
>> grant all on * to user@localhost identified by 'mypw' with grant options;
>>
>> What is wrong so that the created users can execute the above code?
>>
>> Thanks.
>>
>> Bruce[/color]
>
> Which db was current when you did the grant? If it was the mysql db,
> then you probably just granted access to that rather than the ones
> you intended to.
> AFAIK (hopefully someone corrects if I'm wrong) a * on its own grants
> only to the current db. You should change it to *.* if you want to grant
> to all dbs, or use dbname.* for a specific db.
> As an aside your privs are very broad here - I guess for test it doesn't
> matter, but careful when you move to production.
>
> --
> Dave <dave@REMOVEbun dook.com>
> (Remove REMOVE for email address)
>[/color]

Okay. I'll try to grant with *.*. I'll also experiment with "Use"ing
different databases before root issues the grant. Thanks.

Question: What are "privs "? I don't understand your statement "As an aside
your privs are very broad here - I guess for test it doesn't matter, but
careful when you move to production." I'd appreciate if you could expand on
your comment. Thanks..

Bruce

[Dave]

Re: Load Data InFile fails

On Mon, 01 Aug 2005 23:32:29 -0400, Bruce A. Julseth decided we needed to
hear:
<snip>[color=blue]
> Okay. I'll try to grant with *.*. I'll also experiment with "Use"ing
> different databases before root issues the grant. Thanks.[/color]

hopefully that does the trick[color=blue]
>
> Question: What are "privs "? I don't understand your statement "As an aside
> your privs are very broad here - I guess for test it doesn't matter, but
> careful when you move to production." I'd appreciate if you could expand on
> your comment. Thanks..
>
> Bruce[/color]

privs = privileges.

You are doing a grant all which grants every single privilege to
the user - plus you add the grant option which allows the user to
give those privs to other users.
Its (generally speaking) a bad practice to do this particularly
if the database is available directly over a network.
A better way is to grant to the user only those privs required
to perform the required task e.g. a user may only need to do
select and update (not delete, insert etc) so granting just those
two minimises risk should the worst happen and a malicious person
gets hold of the user/pass.

HTH

--
Dave <dave@REMOVEbun dook.com>
(Remove REMOVE for email address)

[Bruce A. Julseth]

Re: Load Data InFile fails


"Dave" <dave@REMOVEbun dook.com> wrote in message
news:pan.2005.0 8.01.21.55.18.1 08530@REMOVEbun dook.com...[color=blue]
> On Mon, 01 Aug 2005 21:38:41 -0400, Bruce A. Julseth decided we needed to
> hear:
>[color=green]
>> I have three users with access to my test MySQL databases, root plus two
>> others. The following PHP code only works when the user is root.
>> Otherwise
>> it fails.
>>
>> $File = addslashes(getc wd() . "\Address.txt") ;
>> $SQL = "Load Data InFile \"" . $File . "\" into table addresses";
>> $result = mysql_query($SQ L) or die("Failed to load data");
>>
>> Both users were created by root from a cmd prompt:
>>
>> grant all on * to user@localhost identified by 'mypw' with grant options;
>>
>> What is wrong so that the created users can execute the above code?
>>
>> Thanks.
>>
>> Bruce[/color]
>
> Which db was current when you did the grant? If it was the mysql db,
> then you probably just granted access to that rather than the ones
> you intended to.
> AFAIK (hopefully someone corrects if I'm wrong) a * on its own grants
> only to the current db. You should change it to *.* if you want to grant
> to all dbs, or use dbname.* for a specific db.
> As an aside your privs are very broad here - I guess for test it doesn't
> matter, but careful when you move to production.
>
> --
> Dave <dave@REMOVEbun dook.com>
> (Remove REMOVE for email address)
>[/color]

Dave:

*.* in the grant seems to have fixed the problem. Thanks...

Bruce