At 13:09 -0500 2/21/04, Rhino wrote:
Followup questions interspersed below....
----- Original Message -----
From: "Egor Egorov" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, February 21, 2004 12:29 PM
Subject: Re: GRANT question
"Rhino" <[EMAIL PROTECTED]> wrote:
> I am new to most aspects of MySQL administration so I was wondering if
someone can help me figure out what GRANTs I need for a particular
situation.
>
> I have a user who needs to be able to run some MySQL scripts that create
and load tables in a database named NFL. He is accessing MySQL remotely from
a client on a Windows machine. Our server is running in Linux Mandrake 9.1;
the client is Windows 98SE.
>
> I've run the following grants for him:
> grant all on NFL.* to [EMAIL PROTECTED] indentified by 'xxxxx';
> grant all on NFL.* to [EMAIL PROTECTED] identified by 'xxxxx';
> grant file on NFL.* to [EMAIL PROTECTED] identified by 'xxxxx';
> grant file on NFL.* to [EMAIL PROTECTED] identified by 'xxxxx';
>
> When I try signing on as him from my remote Windows client, he can
connect to MySQL and get to the mysql prompt. He can run the script which
creates and loads the tables. However, he gets "Access Denied" on the Load
Data Infile statements when the script executes.
>
> 1. Why is this happening? The Load Data article says he needs the File
privilege and I've given it to him. He also has all privileges on the NFL
database. What more do I need to do for him?
FILE is a global level privilege.
You should grant:
GRANT FILE ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'xxxxx';
So, am I correct in understanding that I can't give the FILE privilege for a
single database or table? That means my user can load *any* table in *any*
database when I only want him to be able to load the tables in one database,
which is more access than I wanted to give him. I guess I'll either have to
trust him or run the scripts myself.
No. You must grant FILE on the global level. That means you can read or
write files. It doesn't imply anything about which tables you can access.
If you have no access to a table, you can't read a file into it, for example.
>
>2. Are the privileges given to [EMAIL PROTECTED] redundant with the
privileges given to [EMAIL PROTECTED] They *look* redundant to me; I suspect
I've misinterpreted something I read in the manual. Can I get by with giving
him just the [EMAIL PROTECTED] privileges? Do I need to give him anything else
to account for the fact that he is coming in from a remote client?
localhost on Unix system means that you are using Unix socket connection.
127.0.0.1 means that you are using TCP/IP connection.
But you can't use these accounts to connect to the MySQL server remotely.
Check with CURRENT_USER() function username and hostname that current
connection was authenticated as.
I'm confused by your answer. The privileges he currently has *do* allow him
to connect to the MySQL server remotely. (We are using SSH as our Windows
client.)
I ran the query "select current_user() from NFL.Teams" and got the following
result while signed on as my user: [EMAIL PROTECTED] Does this mean that I
should revoke the privileges given to [EMAIL PROTECTED] Honestly, I'm not
sure whether I should be using Unix sockets or TCP/IP; I'm not sure I
understand the implications of using either one to MySQL.
If CURRENT_USER() is returning [EMAIL PROTECTED], it means you're not actually
connecting from a remote server, you are connecting to the MySQL server from
the same host where the server is running.
The host that you specify in the GRANT statement is not the host that
the MySQL server runs on. It is the client host *from which* you plan
to connect to the server.
By the way, I change the File privilege as you suggested - and nothing
else - and I can now execute the script successfully, including the LOAD
DATA commands, while logged on as 'brian'.
Rhino
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
