Path: utzoo!utgpu!jarvis.csri.toronto.edu!cs.utexas.edu!wuarchive!zaphod.mps.ohio-state.edu!mips!prls!pyramid!infmx!perk From: perk@infmx.UUCP (Paul Perkovic) Newsgroups: comp.databases Subject: Re: Permissions on views in INFORMIX Summary: You must have GRANT privileges on the underlying tables Message-ID: <3511@infmx.UUCP> Date: 1 Mar 90 22:17:08 GMT References: Reply-To: perk@infmx.UUCP (Paul Perkovic) Distribution: comp.databases Organization: Informix, Menlo Park, Ca. U.S.A. Lines: 43 In an earlier article bochner@abacus.harvard.edu (Harry Bochner) writes: >It seems like one of the main features of views is to provide user >access control: if you create a view that selects exactly the data >that belongs to a particular user, you should be able to grant that >user select permission on just that view, while denying access to the >underlying tables. That way you get access control exactly customized >to the user. >It does work this way in some versions of SQL (I think), but >apparently not in INFORMIX, according the experiments I just tried in >ISQL 2.10.03F (on a sun4). I was able to select through the view only >when selection permission was granted on the underlying tables as well. >That seems to make views useless for protection purposes. Am I missing >something, or do I have to find some other way to restrict access to >the data? > >Thanks for any suggestions ... When you create a view, you receive the same privileges that you had on the underlying tables that are referenced in the query specification. In order to be able to grant privileges on a view, you must be able to grant the corresponding privileges on all tables on which the view is based. If you are the owner of the base tables and the views, you (as owner) could grant privileges on the base tables, therefore you should be able to grant privileges on the views only (without actually granting privileges on the base tables). If the base tables are owned by someone else who has granted privileges to you, that privilege grant must include WITH GRANT OPTION to permit you to propagate the privileges through the view. Again, you do not need to actually grant privileges on those tables, just on the view, but you must be able to grant the underlying privileges. This explanation is based on the ANSI standard (X3.135-1989) and on the Informix ESQL/C manual for Version 4.0, which is designed to comply with the ANSI standard. You referenced an earlier version of Informix; please make sure you have grant privileges on the base tables, and let me know if you are still having difficulties. / perk perk@informix.com ...{pyramid|uunet}!infmx!perk Paul Perkovic (415) 591-7700 anytime Informix Software, Inc. (415) 926-6821 17 Rinconada Cir./ Belmont, CA 94002 4100 Bohannon Dr./ Menlo Park, CA 94025