Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Tim Watts Newsgroups: comp.databases.mysql Subject: Re: MySQL user management vs PostgreSQL Followup-To: comp.databases.mysql Date: Mon, 18 Apr 2011 16:42:53 +0100 Organization: A noiseless patient Spider Lines: 56 Message-ID: References: <5drr78-heq.ln1@squidward.dionic.net> <7agt78-8d4.ln1@xl.homelinux.org> Mime-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 7Bit Injection-Info: mx01.eternal-september.org; posting-host="po+45Cp4NCxYiawlIeVFWQ"; logging-data="30857"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18Mk2hwJAhJYHRd/WSdXfS3tWIsRiUE8vk=" User-Agent: KNode/4.4.6 Cancel-Lock: sha1:NUbEepxAT1lCzTuTw7n3P2r9evQ= Xref: x330-a1.tempe.blueboxinc.net comp.databases.mysql:574 Axel Schwenke wrote: > Tim Watts wrote: > >> I'm not a MySQL buff but I have inherited a few: >> >> *Seems* to be the case that a password belongs to the pattern >> "user@somewhere" rather than just "user". Hi Axel, This all looks *vey* interesting. Thanks for the chapter references - I will look at them now. > > In MySQL a user specification always has the form username@hostname. > Both parts can contain wildcards. And the host part can also make use > of numerical ip address ranges (by using a net mask). > > http://dev.mysql.com/doc/refman/5.1/en/account-names.html > >> Correct me if I'm wrong - but when I did some extra grants to allo a user >> to connect from some extra client hosts, the new user was passwordless(!) >> (the original user@somblah did require a password. > > Short: you are looking for the NO_AUTO_CREATE_USER SQL mode: > > http://dev.mysql.com/doc/refman/5.1/en/server-sql- mode.html#sqlmode_no_auto_create_user > > Long: if you execute a GRANT statement for a not yet existing user, > then this user is created automatically. If your GRANT statement lacks > a 'IDENTIFIED BY' clause, then this new user will have no password. > The abovementioned SQL mode modifies the behavior of GRANT, such that > it will never create a new user without a password. This is going to take some playing to get my head around fully but I think I understand the gist. > You still can create a new user and GRANT privileges at once, but then > the GRANT statement must specify a nonempty password. Alternatively you > can use CREATE USER first and then GRANT to add permissions. > > GRANT will never be a problem if you add permissions to existing users. > > > XL Thanks again - I think that should help a great deal. I've only b*ggered up a couple of minor users so I can drop those and start again. Cheers, Tim -- Tim Watts