This may sound like a stupid question, but I have to ask. I've been
running a script that goes like the following.
use ecommerce;
create temporary table customer_tmp as select * from customer limit 0;
load data infile '/' into table
\customer_tmp;
at which point the script fails for permssion reasons. It seems that this
user can create a temporary table, but not load data into it?
What did I miss on permissions to allow this to work?
Thanks
Curtis

possibly that the mysql user on the box does not have access to the
data file in question. Can you post the error messages you get?

Dan

8/15/06, Curtis Maurand <curtis (AT) maurand (DOT) comwrote:
This may sound like a stupid question, but I have to ask. I've been
running a script that goes like the following.
>
>
>
use ecommerce;
create temporary table customer_tmp as select * from customer limit 0;
load data infile '/' into table
\customer_tmp;

at which point the script fails for permssion reasons. It seems that this
user can create a temporary table, but not load data into it?

What did I miss on permissions to allow this to work?

Thanks
Curtis
--

here is what I get. I'm logged in as the user admin (really a regular user.)

mysqlcreate temporary table customer_tmp as select * from customer limit 0;
Query K, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysqlload data infile '/' into table
customer_tmp;
ERRR 1045 (28000): Access denied for user 'admin'@'localhost' (using
password: YES)

Dan Buettner wrote:
possibly that the mysql user on the box does not have access to the
data file in question. Can you post the error messages you get?

Dan
--
8/15/06, Curtis Maurand <curtis (AT) maurand (DOT) comwrote:
>This may sound like a stupid question, but I have to ask. I've been
>running a script that goes like the following.
>>
>>
>>
>use ecommerce;
>create temporary table customer_tmp as select * from customer limit 0;
>load data infile '/' into table
>\customer_tmp;
>>
>at which point the script fails for permssion reasons. It seems that
>this
>user can create a temporary table, but not load data into it?
>>
>What did I miss on permissions to allow this to work?
>>
>Thanks
>Curtis
>>
>>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>@gmail.com
>>
>>
>

Curtis, you might need to make sure 'admin'@'localhost' has 'FILE'
privileges in the proper database, for load data infile. Note that
'admin'@'%' is not the same as 'admin'@'localhost'

See

Also here is a comment from the same page:
QUTE
Posted by Cristian Gafton on July 17 2006 6:28pm[Delete] [Edit]
Please note that the current MySQL priviledge model does not support
creating a read-only user for an application that needs to work with
temporary tables. In other words, the 'SELECT,CREATE TEMPRARY TABLES'
privileges are not sufficient to let an application work with
temporary tables against a read-only set of tables. MySQL also
requires INSERT, DELETE and UPDATE grants to allow temporary tables to
be used. Since the temporary tables are not 'defined' at the time of
the grant, one would have no choice but to grant INSERT,DELETE,UPDATE
on db.*, which negates the possibility of a read-only user.

It would be nice if MySQL would have a more generic 'TEMPRARY TABLES'
permission that would allow one to create, insert, delete from and
drop temporary tables without having to give up insert/update/delete
privileges on the real tables

/QUTE

Dan

8/15/06, Curtis Maurand <curtis (AT) maurand (DOT) comwrote:

here is what I get. I'm logged in as the user admin (really a regular user.)

mysqlcreate temporary table customer_tmp as select * from customer limit 0;
Query K, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysqlload data infile '/' into table
customer_tmp;
ERRR 1045 (28000): Access denied for user 'admin'@'localhost' (using
password: YES)
>
>
>
>
Dan Buettner wrote:
possibly that the mysql user on the box does not have access to the
data file in question. Can you post the error messages you get?

Dan
--
8/15/06, Curtis Maurand <curtis (AT) maurand (DOT) comwrote:
>This may sound like a stupid question, but I have to ask. I've been
>running a script that goes like the following.
>>
>>
>>
>use ecommerce;
>create temporary table customer_tmp as select * from customer limit 0;
>load data infile '/' into table
>\customer_tmp;
>>
>at which point the script fails for permssion reasons. It seems that
>this
>user can create a temporary table, but not load data into it?
>>
>What did I miss on permissions to allow this to work?
>>
>Thanks
>Curtis
>>
>>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>@gmail.com
>>
>>
>

When I created the user, I specified:

grant all on ecommerce.* to admin@'localhost' identified by '<password>';

Doesn't that cover the file privilege? I noticed the "navicat" commercial
product doesn't list that privilege specifically. the mysql-administrator
just locks up when I go to manage users. i've been working it from the
command line.

Curtis

Dan Buettner wrote:
Curtis, you might need to make sure 'admin'@'localhost' has 'FILE'
privileges in the proper database, for load data infile. Note that
'admin'@'%' is not the same as 'admin'@'localhost'

See
--
Also here is a comment from the same page:
QUTE
Posted by Cristian Gafton on July 17 2006 6:28pm[Delete] [Edit]
Please note that the current MySQL priviledge model does not support
creating a read-only user for an application that needs to work with
temporary tables. In other words, the 'SELECT,CREATE TEMPRARY TABLES'
privileges are not sufficient to let an application work with
temporary tables against a read-only set of tables. MySQL also
requires INSERT, DELETE and UPDATE grants to allow temporary tables to
be used. Since the temporary tables are not 'defined' at the time of
the grant, one would have no choice but to grant INSERT,DELETE,UPDATE
on db.*, which negates the possibility of a read-only user.

