FW: GRANT and ticks or no ticks...

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@10.10.10.%
|
+----------------------------------------------------------- ----------------
+
| 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@daevid.com]
Sent: Tuesday, May 26, 2009 4:49 PM
To: 'mysql@lists.mysql.com'
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.h tml

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: FW: GRANT and ticks or no ticks...

Start the server with --skip-grants-table. That will disable logins.
Then do "delete from mysql.user" and restart :)

Walter

On Tue, May 26, 2009 at 6:05 PM, Daevid Vincent wrote:
> 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 PASSW=
ORD
> 'secret';
>
> and then I get this:
>
> SHOW GRANTS FOR 'user'@'10.10.10.%';
> +----------------------------------------------------------- -------------=
---
> +
> | Grants for user@10.10.10.%
> |
> +----------------------------------------------------------- -------------=
---
> +
> | GRANT USAGE ON *.* TO 'user'@'10.10.10.%' IDENTIFIED BY PASSWORD 'secre=
t'
> |
> | 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 PASSW=
ORD
> 'secret';
>
> like the first line says and like I specifically said in my grant stateme=
nt
> up above??!
>
> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@daevid.com]
> Sent: Tuesday, May 26, 2009 4:49 PM
> To: 'mysql@lists.mysql.com'
> 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.%' IDENTIFIE=
D
> BY PASSWORD 'secret';
>
> (single quotes)
> GRANT ALL PRIVILEGES ON 'mydb'.'mytable' TO 'user'@'10.10.10.%' IDENTIFIE=
D
> BY PASSWORD 'secret';
>
> All the examples seem to show no quotes:
> http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.h tml
>
> 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 blow=
ing
> 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 mysq=
l
> shell I am "safe"?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=3Dlists@o=
lindata.com
>
>



--=20
Walter Heck

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: FW: GRANT and ticks or no ticks...

--001485f85ef62f3cba046ade856b
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Wed, May 27, 2009 at 2:05 AM, Daevid Vincent wrote:

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


Because it doesn't save your original statements, but recreates an
appropriate set from the grant tables.


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


Because it's easier to get the password and the connect privilege out of the
way in the first line, and then methodically work down through all the other
privileges. The statements still do exactly what you asked for, they're just
easier to generate in that way.


--
Celsius is based on water temperature.
Fahrenheit is based on alcohol temperature.
Ergo, Fahrenheit is better than Celsius. QED.

--001485f85ef62f3cba046ade856b--