List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:January 16 2012 9:01am
Subject:Re: delete all hosts using a wildcard
View as plain text  
I wouldn't recommend 'playing' with the grant tables instead use the
designated commands.
Anyway keep in mind that if you modify the grant tables manually you have
to force the reload of the privileges in memory by using the 'flush
privileges'.
Not needed if you use GRANT/REVOKE etc.
Cheers
Claudio
On Jan 15, 2012 1:28 AM, "Tim Dunphy" <bluethundr@stripped> wrote:

> Hello again list,
>
>  Thanks for pointing out where I was making my mistake. I just needed to
> select the right field. And this is just a test environment so getting rid
> of those users won't have any meaningful impact. Also previewing what you
> will be deleting by using a select is great advice I intend to use.
>
> Best
> tim
>
> ----- Original Message -----
> From: "Paul DuBois" <paul.dubois@stripped>
> To: "Tim Dunphy" <bluethundr@stripped>
> Cc: mysql@stripped
> Sent: Saturday, January 14, 2012 6:46:38 PM
> Subject: Re: delete all hosts using a wildcard
>
>
> On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote:
>
> > hello list,
> >
> > I have a number of hosts that I would like to delete using a wildcard
> (%) symbol.
> >
> >  Here is the query I am using:
> >
> >  mysql> delete from mysql.user where user='%.summitnjhome.com';
>
> Couple of things:
>
> * You want to compare your pattern to the host column, not user.
> * To match the pattern, use LIKE, not =.
>
> So: WHERE host LIKE '%.summitnjhome.com'
>
> But to see what rows your DELETE will affect, try this first:
>
> SELECT * FROM mysql.user WHERE host LIKE '%.summitnjhome.com';
>
> Something else to consider: What if these accounts have privileges
> defined in the other grant tables, such as database-level privileges
> in the db table?
>
>
>
> http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like
> http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html
>
> > Query OK, 0 rows affected (0.00 sec)
> >
> > And I am attempting to delete all the hosts at the domain '
> summitnjhome.com'...
> >
> > But as you can see I am unsuccessful:
> >
> > mysql> select user,host from mysql.user;
> > +----------+-----------------------------+
> > | user     | host                        |
> > +----------+-----------------------------+
> > | root     | 127.0.0.1                   |
> > | repl     | virtcent10.summitnjhome.com |
> > | admin    | virtcent11.summitnjhome.com |
> > | repl     | virtcent19.summitnjhome.com |
> > | repl     | virtcent23.summitnjhome.com |
> > | repl     | virtcent30.summitnjhome.com |
> > +----------+-----------------------------+
> >
> >
> > I know I can delete them individually and this is what I am going to do.
> But I would like to use this as a learning opportunity to help me
> understand how the wildcard works.
> >
> > Thanks in advance..
> >
> > Best regards,
> > Tim
>
> --
> Paul DuBois
> Oracle Corporation / MySQL Documentation Team
> Madison, Wisconsin, USA
> www.mysql.com
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>
>

Thread
delete all hosts using a wildcardTim Dunphy14 Jan
  • Re: delete all hosts using a wildcardPaul DuBois14 Jan
    • Re: delete all hosts using a wildcardTim Dunphy15 Jan
      • Re: delete all hosts using a wildcardClaudio Nanni16 Jan
Re: delete all hosts using a wildcardJan Steinman16 Jan
  • Re: delete all hosts using a wildcardGovinda16 Jan