Path: utzoo!attcan!uunet!tut.cis.ohio-state.edu!ucbvax!hplabs!hpl-opus!hpspdra!hpcmsrb!iverson
From: iverson@hpcmsrb.HP.COM (Kim Iverson)
Newsgroups: comp.databases
Subject: Re: Informix Isql Protection
Message-ID: <70220001@hpcmsrb.HP.COM>
Date: 15 Aug 89 22:45:01 GMT
References: <4266@uhccux.uhcc.hawaii.edu>
Organization: HP Corporate Manufacturing Systems
Lines: 35


Hi,

This isn't pretty, but it's the only way I can think of to absolutely
restrict access to the tables.  The other methods I thought of depend
on the students' not knowing how Informix works, which probably
won't do.

You can create views on the tables which will allow you to restrict
selection to specific rows, based on query criteria.  Unfortunately,
each student needs his or her own view.  I know of no way to have
Informix check user id at the row level.

Here's an example of a view:

	create view homework_jstudent as
	  select * from homework
	    where student_uid = "jstudent"
	  with check option;
	revoke all on homework from public;
	grant connect to jstudent;
	grant select on homework_jstudent to jstudent;

The student would select from the view exactly as if it were a table,
but would only see his or her own rows.  Perform screens and Ace
reports can be built as well -- one per view.

If you are handy with vi or other unix facilities, you can 
generate the SQL script for each student without too much effort,
but it would be nice if there were a better way.


Hope this helps,

Kim Iverson