At 1:12 PM +1300 1999/11/30, Chris Crook wrote:
>>Chris Crook writes:
>> > Hi
>> > I am having difficulty understanding how to use the "WITH GRANT OPTION"
>> > of the GRANT statement.
>> > I am trying to set up a user cmsadmin that can grant and revoke rights to
>> > a specific database (cms) but has no influence on any other
>> > my reading of the documentation I believe that I should be able to set
>> > this up with a statement like
>> > GRANT ALL PRIVILEGES ON cms.* to cmsadmin@localhost WITH GRANT OPTION;
>> > I have used this, and all appears well. However when I then connect as
>> > the user cmsadmin, and try to grant rights to another user ccrook with
> > > GRANT SELECT, INSERT, UPDATE, DELETE ON cms.* TO ccrook@localhost;
>> > I get the following error
>> > ERROR 1045: Access denied for user: 'cmsadmin@localhost' (Using
>> > Obviously this isn't what I want!!!! I'd appreciate any insight into why
>> > < ...... stuff cut out >
>>WITH GRANT OPTION should be used with global rights , i.e. *.*.
In that case, GRANT should fail if you try to grant non-global rights.
Besides, as pointed out below, the db table has a Grant_priv column,
which doesn't make any sense if you can't grant GRANT at the database
>You may be right that WITH GRANT OPTION should be used with global rights, but
>I cannot find anything in the manual that specifies this. The db
>table includes a
>grant_priv column, and there is nothing to indicate that this isn't
>used. In fact the
>documentation of the GRANT syntax in section 7.25 of the manual
>about using the grant option at the database (rather than global level).
That's my understanding of how it should work as well. The purpose is
to allow a user who "owns" a database to administer the permissions
on it himself. And in fact it does work for me. I just tried this
under MySQL 3.23.6. What version are you using?
>However I'll try using the GRANT OPTION at the global level .. I can
>that to achieve what I require.
Paul DuBois, paul@stripped