Re: Error -302: No GRANT option or illegal option on multi-table view - SOLVED

David Kosenko wrote: > > Peter Lancashire <Peter.Lancashire.PL1@bayer.co.uk> offerred: > > +No doubt this is simple but I can't see it. > + > +The following was all done as the DBA user. > .... > +I granted privileges on all tables in the database like this: > +grant select on <all_tables> to query with grant option; > + > +Then I created a view for user "query" as below. The view includes > +several tables and an expression. The view works OK. > +create view query.ptreatments (...) as select ...; > + > +Then I attempted this and it failed: > +grant select on query.ptreatments to ukkiy; > +# ^ > +# 302: No GRANT option or illegal option on multi-table view. > +# > + > +User ukkiy has select privilege on all the tables used by the view, > +although I do not think that is relevant. > > You, even as DBA, do not have GRANT privs on the view - you gave them away when you > created the view as owned by user query. Run the GRANT as user query, and it should > work ok. While you are at it, as user query GRANT ALL ON ptreatments TO DBA; > > Dave > Thanks for the help. I have solved the problem in a slightly different way, like this: create schema authorization query create view ptreatments (...) as select ... grant select on query.ptreatments to ukkiy; I presume the create schema authorization query syntax is much like connecting as another user. I'd have had a problem with "query" as the user does not exist, its just a device for organizing the database into schemas. Tim V also suggested that the problem might be that someone else owned some of the tables in the view. They didn't. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/