Path: utzoo!attcan!uunet!snorkelwacker!think!zaphod.mps.ohio-state.edu!mips!prls!pyramid!infmx!aland From: aland@infmx.UUCP (Dr. Scump) Newsgroups: comp.databases Subject: Re: Permissions on views in INFORMIX Summary: the 4.0 release works how (I think) you want it to Message-ID: <3551@infmx.UUCP> Date: 6 Mar 90 03:49:05 GMT References: <3511@infmx.UUCP> Reply-To: aland@infmx.UUCP (alan denney) Organization: INFORMIX Professional Services ("Peace thru Normalization") Lines: 53 In article <3511@infmx.UUCP> perk@infmx.UUCP (Paul Perkovic) writes: |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). |... Paul, I think that you are missing Mr. Bochner's problem. If I under- stand correctly, he is complaining that he should be able to create views restricting portions of tables (while he has full privileges on said tables), then grant the desired privileges on the views and revoke all privileges on the underlying tables. He cannot do this with the .03F release that he has -- the permissions on the underlying tables are used *in addition* to those on the view. As part of other ANSI (SQL2) compatibility changes, the view/table privilege control was revamped in the 4.0 release to allow just as he (seems to) want. He can build his views, grant privileges on them, and revoke the privileges on the underlying tables. From that point on, the affected users can get at the data *only* through his views. |/ perk perk@informix.com ...{pyramid|uunet}!infmx!perk -- Alan S. Denney @ Informix Software, Inc. "We're homeward bound aland@informix.com {pyramid|uunet}!infmx!aland ('tis a damn fine sound!) ----------------------------------------------- with a good ship, taut & free Disclaimer: These opinions are mine alone. We don't give a damn, If I am caught or killed, the secretary when we drink our rum will disavow any knowledge of my actions. with the girls of old Maui."