Re: Permission Problem in View

Posted in 2003

Topics: Server Administration, Security, Permissions & Auditing

Richard Spitz — Wed, 22 Jan 2003 09:17:57 — source: IIUG Forums & Mailing Lists

Andrew Hamm wrote: > > possible workarounds: > 1) change owners of all tables in the view - not a pretty task Out of the question. > 2) original owner of the tables may be able to use "with grant option" on a > GRANT command for your mpgadmin account. Didn't help. Still get the -302 error when trying to grant select on the view. > 3) a dba user (perhaps needs to be registered as a dba on both databases) > might be able to on-grant permissions. That didn't work either. > 4) if you are screamingly desperate, you can grant dba priviledge to any > desired users of the view, but this is risky at best. Giving away DBA > priviledge is not something to do lightly because it endangers your data. Even under my own account, which has dba privileges on all the databases involved I cannot select from the view, I get the -272 error. However, you brought me on the right track: I dropped the views and re-created them under my own account. Now I am able to grant select privilege to the users I want, and they can now select from the view. 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 | +-------------------------------+---------------------------------------+

↪ replying to Richard Spitz

DL Redden — Wed, 22 Jan 2003 17:33:19 — source: IIUG Forums & Mailing Lists

--0-1769910248-1043256634=:29988 Content-Type: text/plain; charset=us-ascii The first time a view is selected from information on the remote tables that are used in that view are stored in the dictionary cache, as best we can tell. Since this info is cached, any changes made to the remote tables, ie permisions or schema, after this initial select will not be reflected in the view. This can be remedied by either droping and recreating the view with a different name or owner, overflowing the dictionary cache so that the view info is flushed, or by bouncing the local instance of Informix. My two cents Richard Spitz <Richard.Spitz@ana.med.uni-muenchen.de> wrote:Andrew Hamm wrote: > > possible workarounds: > 1) change owners of all tables in the view - not a pretty task Out of the question. > 2) original owner of the tables may be able to use "with grant option" on a > GRANT command for your mpgadmin account. Didn't help. Still get the -302 error when trying to grant select on the view. > 3) a dba user (perhaps needs to be registered as a dba on both databases) > might be able to on-grant permissions. That didn't work either. > 4) if you are screamingly desperate, you can grant dba priviledge to any > desired users of the view, but this is risky at best. Giving away DBA > priviledge is not something to do lightly because it endangers your data. Even under my own account, which has dba privileges on all the databases involved I cannot select from the view, I get the -272 error. However, you brought me on the right track: I dropped the views and re-created them under my own account. Now I am able to grant select privilege to the users I want, and they can now select from the view. 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 | +-------------------------------+---------------------------------------+ --------------------------------- Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now --0-1769910248-1043256634=:29988 Content-Type: text/html; charset=us-ascii The first time a view is selected from information on the remote tables that are used in that view are stored in the dictionary cache, as best we can tell. Since this info is cached, any changes made to the remote tables, ie permisions or schema, after this initial select will not be reflected in the view. This can be remedied by either droping and recreating the view with a different name or owner, overflowing the dictionary cache so that the view info is flushed, or by bouncing the local instance of Informix. My two cents  Richard Spitz <Richard.Spitz@ana.med.uni-muenchen.de> wrote: <BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">Andrew Hamm wrote: > > possible workarounds: > 1) change owners of all tables in the view - not a pretty task Out of the question. > 2) original owner of the tables may be able to use "with grant option" on a > GRANT command for your mpgadmin account. Didn't help. Still get the -302 error when trying to grant select on the view. > 3) a dba user (perhaps needs to be registered as a dba on both databases) > might be able to on-grant permissions. That didn't work either. > 4) if you are screamingly desperate, you can grant dba priviledge to any > desired users of the view, but this is risky at best. Giving away DBA > priviledge is not something to do lightly because it endangers your data. Even under my own account, which has dba privileges on all the databases involved I cannot select from the view, I get the -272 error. However, you brought me on the right track: I dropped the views and re-created them under my own account. Now I am able to grant select privilege to the users I want, and they can now select from the view. 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 | +-------------------------------+---------------------------------------+</BLOCKQUOTE> <hr size=1>Do you Yahoo!? <a href="http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com">Yahoo! Mail Plus</a> - Powerful. Affordable. <a href="http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com">Sign up now</a> --0-1769910248-1043256634=:29988--

Related threads

Index space reuse