[Wylug-help] MySQL GRANT Command

[Aaron Crane]

Smylers writes:
>   GRANT SELECT ON nora.* TO compo;
> 
> Don't do that -- it's really bad!
> [...]
> So next I look at all the places compo can currently connect from, and
> try specifying those, in separate commands (which is irritating enough,
> since I'm just typing back into MySQL information it's just provided me
> with):
> 
>   GRANT SELECT ON nora.* TO compo at localhost;
>   GRANT SELECT ON nora.* TO compo at pub;
>   GRANT SELECT ON nora.* TO compo at field;
> 
> And that seems to work; the user table isn't touched at all, and no
> security whole is opened up.  But the problem is that in the db table
> rather than one entry which says user = 'compo', host = '%' (deferring
> the host authorization to the user table), there are three entries, one
> for each of the above hostnames (but otherwise identical).

I think that what you want to do is either impossible with the GRANT
command, or possible only in MySQL 5.0 and above (and I happen to know that
you aren't using MySQL 5.0 in this case).

You have several rows in the mysql.User table, for compo at localhost,
compo at pub, compo at field, etc.  Each existing mysql.db, mysql.tables_priv,
mysql.columns_priv row grants permissions to any compo@'%' user.  (Note
that there is no meaningful way of joining mysql.db against mysql.user
using only the equality operator.)

Any GRANT statement you issue apparently looks for a suitable user by
equality-comparing the user and host in the GRANT with the user.User and
user.Host columns.  As far as I can tell, the use of equality rather than
LIKE is by design, though I can't come up with a situation in which anyone
would want what it does.

Regardless, in this case, no such matching user is found.  According to the
manual at http://dev.mysql.com/doc/refman/5.0/en/grant.html :

  If you grant privileges for a username/hostname combination that does not
  exist in the mysql.user table, an entry is added [...]. In other words,
  GRANT may create user table entries

  Warning: If you create a new user but do not specify an IDENTIFIED BY
  clause, the user has no password. This is very insecure.

Which is precisely the situation you're in, as you note.

The manual for MySQL 5.0 continues:

  As of MySQL 5.0.2, you can enable the NO_AUTO_CREATE_USER SQL mode to
  prevent GRANT from creating a new user if it would otherwise do so, unless
  IDENTIFIED BY is given to provide the new user a non-empty password.

It's not clear from the manual what the behaviour in that situation would
be.  I can think of the following options:

  - MySQL refuses to execute the statement

  - MySQL creates the appropriate rows in mysql.db (or other table for
    sub-database-level privileges), referring to a user compo@'%'

The latter is obviously what you want, but I have no confidence that MySQL
does it.  And, of course, it's only available in version N+1 anyway.

Sigh.

-- 
Aaron Crane