It would be nice if MySQL would have a more generic 'TEMPRARY TABLES'
permission that would allow one to create, insert, delete from and
drop temporary tables without having to give up insert/update/delete
privileges on the real tables

/QUTE

Dan
>
>
>
8/15/06, Curtis Maurand <curtis (AT) maurand (DOT) comwrote:
>>
>here is what I get. I'm logged in as the user admin (really a regular
>user.)
>>
>mysqlcreate temporary table customer_tmp as select * from customer
>limit 0;
>Query K, 0 rows affected (0.00 sec)
>Records: 0 Duplicates: 0 Warnings: 0
>>
>mysqlload data infile '/' into table
>customer_tmp;
>ERRR 1045 (28000): Access denied for user 'admin'@'localhost' (using
>password: YES)
>>
>>
>>
>>
>Dan Buettner wrote:
>possibly that the mysql user on the box does not have access to the
>data file in question. Can you post the error messages you get?
>>
>Dan
>>
>>
>8/15/06, Curtis Maurand <curtis (AT) maurand (DOT) comwrote:
>>This may sound like a stupid question, but I have to ask. I've been
>>running a script that goes like the following.
>>>
>>>
>>>
>>use ecommerce;
>>create temporary table customer_tmp as select * from customer limit
>0;
>>load data infile '/' into table
>>\customer_tmp;
>>>
>>at which point the script fails for permssion reasons. It seems that
>>this
>>user can create a temporary table, but not load data into it?
>>>
>>What did I miss on permissions to allow this to work?
>>>
>>Thanks
>>Curtis
>>>
>>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:
>>@gmail.com
>>>
>>>
>>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>@maurand.com
>>
>>
>>
>>
>

8/15/06, Dan Buettner <drbuettner (AT) gmail (DOT) comwrote:
It would be nice if MySQL would have a more generic 'TEMPRARY TABLES'
permission that would allow one to create, insert, delete from and
drop temporary tables without having to give up insert/update/delete
privileges on the real tables

Can't you "blacklist" the real tables in turn? I.e. assign create,
insert, update rights to the new user and then assign table specific
rights to him as well, revoking these rights for the individual real
tables?

That's quite a bit more work and has to be maintained everytime you
add a new real table (so it's no real replacement for your suggestion
above) but it seems to be a quite secure workaround.

Fred

You were right. Its a global privilege not a table one. I granted it at
a global level. it can't be granted at the databae level

Dan Buettner wrote:
Curtis, you might need to make sure 'admin'@'localhost' has 'FILE'
privileges in the proper database, for load data infile. Note that
'admin'@'%' is not the same as 'admin'@'localhost'

See
--
Also here is a comment from the same page:
QUTE
Posted by Cristian Gafton on July 17 2006 6:28pm[Delete] [Edit]
Please note that the current MySQL priviledge model does not support
creating a read-only user for an application that needs to work with
temporary tables. In other words, the 'SELECT,CREATE TEMPRARY TABLES'
privileges are not sufficient to let an application work with
temporary tables against a read-only set of tables. MySQL also
requires INSERT, DELETE and UPDATE grants to allow temporary tables to
be used. Since the temporary tables are not 'defined' at the time of
the grant, one would have no choice but to grant INSERT,DELETE,UPDATE
on db.*, which negates the possibility of a read-only user.

It would be nice if MySQL would have a more generic 'TEMPRARY TABLES'
permission that would allow one to create, insert, delete from and
drop temporary tables without having to give up insert/update/delete
privileges on the real tables

/QUTE

Dan
>
>
>
8/15/06, Curtis Maurand <curtis (AT) maurand (DOT) comwrote:
>>
>here is what I get. I'm logged in as the user admin (really a regular
>user.)
>>
>mysqlcreate temporary table customer_tmp as select * from customer
>limit 0;
>Query K, 0 rows affected (0.00 sec)
>Records: 0 Duplicates: 0 Warnings: 0
>>
>mysqlload data infile '/' into table
>customer_tmp;
>ERRR 1045 (28000): Access denied for user 'admin'@'localhost' (using
>password: YES)
>>
>>
>>
>>
>Dan Buettner wrote:
>possibly that the mysql user on the box does not have access to the
>data file in question. Can you post the error messages you get?
>>
>Dan
>>
>>
>8/15/06, Curtis Maurand <curtis (AT) maurand (DOT) comwrote:
>>This may sound like a stupid question, but I have to ask. I've been
>>running a script that goes like the following.
>>>
>>>
>>>
>>use ecommerce;
>>create temporary table customer_tmp as select * from customer limit
>0;
>>load data infile '/' into table
>>\customer_tmp;
>>>
>>at which point the script fails for permssion reasons. It seems that
>>this
>>user can create a temporary table, but not load data into it?
>>>
>>What did I miss on permissions to allow this to work?
>>>
>>Thanks
>>Curtis
>>>
>>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:
>>@gmail.com
>>>
>>>
>>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>@maurand.com
>>
>>
>>
>>
>