List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:May 27 2009 12:05am
Subject:FW: GRANT and ticks or no ticks...
View as plain text  
Now I'm really confused.

I just did this:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'10.10.10.%';
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'10.10.10.%' IDENTIFIED BY PASSWORD
'secret';
 
and then I get this:

SHOW GRANTS FOR 'user'@'10.10.10.%';
+---------------------------------------------------------------------------
+
| Grants for user@stripped.%
|
+---------------------------------------------------------------------------
+
| GRANT USAGE ON *.* TO 'user'@'10.10.10.%' IDENTIFIED BY PASSWORD 'secret'
|
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'user'@'10.10.10.%'
|
+---------------------------------------------------------------------------
+

So why mySQL is putting back ticks in there even though I didn't, 

and more importantly why doesn't the second line say:

GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'10.10.10.%' IDENTIFIED BY PASSWORD
'secret';

like the first line says and like I specifically said in my grant statement
up above??!

-----Original Message-----
From: Daevid Vincent [mailto:daevid@stripped] 
Sent: Tuesday, May 26, 2009 4:49 PM
To: 'mysql@stripped'
Subject: GRANT and ticks or no ticks...


Wondering which of these will work or not?

(no quotes)
GRANT ALL PRIVILEGES ON mydb.mytable TO 'user'@'10.10.10.%' IDENTIFIED BY
PASSWORD 'secret';

(backticks)
GRANT ALL PRIVILEGES ON `mydb`.`mytable` TO 'user'@'10.10.10.%' IDENTIFIED
BY PASSWORD 'secret';

(single quotes)
GRANT ALL PRIVILEGES ON 'mydb'.'mytable' TO 'user'@'10.10.10.%' IDENTIFIED
BY PASSWORD 'secret';

All the examples seem to show no quotes:
http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html

But our grant table has a mixture of all three (legacy inheritance I'm
trying to clean up)

mysql -uroot -p -Bse "SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'',
host, '\';') FROM mysql.user" | mysql -uroot -p -Bs | sed 's/$/;/g'

Also, is there a way to just "wipe" all the grants so that I can add them
one at a time and get rid of the cruft? Obviously this has a risk of blowing
away the "root" user you're adding grants with. Does this also mean that if
I ungrant my current user, does that change take effect immediatly and I
won't be able to grant anymore? Or as long as I stay logged into the mysql
shell I am "safe"?

Thread
FW: GRANT and ticks or no ticks...Daevid Vincent27 May
  • Re: FW: GRANT and ticks or no ticks...Walter Heck - OlinData.com27 May
  • Re: FW: GRANT and ticks or no ticks...Johan De Meersman27 May