simmank.daniel wrote:
[skip]
> Now I'm trying to set up a view in the second
> database (where the user may do anything he wants)
> that is selecting data from the first database.
> Something like this:
>
> CREATE VIEW db2.test as SELECT * FROM db1.table1;
>
> Now I'm getting the following error:
> create view command denied to user
> 'dbrw'@'localhost' for column 'id' in table
> 'test'
>
> Running the above command as root works fine (By
> the way: SELECT * FROM db1.table1 works fine
> too).
>
> Of course I granted the necessary permissions to
> the user 'dbrw' using the following commands:
>
> GRANT ALL ON db2.* TO 'dbrw'@'localhost';
> GRANT SELECT, CREATE VIEW, SHOW VIEW ON db1.* TO
> 'dbrw'@'localhost';
IMHO, there is several mistakes (they are not connected to the problem):
you do not need ALL privileges to db2 if you want to have read only views ('dbrw'@'localhost' will be able to update them in your case, please read more bout how view check rights for underlying tables here
http://dev.mysql.com/doc/refman/5.0/en/create-view.html, or just standard behavior in some book (MySQL follows standard by default in underlying tables privilege check)).
You do not need CREATE VIEW privilege for 'dbrw'@'localhost', in db1, because view should be created in db2 as I understand.
I very doubt that limited user should have CREATE VIEW privilege at all, thought about whole system security (only administrators should be able to create objects including views).
>
> Now my question: Why am I not able to create that
> view? Did I miss something? Is this a bug? Or am I
> just to stupid? :-)
You better send your sequence to
http://bugs.mysql.com/, but check first that you use last version of 5.0 or 5.1 server.
[skip]
Mr. Oleksandr Byelkin
MySQL AB, Full-Time Developer
Lugansk, Ukraine