Re: Mysql permissions

bruce bales wrote:
> Steven Saner wrote:
> 
> 
>>In order to do this you must have "FILE" privileges. Try issuing
>>something like:
>>
>>GRANT FILE ON table_name TO username;

FILE just covers the SELECT INTO OUTFILE and LOAD DATA INFILE privileges;
i.e., those dealing with external files. See:

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

>>Steve
>>
> 
> I signed on as root and tried to do that.  Got "Illegal grant command." 
>  Tried the same as Linux ROOT and got the same result.
> ---------------------------------start-----------------------------
> [bruce@bertha temp]$ mysql -u root -p
> Enter password:
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 33 to server version: 3.23.56-log
> 
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> 
> mysql> use class50;
> Database changed
> mysql> grant file on class to bruce;
 > ERROR 1144: Illegal GRANT/REVOKE command. Please consult the manual
 > which privileges can be used.
 > --------------------------------end-----------------------------------

This should probably be:

   mysql> grant all on class50.* to bruce@localhost identified by 'PWD';
   mysql> flush privileges;

This grant would allow you to create tables in the class50 database, add
records, delete records, select, etc. Instead of ALL you can get pickier
on the privileges, but if you just want to learn on a private database,
ALL is the best place to start.

The need to flush privileges is also counterintuitive -- can make the
procedure downright mysterious, in fact.

After you've done this, try to access the database using:

   $ mysql --user=bruce --password='PWD' class50

Or you can use -p and it will prompt you for the password. If you don't
explicitly specify the user there seems to be some weirdness in how mysql
guesses who you are. (E.g., if I su - to a user, then run mysql, it still
tries to log in using my pre-su user name; probably has something to do
with the owner of the ptty.)

Some general doc on users and privileges:

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

There are many things about this I don't fully understand -- e.g., when
pieces of grant statements should or should not be quoted, what % means,
and why it's so hard to get things working without passwords when the
default root works fine without a password. Still, you can get around
most of the password hassle by creating a ~/my.cnf file with the lines:

   [client]
   password=PWD

Such a file should only be user-readable.

> Then I tried:
> 
> mysql> grant file on * to bruce;
> Query OK, 0 rows affected (0.01 sec)
> 
> But when I tried to use the outfile I got the  "Access denied for bruce."

-- 
/*
  *  Tom Hull * thull2(cox.net) * http://www.tomhull.com/
  */

-- This is the [email protected] list.  To unsubscribe,
visit http://www.complete.org/cgi-bin/listargate-aclug.cgi