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: GRANT and ticks or no ticks...

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

2009/5/27 Daevid Vincent

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


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.nanni@gmail.com
>
>

--000e0cd297764b9b8f046ae5ceb5--

Create function ignore deps

I want to check all my functions and procs into my svn as individual sql =
files. When I use these to create my db, the person doing this may not =
realize the correct order to run these files and not have dependency =
challenges... How can I have procs that depend on functions, or vice =
versa, get successfully created without regard to correct order?
Thanks
Bryancan

--
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: Create function ignore deps

On Wed, May 27, 2009 at 11:24 AM, Cantwell, Bryan
wrote:
> I want to check all my functions and procs into my svn as individual sql =
files. When I use these to create my db, the person doing this may not real=
ize the correct order to run these files and not have dependency challenges=
.... How can I have procs that depend on functions, or vice versa, get succe=
ssfully created without regard to correct order?
> Thanks
> Bryancan
>

I prefixed the filenames of the various discrete files with numeric
prefixes like so

10-create-customer.sql
20-finalize-transaction.sql
....

and then wrote a shell script to fire them off in sort-order. I
deliberately used the numbering convention from line-numbered basic to
allow me to inject intervening files without having to renumber the
set.


--=20
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.

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