Re: GRANT and ticks or no ticks...
2009/5/27 Daevid Vincent dae...@daevid.com
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:
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?
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'
Cheers
Claudio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 u...@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:dae...@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.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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.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 dae...@daevid.com 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 PASSWORD
'secret';
and then I get this:
SHOW GRANTS FOR 'user'@'10.10.10.%';
+---
+
| Grants for u...@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:dae...@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.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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com
--
Walter Heck
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: FW: GRANT and ticks or no ticks...
On Wed, May 27, 2009 at 2:05 AM, Daevid Vincent dae...@daevid.com 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.
