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"?
