More followups below.... ----- Original Message ----- From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, February 21, 2004 1:25 PM Subject: Re: GRANT question
> 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. > Okay, that's a relief; I didn't want to give him access to other databases. He doesn't have many computer skills yet so there's a very good chance that he could mess up the other databases if he decided to give it a try. Now that I know he can't access the other databases, I'm no longer worried. > > > > >> >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. > Are you saying that connecting via SSH is effectively the same as sitting in the same room as the server and accessing its command line directly? My user and I are not even in the same city as the server so I thought that inevitably meant that a program like SSH had to be treated as a client, not as if it were the host itself. Forgive me if this is a stupid question but my knowledge of networking is sketchy at best. > 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. > If the use of SSH means that I am directly accessing the server in a *local* fashion, rather than as a client, does this mean I can eliminate the GRANTs to 127.0.0.1 or localhost? If yes, which one would be the sensible one to eliminate? I don't know the pros and cons of using Unix sockets vs. TCP/IP. > > > >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]
