bochner@abacus.harvard.edu (Harry Bochner) (02/14/90)
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 ... -- Harry Bochner bochner@endor.harvard.edu
perk@infmx.UUCP (Paul Perkovic) (03/02/90)
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
aland@infmx.UUCP (Dr. Scump) (03/06/90)
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."