Re: Permissions on a View
Chris S found that other DBA users got error -272 (No SELECT permission) when selecting from views he created, specifically views built on remote tables, even though those users had DBA rights on both databases and could query the remote tables directly; an explicit GRANT SELECT on the view fixed access. Suggestions included stale dictionary-cache permissions (cleared by bouncing the engine), that it may be a bug worth reporting, or that remote-table views simply require explicit grants. No definitive answer is recorded; Chris had a support call open and promised to post back.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Chris S wrote: > Here's a snippet from the Informix 10 Manual: "When you create a view, > PUBLIC does not automatically receive any privileges for a view that > you create. Only you have access to table data through that view. Even > users who have privileges on the base table of the view do not > automatically receive privileges for the view." > > Does this hold true for other DBA's on the same database? It seems as > this is the case through some testing I have done here but it seems odd > to me. If I do not explicity grant SELECT on my view to a DBA user, > then they cannot select from it. > > Can anyone tell me if this is working as it's supposed to? It seems > strange to me that DBA's don't just get full permissions by default on > new views. What I get is 272: No SELECT permission. Here's a little more information that is probably very useful - all of the views that I am having permission problems with are views against remote tables. -- Chris
ifaikr if a user is dba then that user has full control; > What I get is 272: No SELECT permission. > > Here's a little more information that is probably very useful - all of > the views that I am having permission problems with are views against > remote tables. Question does that user have dba permissions on the remote site too?? if not then the error message is correct. Superboer.
Superboer wrote: > ifaikr if a user is dba then that user has full control; > > > What I get is 272: No SELECT permission. > > > > Here's a little more information that is probably very useful - all of > > the views that I am having permission problems with are views against > > remote tables. > > Question does that user have dba permissions on the remote site too?? > > if not then the error message is correct. > > Superboer Yes, the other account has DBA on both sides. If I explicitly grant select on the view to the other account I can then use the view.
I have seen instances where the permissions on the remote table where cached and the only way to resolve the issues was to clear/flush the dictionary cache either by flooding the local engine with queries against every table in the system or by bouncing the engine. ----- Original Message ---- From: Chris S <cjsommer@gmail.com> To: informix-list@iiug.org Sent: Tuesday, June 20, 2006 2:21:19 PM Subject: Re: Permissions on a View Superboer wrote: > ifaikr if a user is dba then that user has full control; > > > What I get is 272: No SELECT permission. > > > > Here's a little more information that is probably very useful - all of > > the views that I am having permission problems with are views against > > remote tables. > > Question does that user have dba permissions on the remote site too?? > > if not then the error message is correct. > > Superboer Yes, the other account has DBA on both sides. If I explicitly grant select on the view to the other account I can then use the view. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
this sounds like a bug to me contact TS !! Superboer.
I think it's not a bug. If dba doesn't have select permission for that remote table then dba must not have permission for this view. When you create a view against remote table do you want Informix to check permissions for that remote table for all dba in database? Superboer wrote: > this sounds like a bug to me contact TS !! > > > Superboer.
The DBA does have select permission on the remote table. If I query that remote table directly, I can get data back just fine. I just cannot query it through the view. It sounds like permissions on a view to a remote table have to be explicitly granted to ALL users of that view, even other DBA's, before they can query through that view. I do have a call open with Informix Support and will post back when they give me an explanation. I'm not saying it's a bug, but I'm just looking to understand "why" it behaves this way. -- Chris SaltTan wrote: > I think it's not a bug. > > If dba doesn't have select permission for that remote table then dba > must not have permission for this view. > When you create a view against remote table do you want Informix to > check permissions for that remote table for all dba in database? > > Superboer wrote: > > this sounds like a bug to me contact TS !! > > > > > > Superboer.