List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:May 27 2009 2:39pm
Subject:Re: GRANT and ticks or no ticks...
View as plain text  
2009/5/27 Daevid Vincent <daevid@stripped>

> 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';

Backticks are only used, and useful , when you have 'dangerous' characters
or reserved names in the identifiers names.
I don't think single quotes even work.
And also you don't need to use the PASSWORD keyword in the grant statement.

> All the examples seem to show no quotes:
> 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"?

The clean way:

to know which accounts you have issue:

     select user,host from mysql.user

then SHOW grants for 'user'@'host' for all of them

then revoke each and every grant. (not the GRANT USAGE, not needed, this is
actually just the user account)

if you just want to remove the users as well then use DROP 'user'@'host'

Dirty way:

do it the clean way!

delete from user and host table all the users but the 'root'



> --
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

GRANT and ticks or no ticks...Daevid Vincent27 May
  • Re: GRANT and ticks or no ticks...Claudio Nanni27 May
    • Create function ignore depsBryan Cantwell27 May
      • Re: Create function ignore depsMichael Dykman27 May