sandramc@sequent.UUCP (Sandra McMaster) (07/04/89)
In article <4266@uhccux.uhcc.hawaii.edu> richardj@uhccux.UUCP (Richard Jablonka) writes: >Howzit, > > I am using Informix Isql on the unix for the first time. I have made a > database which lists grades for students in a class. Students are Rows in all > tables in the database. How do I limit access to just the row where the > the logged-in student's record is? I don't want student to access other > student records. > > Thanks, > Richard Jablonka Off hand, I can think of a couple solutions that might work... 1) you could create views for each of your students... not the cleanest solution but it would work 2) using Informix 4GL (for a screen oriented solution) or ACE report writer (for a report oriented solution) you could do a query using the user's login name so that they only get info that is tied to their name [this would also require putting the students' login names in the database] -- the variable USER should give you the login name If you want to mail me a listing of your database schema, I would be happy to assist you in writing the necessary query... One other thought... you need to make sure that your database is not accessible to "public"... if it is, anyone logging into the system could potentially gain access to the database if they can get access to the directory where you have the database stored... (check the permissions under Database, Table, Permissions.... you use the SQL to change permissions - the grant and revoke commands) Hope this helps... :-)
richardj@uhccux.uhcc.hawaii.edu (Richard Jablonka) (07/30/89)
I am designing a database using Informix isql and don't understand how the protection works. I have created 2 tables and 2 corresponding views ( 1 for each table). The tables have records of all students, the views show the record for just the student concerned. Here's the question: I know that I have to give SELECT rights to each table, in order to allow user to use each view. How do I stop users from just logging into isql quering the tables directly?
richardj@uhccux.uhcc.hawaii.edu (Richard Jablonka) (08/08/89)
Howzit,
I am using Informix Isql on the unix for the first time. I have made a
database which lists grades for students in a class. Students are Rows in all
tables in the database. How do I limit access to just the row where the
the logged-in student's record is? I don't want student to access other
student records.
Thanks,
Richard Jablonkajohnw@techwood.UUCP (John Wheeler) (08/15/89)
In article <4266@uhccux.uhcc.hawaii.edu> you write: >Howzit, > > I am using Informix Isql on the unix for the first time. I have made a > database which lists grades for students in a class. Students are Rows in all > tables in the database. How do I limit access to just the row where the > the logged-in student's record is? I don't want student to access other > student records. You make a field in the database which contains the login name for that student. You then user the USER (which is a predefined variable, filled in at isql invocation) in a select statement as in select * from tablename where loginname matches USER and the only row that will be brought up is the one corresponding to that user's login name. -- Turner John Wheeler E N T E R T A I N M E N T ...!gatech!nanovx!techwood!johnw Networks Techwood Library * home of Superstation TBS * TNT * TBS Sports
chris@vision.UUCP (Chris Davies) (08/16/89)
In article <4266@uhccux.uhcc.hawaii.edu> richardj@uhccux.UUCP writes: > I am using Informix Isql on the unix for the first time. I have made a > database which lists grades for students in a class. Students are Rows in all > tables in the database. How do I limit access to just the row where the > the logged-in student's record is? I don't want student to access other > student records. For *each student* you will have to create a view onto your main table as follows:- create view FRED as select * from EVERYONE where NAME = "FRED"; revoke all on FRED from public; grant select on FRED to UFRED What this does is to create a view called FRED which has the single row from the table EVERYONE (containing all the students' scores) corresponding to the student named FRED. Does this make sense to you? Additionally it lets only the user UFRED access the table (UFRED is a Unix username). Secondly, you will have to set the table permissions *very* carefully:- revoke all on EVERYONE from public; grant connect to public; Thirdly, ensure that all your students can actually get to the directory <Your_DatabaseName>.dbs, but cannot read the contents (default protection in Unix Informix arranges this). This assumes the following information:- Your table containing *all* the students' scores is called EVERYONE FRED is a student who has grades listed in your table NAME is a column in your table, EVERYONE, which holds the name of the student. UFRED is a valid Unix username, used only by the student called FRED. Hope this is of some help... Chris -- VISIONWARE LTD | UUCP: chris@vision.uucp (or try systime.UUCP) Systime House | or: ...!mcvax!ukc!vision!chris Leeds Business Park | Bruntcliffe Lane | JANET: chris%uk.co.vision@uk.ac.ukc Morley, LEEDS LS27 0NH | England | VOICE: +44 532 529292 ----------------- "You call these opinons? Not Visionware's!" -----------------
iverson@hpcmsrb.HP.COM (Kim Iverson) (08/16/89)
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
iverson@hpcmsrb.HP.COM (Kim Iverson) (08/17/89)
Hi again, I would like to correct a mistake in my earlier reply regarding restricting access to tables using views. I thought it would be possible have the authorizations on the view override those on the underlying table, but that isn't so. So, although the view idea (especially in conjunction with the USER idea from reply 2) may be useful in your case, I see no way to GUARANTEE your students' confidentiality using Informix security features, if that's your aim. I was happy to learn about the USER variable, it's something I can put to good use. Kim Iverson
paul@csnz.co.nz (Paul Gillingwater) (08/18/89)
In article <463@techwood.UUCP> johnw@techwood.UUCP (John Wheeler) writes: >In article <4266@uhccux.uhcc.hawaii.edu> you write: >>Howzit, >> >> I am using Informix Isql on the unix for the first time. >> ... How do I limit access to just the row where the >> the logged-in student's record is? I don't want student to access other >> student records. > >You make a field in the database which contains the login name >for that student. You then use the USER (which is a predefined >variable, filled in at isql invocation) in a select statement >as in >select * from tablename where loginname matches USER Depends on the student's knowledge of UNIX, and whether they get at the shell. I suspect the USER variable might be set up in the .profile -- which could be edited. Alternatively, you could do something like: USER=`id | sed -e 's/[^(]*(\([^)]*\).*/\1/'` export USER which is harder to forge. Don't put it in the .profile, but in the wrapper for Informix. -- Paul Gillingwater, Computer Sciences of New Zealand Limited Bang: ..!uunet!dsiramd!csnz!paul Domain: paul@csnz.co.nz Call Magic Tower BBS V21/23/22/22bis 24 hrs +0064 4 767 326
aland@infmx.UUCP (Dr. Scump) (08/30/89)
In article <101@csnz.co.nz> paul@csnz.co.nz (Paul Gillingwater) writes: >In article <463@techwood.UUCP> johnw@techwood.UUCP (John Wheeler) writes: >... >Depends on the student's knowledge of UNIX, and whether they get >at the shell. I suspect the USER variable might be set up >in the .profile -- which could be edited. Alternatively, you >could do something like: > >USER=`id | sed -e 's/[^(]*(\([^)]*\).*/\1/'` >export USER > >which is harder to forge. Don't put it in the .profile, but in >the wrapper for Informix. >-- >Paul Gillingwater, Computer Sciences of New Zealand Limited Perhaps "USER" needs some clarification here. USER is not an environment variable; it is a figurative pseudo-constant (?) in our SQL dialect. The keyword USER returns the user name in character string format; it is obtained by taking the uid and getting the name from /etc/passwd, *not* from something like logname(). Example: find all table privilege entries granted to you: select * from systabauth where grantee = user; This is also handy for getting the current user name into a variable in a 4GL or ESQL program. Given the character variable curr_user: select unique user into curr_user from systables; ^ need ':' or '$' here in ESQL, of course Hope this clarifies things. Another one of these "figurative variables" (that any better?) is the keyword TODAY, which evaluates to the current date in DATE datatype format. -- Alan S. Denney @ Informix Software, Inc. {pyramid|uunet}!infmx!aland "I want to live! -------------------------------------------- as an honest man, Disclaimer: These opinions are mine alone. to get all I deserve If I am caught or killed, the secretary and to give all I can." will disavow any knowledge of my actions. - S. Vega