LOAD FILE access problem

[CPD]

From PHP, doing a DROP TABLE and a CREATE TABLE is successful, but doing a
LOAD FILE keeps failing, with the error "Access denied for user
'db_user'@'loca lhost' (using password: YES)". The DROP/CREATE code and the
LOAD FILE code are in separate .php files which I am running from my
browser, but both commands are executed with mysql_query(). Don't know what
I'm doing wrong, so I would appreciate any help with this.

Both files run the following code first, which works fine:
=============== =============== ====
// -----------------------------------------------------------------------
// Connect to MySQL server
// -----------------------------------------------------------------------
$link = mysql_connect($ db_host, $db_user, $db_password);
if (! $link) {
die("Could not connect to {$db_user}/{$db_password} on host
{$db_host}:\n" .
mysql_error() . end_body_html() );
exit(1);
}
else {
echo "Connected to MySQL on host {$db_host}.<p>\ n";
}

// -----------------------------------------------------------------------
// Drop and create PHC_DB database
// -----------------------------------------------------------------------
$drop_db_query = "DROP DATABASE IF EXISTS {$db_name}";

if (run_mysql_quer y($drop_db_quer y));

$create_db_quer y = "CREATE DATABASE IF NOT EXISTS {$db_name}";

if (run_mysql_quer y($create_db_qu ery));

// -----------------------------------------------------------------------
// Select database
// -----------------------------------------------------------------------
$db_selected = mysql_select_db ($db_name, $link);
if (! $db_selected) {
die("Could not select {$db_name} database." .
mysql_error() . end_body_html() );
exit(1);
}
else {
echo "Selected {$db_name} database.<p>\n" ;
}
=============== =============== ====

Here is the section of DROP/CREATE code, which also works fine:
=============== =============== ====
// -----------------------------------------------------------------------
// Drop and create REF_USER_LEVELS table
// -----------------------------------------------------------------------
$drop_table_que ry = "DROP TABLE IF EXISTS ref_user_levels ";

run_mysql_query ($drop_table_qu ery);

$create_table_q uery = <<<EOT
CREATE TABLE ref_user_levels (
user_level VARCHAR(10) NOT NULL,
actv_ind ENUM (
'A', -- active
'I' -- inactive
) NOT NULL,
max_counties INT(5),
description VARCHAR(100)
);
EOT;

run_mysql_query ($create_table_ query);
=============== =============== ====

Now here is the LOAD FILE code, which fails with
"Error: Access denied for user 'db_user'@'loca lhost' (using password: YES)
LOAD DATA INFILE '/mysubdir/ref_user_levels .txt' INTO TABLE
ref_user_levels ",
where db_user is the value of $db_user, and localhost is the value of
$db_host, as with the DROP/CREATE above.
$dat_file_path is based on DOCUMENT_ROOT, e.g., /mysubdir/
=============== =============== ====
// -----------------------------------------------------------------------
// Populate REF_USER_LEVELS table
// -----------------------------------------------------------------------
$populate_table _query = "LOAD DATA INFILE
'{$dat_file_pat h}ref_user_leve ls.txt' INTO TABLE ref_user_levels ";

run_mysql_query ($populate_tabl e_query);
=============== =============== ====

Here are the support functions called:
=============== =============== ====
function end_body_html() {
echo "\n</body>\n</html>";
return;
}
function run_mysql_query ($query) {
if (mysql_query($q uery)) {
echo "<span style='font-weight:bold;col or:green;'>Succ ess:</span>
<pre>" . $query . "</pre>\n<p>\n";
} else {
echo "<span style='font-weight:bold;col or:red;'>Error: </span> " .
mysql_error() . "<br>\n<pre>$qu ery</pre><br>\n" . end_body_html() ;
exit(1);
}
}
=============== =============== ====

Thanks in advance!

[Bill Karwin]

Re: LOAD FILE access problem

"CPD" <nospam@email.p ls> wrote in message
news:_CfMf.1472 1$rL5.2660@news svr27.news.prod igy.net...[color=blue]
> From PHP, doing a DROP TABLE and a CREATE TABLE is successful, but doing a
> LOAD FILE keeps failing, with the error "Access denied for user
> 'db_user'@'loca lhost' (using password: YES)".[/color]

Are you aware that there are distinct SQL privileges for DROP, CREATE, and
LOAD DATA INFILE?

For DROP/CREATE, your user must have the DROP and CREATE privileges,
respectively.

For LOAD DATA INFILE, your user must have the FILE privilege.

Regards,
Bill K.

[CPD]

Re: LOAD FILE access problem

I did a SHOW GRANTS and ALL PRIVILEGES have been granted. What else can I be
missing? Thanks again!

"Bill Karwin" <bill@karwin.co m> wrote in message
news:dtt82s010c e@enews4.newsgu y.com...[color=blue]
> "CPD" <nospam@email.p ls> wrote in message
> news:_CfMf.1472 1$rL5.2660@news svr27.news.prod igy.net...[color=green]
>> From PHP, doing a DROP TABLE and a CREATE TABLE is successful, but doing
>> a
>> LOAD FILE keeps failing, with the error "Access denied for user
>> 'db_user'@'loca lhost' (using password: YES)".[/color]
>
> Are you aware that there are distinct SQL privileges for DROP, CREATE, and
> LOAD DATA INFILE?
>
> For DROP/CREATE, your user must have the DROP and CREATE privileges,
> respectively.
>
> For LOAD DATA INFILE, your user must have the FILE privilege.
>
> Regards,
> Bill K.
>[/color]

[Bill Karwin]

Re: LOAD FILE access problem

"CPD" <nospam@email.p ls> wrote in message
news:%9xMf.6133 5$PL5.15779@new ssvr11.news.pro digy.com...[color=blue]
>I did a SHOW GRANTS and ALL PRIVILEGES have been granted. What else can I
>be missing? Thanks again![/color]

What are the permissions on the file you're trying to load? Can the uid of
your mysqld process read it?

Regards,
Bill K.

[CPD]

Re: LOAD FILE access problem

Perms are 644 on the files. The mysqld process should be able to open them,
right? Thanks for following this thread, Bill.

CPD

"Bill Karwin" <bill@karwin.co m> wrote in message
news:dtvdeo02tm 3@enews1.newsgu y.com...[color=blue]
> "CPD" <nospam@email.p ls> wrote in message
> news:%9xMf.6133 5$PL5.15779@new ssvr11.news.pro digy.com...[color=green]
>>I did a SHOW GRANTS and ALL PRIVILEGES have been granted. What else can I
>>be missing? Thanks again![/color]
>
> What are the permissions on the file you're trying to load? Can the uid
> of your mysqld process read it?
>
> Regards,
> Bill K.
>[/color]