Permission Problem in View

Hi Informixers, I have a problem with a view that accesses data in other databases: create view "mpgadmin".helios (persnr,helios) as select x0.persnr ,x0.wert from verteiler@opserver_tcp:"verteil".pers_zusatz x0 where ((x0.attribut = 'MKEN' ) AND ((x0.bis IS NULL ) OR (x0.bis >= TODAY ) ) ) union select x1.persnr ,x1.helios from an_pflege@opserver_tcp:"gteicher".helios x1; As user "mpgadmin", I can select from this view just fine. All other users I tried get "272: No select permission". I verified that the users in question do have connect and select permissions on the external tables referenced in the view. They can execute the "select" part of the view definition. But they cannot select from the view. "Grant select" for these users results in "302: No GRANT option or illegal option on multi- table view". "Grant all" gives no error, but doesn't resolve the problem. What can I do? I do need the ability to select from this view as a user other than the owner of the view. Platform is IDS 7.31UD5 on Linux (Kernel 2.4.4). Regards, Richard -- +-------------------------------+---------------------------------------+ | Dr. med Richard Spitz | Mail: spitz@ana.med.uni-muenchen.de | | Klinik für Anaesthesiologie | Tel : +49-89-7095-6110 | | Klinikum der Univ. München | FAX : +49-89-7095-6420 | | 81366 München, Germany | GSM : +49-172-8933578 | +-------------------------------+---------------------------------------+

Hi Richard! I'd suggest to do the following. If the owner of the table or view is not "informix", you'll have to grant select on table-name to user-name as table-owner; (ex. grant select on helios to user1 as mpgadmin;) Richard Spitz wrote: > Hi Informixers, > > I have a problem with a view that accesses data in other databases: > > create view "mpgadmin".helios (persnr,helios) as > select x0.persnr ,x0.wert > from verteiler@opserver_tcp:"verteil".pers_zusatz x0 > where ((x0.attribut = 'MKEN' ) > AND ((x0.bis IS NULL ) OR (x0.bis >= TODAY ) ) ) > union > select x1.persnr ,x1.helios > from an_pflege@opserver_tcp:"gteicher".helios x1;> > As user "mpgadmin", I can select from this view just fine. > > All other users I tried get "272: No select permission". > > I verified that the users in question do have connect and select > permissions on the external tables referenced in the view. They > can execute the "select" part of the view definition. But they > cannot select from the view. "Grant select" for these users > results in "302: No GRANT option or illegal option on multi- > table view". "Grant all" gives no error, but doesn't resolve > the problem. > > What can I do? I do need the ability to select from this > view as a user other than the owner of the view. > > Platform is IDS 7.31UD5 on Linux (Kernel 2.4.4). > > Regards, Richard > -- > +-------------------------------+---------------------------------------+ > | Dr. med Richard Spitz | Mail: spitz@ana.med.uni-muenchen.de | > | Klinik für Anaesthesiologie | Tel : +49-89-7095-6110 | > | Klinikum der Univ. München | FAX : +49-89-7095-6420 | > | 81366 München, Germany | GSM : +49-172-8933578 | > +-------------------------------+---------------------------------------+ "WorldSecure Server <safeway.com>" made the following annotations on 01/21/03 12:35:30 ------------------------------------------------------------------------------ Warning: All e-mail sent to this address will be received by the Safeway corporate e-mail system, and is subject to archival and review by someone other than the recipient. This e-mail may contain information proprietary to Safeway and is intended only for the use of the intended recipient(s). If the reader of this message is not the intended recipient(s), you are notified that you have received this message in error and that any review, dissemination, distribution or copying of this message is strictly prohibited. If you have received this message in error, please notify the sender immediately. ==============================================================================