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

Victor Kirilloff wrote: > > 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 > > > > ** Dave Kosenk <davek@summitdata.com> > > ** Director of Training Services (732) 469-4070 > > ** Summit Data Group (an Informix Authorized Education Center) > > ** Find my advice useful? Let me teach you everything I know about > > ** Informix. Sign up for OFFICIAL Informix training at SDG. > > ** For details, see http://www.summitdata.com/training > > If you have DBA permissions then you can grant any privileges on any > objects in database. If you grant privileges on objects > as owned by other users, you must write GRANT operator with AS option: > GRANT SELECT ON "query".ptreatments TO "ukkiy" AS "query";Yes, but even the DBA seems not to be able to do this unless granted the privilege by the table owner. Doing this solved it: create schema authorization query create view myview as select ... grant all on query.myview to dba WITH GRANT OPTION; The user DBA could then manage the table privileges. User query could still withdraw that privilege. It seems the DBA is not quite all-powerful, unless he or she knows about this cunning create schema authorization command. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions. All opinions are my own and not those of Bayer plc. My Internet plumbing does not allow me to mail and post news together. Sorry. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/