Re: GRANT question

[Paul DuBois]

At 14:30 -0500 2/21/04, Rhino wrote:
More followups below....



 > >>  > 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.
You should still be worried.  FILE is a dangerous privilege.

http://www.mysql.com/doc/en/Privileges_provided.html

See the mention of FILE near the end.


 > >>  >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.)
Then you're not connecting to the MySQL server remotely.  You're using
SSH to connect to the MySQL server *host* remotely, and then from that
host making a local connection to the MySQL server running on that host.
So in this case, all you do need is to grant privileges to a
[EMAIL PROTECTED] account.
 > >
 >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.
See above.  As far as the MySQL server is concerned, you are accessing
it from the same host were it is running.  It doesn't know that you're
"really" on some other machine.
Yes, it's easy to get confused by this kind of stuff. :-)


 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.
Just use localhost.

To connect using the Unix socket file, connect to localhost (the default).
To connect over TCP/IP, connect to 127.0.0.1.
localhost should match them both.

--
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]