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. > > >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. 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 > > > >We are running MySQL 4.0.11a. gamma. > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net http://www.ensita.net/ > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net > <___/ www.mysql.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
