List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 30 1999 1:08am
Subject:Re: Problem using WITH GRANT OPTION clause
View as plain text  
At 1:12 PM +1300 1999/11/30, Chris Crook wrote:
>sinisa@stripped  writes:
>>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 
>>database.  From
>>  > 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 
>>password: YES)
>>  >
>>  > 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

>Hi Sinisa
>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 
>explicitly talks
>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 
>probably use
>that to achieve what I require.

Paul DuBois, paul@stripped
Problem using WITH GRANT OPTION clauseChris Crook28 Nov
  • Re: Problem using WITH GRANT OPTION clausesinisa29 Nov
Re: Problem using WITH GRANT OPTION clauseChris Crook30 Nov
  • Re: Problem using WITH GRANT OPTION clausePaul DuBois30 Nov
Re: Problem using WITH GRANT OPTION clauseChris Crook1 Dec